最后一句改成这个:
print('select * from tb_Account where Id in (CASE WHEN '+@accountType+'=1 THEN '+@accountList+' ELSE N'''+@accountId+''' END)')贴结果
print('select * from tb_Account where Id in (CASE WHEN '+@accountType+'=1 THEN '+@accountList+' ELSE N'''+@accountId+''' END)')贴结果
解决方案 »
- sp_send_dbmail
- BIT数据类型到NVARCHAR数据类型的转换问题
- 20分求一条SQL查询语句。
- Select into 出現重復值的問題
- 求救!!急急急 win98下没法安装sqlserver2000 个人版
- vb.net中如何让DataGridView1控件一次显示多个表?
- 请问如何把HTML代码原封不动的存入数据库?我现在可以存进去,但"全都变成"
- 一个值得关注的实际问题!请每一个朋友都进来看看!
- VFP编译成可执行文件EXE后,运行时为什么一闪后就没了?(在线等待结果,急,谢谢)
- 有没有办法删除表里相同的记录,只留一条,用一句SQL
- SQL server 2008查询数据库的存储过程传递参数失败,求帮助?
- 附加数据库出错 5125
消息 245,级别 16,状态 1,第 10 行
在将 varchar 值 'select * from tb_Account where Id in (CASE WHEN ' 转换成数据类型 int 时失败。
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'
消息 245,级别 16,状态 1,第 10 行
在将 varchar 值 'select * from tb_Account where Id in (CASE WHEN ' 转换成数据类型 int 时失败。exec('select * from tb_Account where Id in (CASE WHEN '+cast(@accountType as varchar(20))+'=1 THEN '+@accountList+' ELSE N'''+@accountId+''' END)')
消息 245,级别 16,状态 1,第 10 行
在将 varchar 值 'select * from tb_Account where Id in (CASE WHEN ' 转换成数据类型 int 时失败。exec('select * from tb_Account where Id in (CASE WHEN '+cast(@accountType as varchar(20))+'=1 THEN '+@accountList+' ELSE N'''+@accountId+''' END)')加了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)
也要报错。
select a.*
from tb_Account a
join tb_Account b on a.id = case @accountType when 1 then b.id else @accountId end换成join 吧
from tb_Account
where id = case when @accountType = 1 then id else @accountId end
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' 附近有语法错误。
or ('+cast(@accountType as varchar(20))+'<>1 and id in ('+@accountId+'))
if(@accountType=1)
exec('select ....')
else
exec('select')这样形式了
感 谢 大 家