动态SQL语法错误

renliquan 2014-05-12 05:54:06



DECLARE @accountId nvarchar(36),@accountList nvarchar(4000),@accountType int

SET @accountId='2ef51e92-931e-4cbc-8d0c-9502cc2fa32b'

SET @accountType= 1

SET @accountList=''

SELECT @accountList = @accountList +''''+ Id + ''',' FROM tb_Account

SET @accountList = left(@accountList,len(@accountList)-1)



--这句不会报错

--exec('select * from tb_Account where 1=1 and Id in ('+@accountList+')')



--这句就要报错：消息 102，级别 15，状态 1，第 1 行 ',' 附近有语法错误。

exec('select * from tb_Account where Id in (CASE WHEN '+@accountType+'=1 THEN '+@accountList+' ELSE N'''+@accountId+''' END)')

...全文

268 16 打赏收藏转发到动态举报

写回复

用AI写文章

16 条回复

切换为时间正序

请发表友善的回复…

发表回复

renliquan 2014-05-13

打赏
举报

引用 15 楼 DBA_Huangzj 的回复:

那就print @accountType 这个看看

算了，不搞了，头都大了。我还是写成 if(@accountType=1) exec('select ....') else exec('select') 这样形式了。。。感谢大家

發糞塗牆 2014-05-13

打赏
举报

那就print @accountType 这个看看

renliquan 2014-05-13

打赏
举报

引用 12 楼 DBA_Huangzj 的回复:

把exec改成print看看

Print不出来。报一样的错。

sdhp 2014-05-13

打赏
举报

exec('select * from tb_Account where ('+cast(@accountType as varchar(20))+'=1 and id in ('+@accountList+')) or ('+cast(@accountType as varchar(20))+'<>1 and id in ('+@accountId+'))

發糞塗牆 2014-05-13

打赏
举报

把exec改成print看看

renliquan 2014-05-13

打赏
举报

引用 9 楼 DBA_Huangzj 的回复:

DECLARE @accountId nvarchar(36),@accountList nvarchar(4000),@accountType int SET @accountId='2ef51e92-931e-4cbc-8d0c-9502cc2fa32b' SET @accountType= 1 SET @accountList='' SELECT @accountList = @accountList +''''+ Id + ''',' FROM tb_Account SET @accountList = left(@accountList,len(@accountList)-1) exec('select * from tb_Account where Id in (CASE WHEN '+@accountType+'='1' THEN '+@accountList+' ELSE N'''+@accountId+''' END)')

这段SQL运行结果如下：消息 102，级别 15，状态 1，第 9 行 '1' 附近有语法错误。

exception92 2014-05-13

打赏
举报

看case 中的等号两侧的类型一致不？？

發糞塗牆 2014-05-13

打赏
举报

DECLARE @accountId nvarchar(36),@accountList nvarchar(4000),@accountType int SET @accountId='2ef51e92-931e-4cbc-8d0c-9502cc2fa32b' SET @accountType= 1 SET @accountList='' SELECT @accountList = @accountList +''''+ Id + ''',' FROM tb_Account SET @accountList = left(@accountList,len(@accountList)-1) exec('select * from tb_Account where Id in (CASE WHEN '+@accountType+'='1' THEN '+@accountList+' ELSE N'''+@accountId+''' END)')

renliquan 2014-05-12

打赏
举报

引用 6 楼 zc040921 的回复:


select a.* 
from tb_Account a
	join tb_Account b on a.id = case @accountType when 1 then b.id else @accountId end

换成join 吧

Join也要in呀，还是一样会报错。

MootShao 2014-05-12

打赏
举报

select a.* from tb_Account where id = case when @accountType = 1 then id else @accountId end

MootShao 2014-05-12

打赏
举报


select a.* 
from tb_Account a
	join tb_Account b on a.id = case @accountType when 1 then b.id else @accountId end

换成join 吧

renliquan 2014-05-12

打赏
举报

引用 4 楼 zc040921 的回复:

[quote=引用 2 楼 renliquan 的回复:] [quote=引用 1 楼 DBA_Huangzj 的回复:] 最后一句改成这个： print('select * from tb_Account where Id in (CASE WHEN '+@accountType+'=1 THEN '+@accountList+' ELSE N'''+@accountId+''' END)') 贴结果

消息 245，级别 16，状态 1，第 10 行在将 varchar 值 'select * from tb_Account where Id in (CASE WHEN ' 转换成数据类型 int 时失败。[/quote] exec('select * from tb_Account where Id in (CASE WHEN '+cast(@accountType as varchar(20))+'=1 THEN '+@accountList+' ELSE N'''+@accountId+''' END)') [/quote] 加了cast后，print出来是这种，


select * from tb_Account where Id in (CASE WHEN 1=1 THEN '01b58710-cadd-4c2c-aa5d-26a595bed3ac','0601013e-53aa-429c-a757-3934f08d133c','0df2bd3f-87a6-486e-82d8-f33875771453','2ef51e92-931e-4cbc-8d0c-9502cc2fa32b','2f31219f-68f6-4a1b-905f-a536b40d6400','343c8cee-efad-4e59-81fd-110bf98f8112','3d86bc68-e178-4334-b710-f69b17221058','3fb7dee8-31ad-499a-902b-5f3c6e49f883','41e0f2b7-0f6c-4b77-b297-6c525bcfb075','4cea78f4-4f35-4974-b34f-ef6405849341','69733bb8-6a4e-4745-91a3-7bb14115b9b9','6a76bde3-5210-4484-87b6-a8df842660b3','9757f81f-c23e-4324-93c7-df670ef26f15','a2eeba6b-2abf-4531-bd99-3b9fa073a10e','b5f05215-b58b-4a3c-921d-ec43e35f627a','b65713df-58ba-4e7f-b380-ea6e8a741058','c1797b47-28d6-406f-8e7c-5887c7b3ad05','dfbf327c-bcc7-4eb3-adf6-cb924ed19a42','f17ba0b1-a881-4ae0-9717-024df0bd7d67' ELSE N'2ef51e92-931e-4cbc-8d0c-9502cc2fa32b' END)

也要报错。

MootShao 2014-05-12

打赏
举报

引用 2 楼 renliquan 的回复:

[quote=引用 1 楼 DBA_Huangzj 的回复:] 最后一句改成这个： print('select * from tb_Account where Id in (CASE WHEN '+@accountType+'=1 THEN '+@accountList+' ELSE N'''+@accountId+''' END)') 贴结果

renliquan 2014-05-12

打赏
举报


exec('select * from tb_Account where 1=1 and Id in ('+@accountList+')')

exec('select * from tb_Account where Id in (CASE WHEN '+@accountType+'=1 THEN N'''+@accountId+''' ELSE N'''+@accountId+''' END)')

这两种写法都可以出结果。第二句传AccountList就出错。 AccountList得到的结果是这种型式： '01b58710-cadd-4c2c-aa5d-26a595bed3ac','0601013e-53aa-429c-a757-3934f08d133c','0df2bd3f-87a6-486e-82d8-f33875771453','2ef51e92-931e-4cbc-8d0c-9502cc2fa32b','2f31219f-68f6-4a1b-905f-a536b40d6400','343c8cee-efad-4e59-81fd-110bf98f8112','3d86bc68-e178-4334-b710-f69b17221058','3fb7dee8-31ad-499a-902b-5f3c6e49f883','41e0f2b7-0f6c-4b77-b297-6c525bcfb075','4cea78f4-4f35-4974-b34f-ef6405849341','69733bb8-6a4e-4745-91a3-7bb14115b9b9','6a76bde3-5210-4484-87b6-a8df842660b3','9757f81f-c23e-4324-93c7-df670ef26f15','a2eeba6b-2abf-4531-bd99-3b9fa073a10e','b5f05215-b58b-4a3c-921d-ec43e35f627a','b65713df-58ba-4e7f-b380-ea6e8a741058','c1797b47-28d6-406f-8e7c-5887c7b3ad05','dfbf327c-bcc7-4eb3-adf6-cb924ed19a42','f17ba0b1-a881-4ae0-9717-024df0bd7d67'

renliquan 2014-05-12