灵活插入STORED PROCEDURE [英] Flexible Insert STORED PROCEDURE

问题描述

我存储的

my stored

alter PROCEDURE [dbo].[InsertDynamicFun] 
@TblName nvarchar(max),@F1 nvarchar(50),@F2 nvarchar(50),@F3 nvarchar(50),@F4 nvarchar(50) ,
@P1 nvarchar(50),@P2 nvarchar(50),@P3 nvarchar(50),@P4 nvarchar(50) 
AS BEGIN
 declare @TAblE nvarchar(max);

set @TAblE = 'Insert Into ' + @TblName + '('+@F1+','+@F2+','+@F3+','+@F4+')Values ('+@P1+','+@P2+','+@P3+','+@P4+')' exec(@TAblE) 
END

显示错误消息207，级别16，状态1，行3无效的列名称' @ P2'的价值。消息207，级别16，状态1，行3无效的列名称@ P3的值。 Msg 207，Level 16，State 1，Line 3无效的列名称'@ P4的值'。



但是@ P1没有显示她的错误我想保存任何表中的数据通过传递参数到存储的PROCEDURE并考虑数字字段

Display Error Msg 207, Level 16, State 1, Line 3 Invalid column name 'Value of @P2'. Msg 207, Level 16, State 1, Line 3 Invalid column name 'Value of @P3'. Msg 207, Level 16, State 1, Line 3 Invalid column name 'Value of @P4'.

but @P1 Did not show her fault I want to save the data in any table by pass The Parameter to stored PROCEDURE and Taking into account the numeric fields

推荐答案

设置@TAblE ='插入'+ @TblName +'（' + @ F1 +'，'+ @ F2 +'，'+ @ F3 +'）值（'''+ @ P1 +'''，'''+ @ P2 +'''，'''+ @ P3 +'''）'



你可以尝试使用这一行替代你的。

set @TAblE = 'Insert Into ' + @TblName + '('+@F1+','+@F2+','+@F3+')Values ('''+@P1+''','''+@P2+''','''+@P3+''')'

Can you try with this line in place of your.

这样做没有多大意义 - 直接 INSERT 查询将更容易编码。



但是，如果你要使用这个方法，你需要做两件事：

• 在创建查询之前验证表名和列名;
• 使用 sp_executesql [ ^ ]将参数传递给查询，避免 SQL注入 [ ^ ];

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);
    
    -- Verify that the table exists, and get the full name:
    -- TODO: Restrict the tables which can be used.
    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;
    
    -- Validate that the first column exists:
    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;
    
    -- Validate that the second column exists:
    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;
    
    -- Validate that the third column exists:
    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;
    
    -- Validate that the fourth column exists:
    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;
    
    
    -- Build the query to execute.
    -- By using sp_executesql, the parameters can be passed as parameters.
    -- We still have to use string concatentation for the table and column names,
    -- but since we've validated the names above, this should be OK.
    
    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

这篇关于灵活插入STORED PROCEDURE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！