It doesn't make a lot of sense to do this - a direct
INSERT
query would be easier to code.
However, if you're going to use this approach, you need to do two things:
- Validate the table and column names before creating your query;
- Use
sp_executesql
[^] to pass the parameters to the query, avoiding SQL Injection[^];
ALTER PROCEDURE [dbo].[InsertDynamicFun]
@TblName sysname, @F1 sysname, @F2 sysname, @F3 sysname, @F4 sysname,
@P1 nvarchar(50), @P2 nvarchar(50), @P3 nvarchar(50), @P4 nvarchar(50)
AS
BEGIN
DECLARE @TableObjectID int;
DECLARE @Statement nvarchar(max), @ParameterDefinition nvarchar(max);
SELECT
@TableObjectID = object_id,
@TblName = QUOTENAME(S.name) + '.' + QUOTENAME(T.name)
FROM
sys.tables As T
INNER JOIN sys.schemas As S
ON S.schema_id = T.schema_id
WHERE
name = @TblName
;
If @@ROWCOUNT = 0
BEGIN
RAISERROR('Table "%s" does not exist.', 16, 1, @TblName);
Return;
END;
SELECT
@F1 = QUOTENAME(name)
FROM
sys.columns
WHERE
object_id = @TableObjectID
And
name = @F1
;
If @@ROWCOUNT = 0
BEGIN
RAISERROR('Table "%s" does not contain a column called "%s".', 16, 1, @TblName, @F1);
Return;
END;
SELECT
@F2 = QUOTENAME(name)
FROM
sys.columns
WHERE
object_id = @TableObjectID
And
name = @F2
;
If @@ROWCOUNT = 0
BEGIN
RAISERROR('Table "%s" does not contain a column called "%s".', 16, 1, @TblName, @F2);
Return;
END;
SELECT
@F3 = QUOTENAME(name)
FROM
sys.columns
WHERE
object_id = @TableObjectID
And
name = @F3
;
If @@ROWCOUNT = 0
BEGIN
RAISERROR('Table "%s" does not contain a column called "%s".', 16, 1, @TblName, @F3);
Return;
END;
SELECT
@F4 = QUOTENAME(name)
FROM
sys.columns
WHERE
object_id = @TableObjectID
And
name = @F4
;
If @@ROWCOUNT = 0
BEGIN
RAISERROR('Table "%s" does not contain a column called "%s".', 16, 1, @TblName, @F4);
Return;
END;
SET @Statement = N'INSERT INTO ' + @TblName + N' (' + @F1 + N', ' + @F2 + N', ' + @F3 + N', ' + @F4 + N') VALUES (@P1, @P2, @P3, @P4)';
SET @ParameterDefinition = N'@P1 nvarchar(50), @P2 nvarchar(50), @P3 nvarchar(50), @P4 nvarchar(50)';
EXEC sp_executesql @Statement, @ParameterDefinition, @P1, @P2, @P3, @P4;
END