日期:2014-05-17 浏览次数:20558 次
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
ALTER PROCEDURE [dbo].[ShowCurrentBoardNavigation] @BoardID int AS begin select BoardID,BoardName,ParentStr,ParentID from News_Board b where BoardID =@Boardid and Convert(varchar(10),@BoardID) in (select * from split(ParentStr,',')) end
1.用ParentStr作参数不行 2.去掉函数,改如下: ALTER PROCEDURE [dbo].[ShowCurrentBoardNavigation] @BoardID int AS begin select BoardID,BoardName,ParentStr,ParentID from News_Board b where BoardID =@Boardid and charindex(','+ltrim(@BoardId)+',',','+ParentStr+',')>0 end
------解决方案--------------------
http://blog.csdn.net/lihan6415151528/archive/2009/08/10/4431237.aspx
------解决方案--------------------
表值函数有这个限制,try:
ALTER PROCEDURE [dbo].[ShowCurrentBoardNavigation] @BoardID int AS begin select BoardID,BoardName,ParentStr,ParentID from News_Board b cross apply (select * from dbo.split(b.ParentStr,',')) as t where b.BoardID =@Boardid and Convert(varchar(10),@BoardID) = t.col end
------解决方案--------------------
--更改数据库的兼容级别 DECLARE @DBName VARCHAR(100) SELECT @DBName = db_name() EXEC sp_dbcmptlevel @DBName,90 --然后执行存储过程 EXEC [dbo].[ShowCurrentBoardNavigation] @BoardID = 1
------解决方案--------------------
--更改数据库的兼容级别 DECLARE @DBName VARCHAR(100) SELECT @DBName = db_name() EXEC sp_dbcmptlevel @DBName,90 GO --然后执行存储过程 EXEC [dbo].[ShowCurrentBoardNavigation] @BoardID = 1