拼接语句
SET @sysMessageStr = N'select id as [@ID],messageTitle as [@title], messageType as [@messageType] ,'''+CAST(dbo.getMessageIsRead(id,@userName) as nvarchar)+''' as [isRead], ''author'' as [author] ,createTime as [@createTime] from'
from 后面的表也是动态的
其中 getMessageIsRead(id,@userName) 是自定义的一个函数,sql一直报 id 列名无效,如果不用动态语句拼接的话就没问题,这个咋个改呢?
SET @sysMessageStr = N'select id as [@ID],messageTitle as [@title], messageType as [@messageType] ,'''+CAST(dbo.getMessageIsRead(id,@userName) as nvarchar)+''' as [isRead], ''author'' as [author] ,createTime as [@createTime] from'
from 后面的表也是动态的
其中 getMessageIsRead(id,@userName) 是自定义的一个函数,sql一直报 id 列名无效,如果不用动态语句拼接的话就没问题,这个咋个改呢?
这个 dbo.getMessageIsRead(id,@userName) 就是对的
这个getMessageIsRead 函数就是对每条记录做检查,是否阅读,用列名id来当参数进行传递,message表中存在这个叫id的列名
先执行这个
得到结果字符串以后再跟N'select id as [@ID],messageTitle as [@title], messageType 拼接
--SQL:
select
id as [@ID],
messageTitle as [@title],
messageType as [@messageType] ,
'zzz' as [isRead], --Getmessageisread
'author' as [author] ,
createTime as [@createTime]
from [message]--#1. 把CAST(dbo.Getmessageisread(id, @userName) AS NVARCHAR)两边的'号去掉(如果Getmessageisread函数返回的是字符串的话).
--#2. Getmessageisread函数中的id参数确实没有传值.你上面的sql等于:
--SET @串 = N'A串' + CAST(dbo.Getmessageisread(id, @userName) AS NVARCHAR) + N'B串'
--并未指定id从哪儿来
确实是没传值,我就是不晓得该咋个写这个了 ,getMessageIsRead 返回的是int
像这种动态 sql 拼接,需要用自定义函数,用列名当参数的到底该怎么写呢?
好像这样也不行把,这个id 是列名,比如套在from message 才行的吧
select id as [@ID],
messageTitle as [@title],
messageType as [@messageType] ,
CAST(dbo.getMessageIsRead(id,'+@userName+') as nvarchar) as [isRead],
''author'' as [author] ,
createTime as [@createTime]
from ' +@TB