如何在sql server中为insert语句编写动态查询？ [英] How to write a dynamic query for insert statement in sql server ?

问题描述

我想在表中插入数据的动态插入查询，我有5个具有相同列的表需要单个查询将数据插入表中。



I创建sp

I want a dynamic insert query for insert data in table, I have 5 tables which have same column want a single query to insert data into tables.

I create sp

create proc insertdata
(
@tableName nvarchar(50),
@name nvarchar(50),
@Nrange nvarchar(50),
@isActive bit
)
as 
begin

declare @sql nvarchar(200)
set @sql = 'isert into '+@tableName +' values('+@name +','+@Nrange +','+@isActive +')';
exec(@sql)
end

这样但是当试图执行它时会抛出错误





ERROR

------------

like this but when try to execute it throws error


ERROR
------------

Msg 402, Level 16, State 1, Procedure insertdata, Line 15
The data types nvarchar and bit are incompatible in the add operator.

推荐答案

您的参数 @isActive 的类型为位，而其余命令为 nvarchar 。您不能在这两种数据类型之间使用+运算符。您可能希望将bit参数转换为 nvarchar 以使其正常工作。

Your parameter @isActive is of type bit while rest of the command is nvarchar. You cannot use + operator between these two data types. You might want to convert the bit parameter to nvarchar to get this working.

存储过程不能自动执行的完美示例保护您免受 SQL注入 [ ^ ]！



使用 sp_executesql [ ^ ]执行查询，将参数作为参数传递。您还需要验证表名是否是当前数据库中的有效表。

A perfect example of why stored procedures don't automatically protect you from SQL Injection[^]!

Use sp_executesql[^] to execute your query, passing the parameters as parameters. You also need to validate that the table name is a valid table in the current database.

CREATE PROC insertdata
(
    @tableName nvarchar(50),
    @name nvarchar(50),
    @Nrange nvarchar(50),
    @isActive bit
)
As 
BEGIN
DECLARE @sql nvarchar(max);
DECLARE @RealTableName sysname;
    
    SET NOCOUNT ON;
    
    SELECT
        @RealTableName = 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
        T.name = @tableName
    ;
    
    If @@ROWCOUNT = 0 RAISERROR('Invalid table: "%s"', 16, 1, @tableName);

    SET @sql = N'INSERT INTO ' + @RealTableName + N' VALUES (@name, @Nrange, @isActive)';
    
    EXEC sp_executesql @sql, 
        N'@name nvarchar(50), @Nrange nvarchar(50), @isActive bit',
        @name = @name,
        @Nrange = @Nrange,
        @isActive = @isActive
    ;
END

---

你想知道关于SQL注入的一切（但不敢问）特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

---

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

这篇关于如何在sql server中为insert语句编写动态查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！