存储过程中可以接数组么
小弟有一个功能想写存储过程来执行，可是我不知道SQL 2008 怎么接收一个数组类型的参数~~请教各位大侠~怎么撸~ 有个例子~那是最好的~~3~Q~
存储 sql
[解决办法]
create proc usp_test
@p0 varchar(500)
as
begin
create table #ta(id varchar(50))
insert into #ta
select @p0
;with cte as
(
select left(id,CHARINDEX(',',id)-1) as a,
right(id,len(id)-CHARINDEX(',',id))b
from #ta
union all
select left(b,CHARINDEX(',',b)-1) as a,
right(b,len(b)-CHARINDEX(',',b))b
from cte
where len(b)>1
)
select a from cte
drop table #ta
end
go
exec usp_test '1,2,3,4,5,'
---------------------------------------------
(1 行受影响)
a
--------------------------------------------------
1
2
3
4
5
--创建自定义类型，这里的自定义类型是表，可存储记录
CREATE TYPE MyTable AS TABLE
( a VARCHAR(50),
b INT )
GO
--drop proc proc_table
--创建存储过程，传入参数为自定义表
CREATE Procedure dbo.proc_table
(@ManyRows as MyTable readonly
)
as
select * from @manyrows
go
declare @t MyTABLE
--可以直接把数据添加到LocationTableType类型中
--可以存储多条记录
insert into @t
select '123',1
union all
select 'abc',2
exec proc_table @t
/*
a b
-------------------------------------------------- -----------
123 1
abc 2
(2 行受影响)
*/
alter proc usp_test
@p0 varchar(500)
as
begin
create table #ta(id varchar(500))
insert into #ta
select @p0
;with cte as
(
select left(id,CHARINDEX(',',id)-1) as a,
right(id,len(id)-CHARINDEX(',',id))b
from #ta
union all
select left(b,CHARINDEX(',',b)-1) as a,
right(b,len(b)-CHARINDEX(',',b))b
from cte
where len(b)>1
)
select a from cte
drop table #ta
end
go
exec usp_test 'F7B857E7-6079-4D67-B3E7-3AF4824309A5,CE7274B8-F738-4F96-87EF-0461AEA50FF3,E2A6C825-E1A4-4917-B248-544631B21957,'
/*
a
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
F7B857E7-6079-4D67-B3E7-3AF4824309A5
CE7274B8-F738-4F96-87EF-0461AEA50FF3
E2A6C825-E1A4-4917-B248-544631B21957
*/
--数据库的 字符串分割方法.
Create FUNCTION [dbo].[Split]
(
@List nvarchar(MAX),
@SplitOn nvarchar(1)
)
RETURNS @RtnValue table (
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
INSERT INTO @RtnValue
SELECT SUBSTRING(@List, n.Rn, CHARINDEX(@SplitOn, @List + @SplitOn, n.Rn) - n.Rn) AS [Value]
FROM dbo.Nums n
WHERE n.Rn <= LEN(@List)
AND SUBSTRING(@SplitOn + @List, n.Rn, 1) =@SplitOn;
Return
END
GO
