sql 存储过程 表名作为参数 传递

Question

    CREATE PROCEDURE UPKJ90Detail @StatusID INT, @IOTime VARCHAR ( 50 ), @UniqueId bigint, @DataFlag VARCHAR ( 50 ) AS UPDATE Detail202206 
    SET DataFlag = @DataFlag,
    StatusID = @StatusID 
WHERE
    UniqueId = @UniqueId 
    AND IOTime BETWEEN CONVERT ( VARCHAR,@IOTime, 120 ) + '.000' 
    AND CONVERT ( VARCHAR,@IOTime, 120 ) + '.999'

Detail202206 作为参数传递，这个sql语句怎么写呢？放到txt文本中c#读取执行。

Answer 1

CREATE PROCEDURE UPKJ90Detail
@StatusID INT,
@IOTime VARCHAR ( 50 ),
@UniqueId bigint,
@DataFlag VARCHAR ( 50 ),
@TableName varchar(200)
AS
set nocount on
declare @sql nvarchar(max)
declare @time1 varchar(30)=convert(varchar(23),@IOTime,120)
declare @time2 varchar(30)=convert(varchar(23),@IOTime,120)+'.997'
set @sql=N'UPDATE '+@TableName+'
SET DataFlag = '''+@DataFlag+''',
StatusID = '+left(@StatusID,10)+'
WHERE UniqueId = '+left(@UniqueId,10)+'
AND IOTime BETWEEN '''+@time1+''' and '''+@time2+''''
exec sp_executesql @sql
set nocount off
说明一下，MSSQL的datetime类型数据精度是3微秒，所以后面加上.999是没有意义的，应该是加上.997