sql - 将日期参数传递给动态sql

Question

我很难构建此查询。错误是 - 从字符串转换日期时转换失败。这是适用于所有表的通用查询。一切正常，但我必须添加一个日期参数。

SQL:

DECLARE @qryUpd nvarchar(MAX), @params nvarchar(MAX), @TableName nvarchar(50),
        @ColName nvarchar(50),@Id nvarchar(50),@ModifiedBy nvarchar(50),
        @ModifiedDate datetime


set @qry = 'update '+@TableName+' set Deleted=1, ModifiedBy='+''''+@Id+''',
            ModifiedDate='+''''+@ModifiedDate+''''+' where '+@Colname+'='+''''+
            @Id+''''

execute sp_executesql @qry

C#:

public void test(int id, int userid)
{
  sqlCommand cmd = new sqlcommand("TestFunc",con);
  cmd.commandtype = commandtype.storedprocedure;
  cmd.parameters.addwithvalue("@TableName","tblArea");
  cmd.parameters.addwithvalue("@ColName","AreaId");
  cmd.parameters.addwithvalue("@Id",id);
  cmd.parameters.addwithvalue("@ModifiedBy",userid);
  cmd.parameters.addwithvalue("@ModifiedDate",system.datetime.now.tostring());
}

Answer 1

除了动态表名和列之外，您至少可以部分地 parameterize您的查询，这将消除转义引号的麻烦，并提供一些针对 Sql 注入(inject)的保护(因为表和列仍然容易受到攻击，需要进行验证):

DECLARE @qry AS NVARCHAR(MAX);
set @qry = N'update '+@TableName + 
            ' set Deleted=1, ModifiedBy=@ModifiedBy, ModifiedDate=@ModifiedDate
            where ' + @Colname + ' = @Id';
exec sp_executesql @qry, 
        N'@Id nvarchar(50), @ModifiedBy nvarchar(50), @ModifiedDate datetime', 
        @Id  = @Id,
        @ModifiedBy = @ModifiedBy,
        @ModifiedDate = @ModifiedDate;

SqlFiddle here

然后您可以将 @Id、@ModifiedBy 和 @ModifiedDate 的 .Net 参数直接与 native 类型绑定(bind)