我写了一个表值函数：
- SQL code
ALTER function [dbo].[split](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(20)) as begin while(charindex(@split,@c)<>0) begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) set @c = stuff(@c,1,charindex(@split,@c),'') end insert @t(col) values (@c) return end
然后使用下面的语句，这样报错，ParentStr是NewsBoard的字段，请问如何解决？：
"ParentStr" 不是可识别的表提示选项。如果它要作为表值函数的参数，请确保您的数据库兼容模式设置为 90。
- SQL code
ALTER PROCEDURE [dbo].[ShowCurrentBoardNavigation]@BoardID int ASbeginselect BoardID,BoardName,ParentStr,ParentID from News_Board bwhere BoardID =@Boardid and Convert(varchar(10),@BoardID) in (select * from split(ParentStr,','))end
------解决方案--------------------
顶！
------解决方案--------------------
select * from dbo.split(...)
------解决方案--------------------
- SQL code
1.用ParentStr作参数不行2.去掉函数，改如下:ALTER PROCEDURE [dbo].[ShowCurrentBoardNavigation]@BoardID int ASbeginselect BoardID,BoardName,ParentStr,ParentID from News_Board bwhere BoardID =@Boardid and charindex(','+ltrim(@BoardId)+',',','+ParentStr+',')>0end
------解决方案--------------------
http://blog.csdn.net/lihan6415151528/archive/2009/08/10/4431237.aspx
------解决方案--------------------
表值函数有这个限制,try:
- SQL code
ALTER PROCEDURE [dbo].[ShowCurrentBoardNavigation]@BoardID int ASbeginselect BoardID,BoardName,ParentStr,ParentID from News_Board bcross apply (select * from dbo.split(b.ParentStr,',')) as twhere b.BoardID =@Boardid and Convert(varchar(10),@BoardID) = t.colend
------解决方案--------------------
- SQL code
--更改数据库的兼容级别DECLARE @DBName VARCHAR(100)SELECT @DBName = db_name()EXEC sp_dbcmptlevel @DBName,90--然后执行存储过程EXEC [dbo].[ShowCurrentBoardNavigation] @BoardID = 1
------解决方案--------------------
- SQL code
--更改数据库的兼容级别DECLARE @DBName VARCHAR(100)SELECT @DBName = db_name()EXEC sp_dbcmptlevel @DBName,90GO--然后执行存储过程EXEC [dbo].[ShowCurrentBoardNavigation] @BoardID = 1