存储过程字符定义解决思路
存储过程字符定义
查询语句如下
SELECT Id,SeminarTheme,Cost1+Cost2+BreakfastCost+LunchCost+DinnerCost as Cost,Case When SeminarStatus=1 then 'pass' When SeminarStatus=0 then 'no' end AS SeminarStatus FROM sfa_t_TBasSeminar_base
case,when语句,我想把这个写成一个存储过程,如下:(出现问题的地方是 SET @SQL中‘pass’的定义,怎么写)
create 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'+@sqlwhen+''
EXEC(@SQL);
PRINT(@SQL);
end
end
测试语句
exec statustest N'2013-05-12',N'2015-05-12'
错误提示:
列名 'pass' 无效。
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'+@sqlwhen+''
这个种的“PASS”我改怎么写,括号的问题?
------解决方案--------------------
试试:
查询语句如下
SELECT Id,SeminarTheme,Cost1+Cost2+BreakfastCost+LunchCost+DinnerCost as Cost,Case When SeminarStatus=1 then 'pass' When SeminarStatus=0 then 'no' end AS SeminarStatus FROM sfa_t_TBasSeminar_base
case,when语句,我想把这个写成一个存储过程,如下:(出现问题的地方是 SET @SQL中‘pass’的定义,怎么写)
create 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'+@sqlwhen+''
EXEC(@SQL);
PRINT(@SQL);
end
end
测试语句
exec statustest N'2013-05-12',N'2015-05-12'
错误提示:
列名 'pass' 无效。
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'+@sqlwhen+''
这个种的“PASS”我改怎么写,括号的问题?
------解决方案--------------------
试试:
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'