创建如下存储过程时,用的是sp_executesql动态执行语句,传入参数执行后说对应的参数不对,请高手指教:CREATE PROCEDURE dbo.Report_KeyClientOperationData
@T0 nvarchar(20),
@T1 nvarchar(20),
@FamilyName nvarchar(50),
@DPId1 nvarchar(10),
@DPId2 nvarchar(10)
ASBEGINDECLARE @sql nvarchar(2000)SET @sql = 'SELECT
fsc.Name,
fsc.Symbol as Ticker,
dpl.DataPoint,
uh.DataValueNew,
uh.DataValueOld
FROM (SELECT Id,TS,DPId,DataValueNew,DataValueOld,UpdateType FROM UpdateHistory
WHERE DPId in ('+@DPId1+','+@DPId2+') AND TS BETWEEN '+@T0+' AND '+ @T1+') uhINNER JOIN DataPointList dpl ON dpl.DPId = uh.DPIdINNER JOIN((SELECT Id,FundId,Name,Status,Symbol FROM FundShareClass WHERE ShareClassTypeId!='+'''LW'''+' or ShareClassTypeId is null) fscINNER JOIN (Select Id,FundName,FamilyName FROM FundBasics WHERE FamilyName = '+@FamilyName+') fb ON fsc.FundId = fb.Id)ON uh.Id = fsc.Id'EXEC sp_executesql @sqlEND执行存储过程 exec dbo.Report_KeyClientOperationData '2008-3-1', '2008-3-31','Barclays','FB10','FB11'报错如下:
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name 'FB11'.
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name 'FB10'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'Barclays'.
@T0 nvarchar(20),
@T1 nvarchar(20),
@FamilyName nvarchar(50),
@DPId1 nvarchar(10),
@DPId2 nvarchar(10)
ASBEGINDECLARE @sql nvarchar(2000)SET @sql = 'SELECT
fsc.Name,
fsc.Symbol as Ticker,
dpl.DataPoint,
uh.DataValueNew,
uh.DataValueOld
FROM (SELECT Id,TS,DPId,DataValueNew,DataValueOld,UpdateType FROM UpdateHistory
WHERE DPId in ('+@DPId1+','+@DPId2+') AND TS BETWEEN '+@T0+' AND '+ @T1+') uhINNER JOIN DataPointList dpl ON dpl.DPId = uh.DPIdINNER JOIN((SELECT Id,FundId,Name,Status,Symbol FROM FundShareClass WHERE ShareClassTypeId!='+'''LW'''+' or ShareClassTypeId is null) fscINNER JOIN (Select Id,FundName,FamilyName FROM FundBasics WHERE FamilyName = '+@FamilyName+') fb ON fsc.FundId = fb.Id)ON uh.Id = fsc.Id'EXEC sp_executesql @sqlEND执行存储过程 exec dbo.Report_KeyClientOperationData '2008-3-1', '2008-3-31','Barclays','FB10','FB11'报错如下:
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name 'FB11'.
Server: Msg 207, Level 16, State 1, Line 10
Invalid column name 'FB10'.
Server: Msg 207, Level 16, State 1, Line 22
Invalid column name 'Barclays'.
@T0 nvarchar(20),
@T1 nvarchar(20),
@FamilyName nvarchar(50),
@DPId1 nvarchar(10),
@DPId2 nvarchar(10)
AS BEGIN DECLARE @sql nvarchar(2000) SET @sql = 'SELECT
fsc.Name,
fsc.Symbol as Ticker,
dpl.DataPoint,
uh.DataValueNew,
uh.DataValueOld
FROM (SELECT Id,TS,DPId,DataValueNew,DataValueOld,UpdateType FROM UpdateHistory
WHERE DPId in ('''+@DPId1+''','''+@DPId2+''') AND TS BETWEEN '+@T0+' AND '+ @T1+') uh INNER JOIN DataPointList dpl ON dpl.DPId = uh.DPId INNER JOIN ((SELECT Id,FundId,Name,Status,Symbol FROM FundShareClass WHERE ShareClassTypeId!='+'''LW'''+' or ShareClassTypeId is null) fsc INNER JOIN (Select Id,FundName,FamilyName FROM FundBasics WHERE FamilyName = '+@FamilyName+') fb ON fsc.FundId = fb.Id) ON uh.Id = fsc.Id' EXEC sp_executesql @sql END
Invalid column name 'Barclays'.能否再帮我看一下,另外,为什么在 @DPId1和@DPId2 上要加上三个单引号?
改成 where colname = Test的话就会报
Invalid column name Test这个错误
CREATE PROCEDURE dbo.Report_KeyClientOperationData
@T0 nvarchar(20),
@T1 nvarchar(20),
@FamilyName nvarchar(50),
@DPId1 nvarchar(10),
@DPId2 nvarchar(10)
AS BEGIN DECLARE @sql nvarchar(2000) SET @sql = 'SELECT
fsc.Name,
fsc.Symbol as Ticker,
dpl.DataPoint,
uh.DataValueNew,
uh.DataValueOld
FROM (SELECT Id,TS,DPId,DataValueNew,DataValueOld,UpdateType FROM UpdateHistory
WHERE DPId in ('''+@DPId1+''','''+@DPId2+''') AND TS BETWEEN '''+@T0+''' AND '''+ @T1+''') uh INNER JOIN DataPointList dpl ON dpl.DPId = uh.DPId INNER JOIN ((SELECT Id,FundId,Name,Status,Symbol FROM FundShareClass WHERE ShareClassTypeId!='+'''LW'''+' or ShareClassTypeId is null) fsc INNER JOIN (Select Id,FundName,FamilyName FROM FundBasics WHERE FamilyName = '''+@FamilyName+''') fb ON fsc.FundId = fb.Id) ON uh.Id = fsc.Id' EXEC sp_executesql @sql END