34,590
社区成员
发帖
与我相关
我的任务
分享
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
--更改数据库的兼容级别
DECLARE @DBName VARCHAR(100)
SELECT @DBName = db_name()
EXEC sp_dbcmptlevel @DBName,90
GO
--然后执行存储过程
EXEC [dbo].[ShowCurrentBoardNavigation] @BoardID = 1
--更改数据库的兼容级别
DECLARE @DBName VARCHAR(100)
SELECT @DBName = db_name()
EXEC sp_dbcmptlevel @DBName,90
--然后执行存储过程
EXEC [dbo].[ShowCurrentBoardNavigation] @BoardID = 1
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
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