因为他犯了个比较低级的错误,多个条件要用in而不是=,就像我在6楼说的那样ALTER PROCEDURE proc_test( @BUSList nvarchar(max) ) As
Declare @tmpBusList nvarchar(max)
set @tmpBusList=replace(@BUSList,',',''',''')
EXEC ('Select * from tb Where Bus in ('''+@tmpBusList+''')')
GO
Exec proc_test @BUSList='A103U5,A88888'
版主,问题已经得到了解决,还有个小问题就是为什么非要EXEC来先运行? EXEC ('Select * from tb Where Bus in ('''+@tmpBusList+''')') 我用 'Select * from tb Where Bus in ('''+@tmpBusList+''') 套进存储过程运行却不行呢?是什么原理
我套进我的存储过程后出错CREATE PROCEDURE clbk @BUSList nvarchar(4000), @year varchar(20)AS Declare @tmpBusList nvarchar(4000) set @tmpBusList=replace(@BUSList,',',''',''') exec ('select SpCheckSum.cs_bus AS 车号, Line.c_li_name AS 线路名称, SoalCodeDb.c_fullname AS 车属单位, SpCheckSum.cs_seat as 座位,dbo.fun_DX(MONTH(SpCheckSum.cs_date)) AS 月份, ISNULL(CAST(sum(case when day(cs_date)=1 then 1 else null end)AS VARCHAR(10)),'×') '1日', ISNULL(CAST(sum(case when day(cs_date)=2 then 1 else null end)AS VARCHAR(10)),'×') '2日', ISNULL(CAST(sum(case when day(cs_date)=3 then 1 else null end)AS VARCHAR(10)),'×') '3日', ISNULL(CAST(sum(case when day(cs_date)=4 then 1 else null end)AS VARCHAR(10)),'×') '4日', ISNULL(CAST(sum(case when day(cs_date)=5 then 1 else null end)AS VARCHAR(10)),'×') '5日', ISNULL(CAST(sum(case when day(cs_date)=6 then 1 else null end)AS VARCHAR(10)),'×') '6日', ISNULL(CAST(sum(case when day(cs_date)=7 then 1 else null end)AS VARCHAR(10)),'×') '7日', ISNULL(CAST(sum(case when day(cs_date)=8 then 1 else null end)AS VARCHAR(10)),'×') '8日', ISNULL(CAST(sum(case when day(cs_date)=9 then 1 else null end)AS VARCHAR(10)),'×') '9日', ISNULL(CAST(sum(case when day(cs_date)=10 then 1 else null end)AS VARCHAR(10)),'×') '10日', ISNULL(CAST(sum(case when day(cs_date)=11 then 1 else null end)AS VARCHAR(10)),'×') '11日', ISNULL(CAST(sum(case when day(cs_date)=12 then 1 else null end)AS VARCHAR(10)),'×') '12日', ISNULL(CAST(sum(case when day(cs_date)=13 then 1 else null end)AS VARCHAR(10)),'×') '13日', ISNULL(CAST(sum(case when day(cs_date)=14 then 1 else null end)AS VARCHAR(10)),'×') '14日', ISNULL(CAST(sum(case when day(cs_date)=15 then 1 else null end)AS VARCHAR(10)),'×') '15日', ISNULL(CAST(sum(case when day(cs_date)=16 then 1 else null end)AS VARCHAR(10)),'×') '16日', ISNULL(CAST(sum(case when day(cs_date)=17 then 1 else null end)AS VARCHAR(10)),'×') '17日', ISNULL(CAST(sum(case when day(cs_date)=18 then 1 else null end)AS VARCHAR(10)),'×') '18日', ISNULL(CAST(sum(case when day(cs_date)=19 then 1 else null end)AS VARCHAR(10)),'×') '19日', ISNULL(CAST(sum(case when day(cs_date)=20 then 1 else null end)AS VARCHAR(10)),'×') '20日', ISNULL(CAST(sum(case when day(cs_date)=21 then 1 else null end)AS VARCHAR(10)),'×') '21日', ISNULL(CAST(sum(case when day(cs_date)=22 then 1 else null end)AS VARCHAR(10)),'×') '22日', ISNULL(CAST(sum(case when day(cs_date)=23 then 1 else null end)AS VARCHAR(10)),'×') '23日', ISNULL(CAST(sum(case when day(cs_date)=24 then 1 else null end)AS VARCHAR(10)),'×') '24日', ISNULL(CAST(sum(case when day(cs_date)=25 then 1 else null end)AS VARCHAR(10)),'×') '25日', ISNULL(CAST(sum(case when day(cs_date)=26 then 1 else null end)AS VARCHAR(10)),'×') '26日', ISNULL(CAST(sum(case when day(cs_date)=27 then 1 else null end)AS VARCHAR(10)),'×') '27日', ISNULL(CAST(sum(case when day(cs_date)=28 then 1 else null end)AS VARCHAR(10)),'×') '28日', ISNULL(CAST(sum(case when day(cs_date)=29 then 1 else null end)AS VARCHAR(10)),'×') '29日', ISNULL(CAST(sum(case when day(cs_date)=30 then 1 else null end)AS VARCHAR(10)),'×') '30日', ISNULL(CAST(sum(case when day(cs_date)=31 then 1 else null end)AS VARCHAR(10)),'×') '31日' FROM SpCheckSum INNER JOIN Line ON SpCheckSum.cs_line = Line.c_li_id INNER JOIN SoalCodeDb ON SpCheckSum.cs_comp = SoalCodeDb.c_viewno WHERE(YEAR(SpCheckSum.cs_date) = @year) AND cs_bus in ('''+@tmpBusList+''') and (SpCheckSum.cs_disuser IS NULL) AND (SoalCodeDb.c_class = '营运单位') GROUP BY SpCheckSum.cs_bus, MONTH(SpCheckSum.cs_date), dbo.fun_DX(MONTH(SpCheckSum.cs_date)), Line.c_li_name, SoalCodeDb.c_fullname,SpCheckSum.cs_seat ORDER BY cs_bus,MONTH(SpCheckSum.cs_date)') GO说第十行'×'这里有语法错误。。
AS Declare @tmpBusList nvarchar(4000) set @tmpBusList=replace(@BUSList,',',''',''') exec ('select SpCheckSum.cs_bus AS 车号, Line.c_li_name AS 线路名称, SoalCodeDb.c_fullname AS 车属单位, SpCheckSum.cs_seat as 座位,dbo.fun_DX(MONTH(SpCheckSum.cs_date)) AS 月份, ISNULL(CAST(sum(case when day(cs_date)=1 then 1 else null end)AS VARCHAR(10)),''×'') ''1日'' FROM SpCheckSum INNER JOIN Line ON SpCheckSum.cs_line = Line.c_li_id INNER JOIN SoalCodeDb ON SpCheckSum.cs_comp = SoalCodeDb.c_viewno WHERE(YEAR(SpCheckSum.cs_date) = @year) AND cs_bus in ('''+@tmpBusList+''') and (SpCheckSum.cs_disuser IS NULL) AND (SoalCodeDb.c_class = ''营运单位'') GROUP BY SpCheckSum.cs_bus, MONTH(SpCheckSum.cs_date), dbo.fun_DX(MONTH(SpCheckSum.cs_date)), Line.c_li_name, SoalCodeDb.c_fullname,SpCheckSum.cs_seat ORDER BY cs_bus,MONTH(SpCheckSum.cs_date)') GO
用一个就行了。
create proc queryno
@bus varchar(10)
as
.............
create procedure proc_test(
@BUSList nvarchar(max)
)
AsDeclare @tmpBusList nvarchar(max)set @tmpBusList=replace(@BUSList,',',''',''')Exec ('Select * from tb Where Bus='''+@tmpBusList+'''')GO
Exec proc_test @BUSList='A103U5,A88888'
Exec SSS @BUSList='桂GA2336,桂B73306'
提示
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。
@BUSList nvarchar(max)
)
As
Declare @tmpBusList nvarchar(max)
set @tmpBusList=replace(@BUSList,',',''',''')
EXEC ('Select * from tb Where Bus in ('''+@tmpBusList+''')')
GO
Exec proc_test @BUSList='A103U5,A88888'
EXEC ('Select * from tb Where Bus in ('''+@tmpBusList+''')') 我用
'Select * from tb Where Bus in ('''+@tmpBusList+''')
套进存储过程运行却不行呢?是什么原理
@BUSList nvarchar(4000),
@year varchar(20)AS
Declare @tmpBusList nvarchar(4000)
set @tmpBusList=replace(@BUSList,',',''',''')
exec ('select SpCheckSum.cs_bus AS 车号, Line.c_li_name AS 线路名称,
SoalCodeDb.c_fullname AS 车属单位, SpCheckSum.cs_seat as 座位,dbo.fun_DX(MONTH(SpCheckSum.cs_date))
AS 月份, ISNULL(CAST(sum(case when day(cs_date)=1 then 1 else null end)AS VARCHAR(10)),'×') '1日',
ISNULL(CAST(sum(case when day(cs_date)=2 then 1 else null end)AS VARCHAR(10)),'×') '2日',
ISNULL(CAST(sum(case when day(cs_date)=3 then 1 else null end)AS VARCHAR(10)),'×') '3日',
ISNULL(CAST(sum(case when day(cs_date)=4 then 1 else null end)AS VARCHAR(10)),'×') '4日',
ISNULL(CAST(sum(case when day(cs_date)=5 then 1 else null end)AS VARCHAR(10)),'×') '5日',
ISNULL(CAST(sum(case when day(cs_date)=6 then 1 else null end)AS VARCHAR(10)),'×') '6日',
ISNULL(CAST(sum(case when day(cs_date)=7 then 1 else null end)AS VARCHAR(10)),'×') '7日',
ISNULL(CAST(sum(case when day(cs_date)=8 then 1 else null end)AS VARCHAR(10)),'×') '8日',
ISNULL(CAST(sum(case when day(cs_date)=9 then 1 else null end)AS VARCHAR(10)),'×') '9日',
ISNULL(CAST(sum(case when day(cs_date)=10 then 1 else null end)AS VARCHAR(10)),'×') '10日',
ISNULL(CAST(sum(case when day(cs_date)=11 then 1 else null end)AS VARCHAR(10)),'×') '11日',
ISNULL(CAST(sum(case when day(cs_date)=12 then 1 else null end)AS VARCHAR(10)),'×') '12日',
ISNULL(CAST(sum(case when day(cs_date)=13 then 1 else null end)AS VARCHAR(10)),'×') '13日',
ISNULL(CAST(sum(case when day(cs_date)=14 then 1 else null end)AS VARCHAR(10)),'×') '14日',
ISNULL(CAST(sum(case when day(cs_date)=15 then 1 else null end)AS VARCHAR(10)),'×') '15日',
ISNULL(CAST(sum(case when day(cs_date)=16 then 1 else null end)AS VARCHAR(10)),'×') '16日',
ISNULL(CAST(sum(case when day(cs_date)=17 then 1 else null end)AS VARCHAR(10)),'×') '17日',
ISNULL(CAST(sum(case when day(cs_date)=18 then 1 else null end)AS VARCHAR(10)),'×') '18日',
ISNULL(CAST(sum(case when day(cs_date)=19 then 1 else null end)AS VARCHAR(10)),'×') '19日',
ISNULL(CAST(sum(case when day(cs_date)=20 then 1 else null end)AS VARCHAR(10)),'×') '20日',
ISNULL(CAST(sum(case when day(cs_date)=21 then 1 else null end)AS VARCHAR(10)),'×') '21日',
ISNULL(CAST(sum(case when day(cs_date)=22 then 1 else null end)AS VARCHAR(10)),'×') '22日',
ISNULL(CAST(sum(case when day(cs_date)=23 then 1 else null end)AS VARCHAR(10)),'×') '23日',
ISNULL(CAST(sum(case when day(cs_date)=24 then 1 else null end)AS VARCHAR(10)),'×') '24日',
ISNULL(CAST(sum(case when day(cs_date)=25 then 1 else null end)AS VARCHAR(10)),'×') '25日',
ISNULL(CAST(sum(case when day(cs_date)=26 then 1 else null end)AS VARCHAR(10)),'×') '26日',
ISNULL(CAST(sum(case when day(cs_date)=27 then 1 else null end)AS VARCHAR(10)),'×') '27日',
ISNULL(CAST(sum(case when day(cs_date)=28 then 1 else null end)AS VARCHAR(10)),'×') '28日',
ISNULL(CAST(sum(case when day(cs_date)=29 then 1 else null end)AS VARCHAR(10)),'×') '29日',
ISNULL(CAST(sum(case when day(cs_date)=30 then 1 else null end)AS VARCHAR(10)),'×') '30日',
ISNULL(CAST(sum(case when day(cs_date)=31 then 1 else null end)AS VARCHAR(10)),'×') '31日'
FROM SpCheckSum INNER JOIN
Line ON SpCheckSum.cs_line = Line.c_li_id INNER JOIN
SoalCodeDb ON SpCheckSum.cs_comp = SoalCodeDb.c_viewno
WHERE(YEAR(SpCheckSum.cs_date) = @year) AND cs_bus in ('''+@tmpBusList+''') and
(SpCheckSum.cs_disuser IS NULL) AND (SoalCodeDb.c_class = '营运单位')
GROUP BY SpCheckSum.cs_bus, MONTH(SpCheckSum.cs_date),
dbo.fun_DX(MONTH(SpCheckSum.cs_date)), Line.c_li_name,
SoalCodeDb.c_fullname,SpCheckSum.cs_seat
ORDER BY cs_bus,MONTH(SpCheckSum.cs_date)')
GO说第十行'×'这里有语法错误。。
1、为什么要用exec我没怎么深究过,我一直都这样用的。
2、你用的时候,先把exec改成print,执行后把print出来的语句,复制到ssms里面执行一下,如果没问题才换成exec。
3、你先别放那么一大段进去,很难调试,你可以先放1行,好了再全面搞。
ISNULL(CAST(sum(case when day(cs_date)=1 then 1 else null end)AS VARCHAR(10)),'×') '不知道为什么
@BUSList nvarchar(4000),
@year varchar(20)
AS
Declare @tmpBusList nvarchar(4000)
set @tmpBusList=replace(@BUSList,',',''',''')
exec ('select SpCheckSum.cs_bus AS 车号, Line.c_li_name AS 线路名称,
SoalCodeDb.c_fullname AS 车属单位, SpCheckSum.cs_seat as 座位,dbo.fun_DX(MONTH(SpCheckSum.cs_date))
AS 月份, ISNULL(CAST(sum(case when day(cs_date)=1 then 1 else null end)AS VARCHAR(10)),''×'') ''1日''
FROM SpCheckSum INNER JOIN
Line ON SpCheckSum.cs_line = Line.c_li_id INNER JOIN
SoalCodeDb ON SpCheckSum.cs_comp = SoalCodeDb.c_viewno
WHERE(YEAR(SpCheckSum.cs_date) = @year) AND cs_bus in ('''+@tmpBusList+''') and
(SpCheckSum.cs_disuser IS NULL) AND (SoalCodeDb.c_class = ''营运单位'')
GROUP BY SpCheckSum.cs_bus, MONTH(SpCheckSum.cs_date),
dbo.fun_DX(MONTH(SpCheckSum.cs_date)), Line.c_li_name,
SoalCodeDb.c_fullname,SpCheckSum.cs_seat
ORDER BY cs_bus,MONTH(SpCheckSum.cs_date)')
GO
exec dbo.clbk @BUSList='桂B11637',@year=2011提示
服务器: 消息 137,级别 15,状态 2,行 38
必须声明变量 '@year'我不是已经申明了吗?
要把
WHERE(YEAR(SpCheckSum.cs_date) = @year)变成这样的格式才行谢谢版主了
WHERE(YEAR(SpCheckSum.cs_date) =''' +@year+''')