试试:ALTER PROCEDURE statustest
@StartTime varchar(100),
@EndTime varchar(100)
as
DECLARE @SQL NVARCHAR(1000)
DECLARE @sqlwhen NVARCHAR(1000)
begin set @SQL=' '
set @sqlwhen=' '
begin
SET @SQL='SELECT Id,SeminarTheme, Cost,Case When SeminarStatus=1 then '+'''pass'''+' When SeminarStatus=0 then 0 end AS SeminarStatus_1 FROM sfa_t_TBasSeminar_base WHERE 1=1 '+@sqlwhen+''
EXEC(@SQL);
--PRINT(@SQL);
end
end GO
exec statustest N'2013-05-12',N'2015-05-12'
@StartTime varchar(100),
@EndTime varchar(100)
as
DECLARE @SQL NVARCHAR(1000)
DECLARE @sqlwhen NVARCHAR(1000)
begin set @SQL=' '
set @sqlwhen=' '
begin
SET @SQL='SELECT Id,SeminarTheme, Cost,Case When SeminarStatus=1 then '+'''pass'''+' When SeminarStatus=0 then 0 end AS SeminarStatus_1 FROM sfa_t_TBasSeminar_base WHERE 1=1 '+@sqlwhen+''
EXEC(@SQL);
--PRINT(@SQL);
end
end GO
exec statustest N'2013-05-12',N'2015-05-12'
'?' 附近有语法错误。
输出的sql语句如下:
SELECT Id,SeminarTheme,Cost1+Cost2+BreakfastCost+LunchCost+DinnerCost as Cost,Case When SeminarStatus=1 then?'pass'?When SeminarStatus=0 then?'pass'?end AS SeminarStatus_1 FROM sfa_t_TBasSeminar_base WHERE convert(varchar(10),ConductTime,121) >='2013-05-21' and convert(varchar(10),ConductTime,121) <='2015-05-21'
在将 varchar 值 'pass' 转换成数据类型 int 时失败。我还有个判断条件是开始时间和结束时间,那有个类型转换
set @sqlwhen=@sqlwhen+' convert(varchar(10),ConductTime,121) >='''+@startTime+''' '
我这应该怎么转换
@StartTime varchar(100),
@EndTime varchar(100)
as
DECLARE @SQL NVARCHAR(1000)
DECLARE @sqlwhen NVARCHAR(1000)
begin set @SQL=' '
set @sqlwhen=' '
begin
SET @SQL='SELECT Id,SeminarTheme, Cost,Case When SeminarStatus=1 then ''PASS'' When SeminarStatus=0 then ''0'' end AS SeminarStatus_1 FROM sfa_t_TBasSeminar_base WHERE 1=1 '+@sqlwhen+''
EXEC(@SQL);
--PRINT(@SQL);
end
end