27,579
社区成员
发帖
与我相关
我的任务
分享
set nocount on
if object_id('dbo.fn_split')is not null drop function dbo.fn_split --自定义函数
go
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
if object_id('P_Info')is not null drop table P_Info -->测试表
go
create table P_Info(name varchar(5),sex varchar(5),age varchar(10),[Work] varchar(10),Meno varchar(10) )
insert P_Info select '张三','男',18,'医生','好医生'
insert P_Info select '王三','男',28,'教师','高级教师'
insert P_Info select '赵三','女',38,'IT','没什么出息'
go
if object_id('P_GetInfo')is not null drop proc P_GetInfo --定义存储过程
go
create proc P_GetInfo
@Name varchar(50),
@Sex varchar(20),
@Age varchar(20)
as
set nocount on
declare @t1 table(ID int identity(1,1),name varchar(5))
declare @t2 table(ID int identity(1,1),Sex varchar(5))
declare @t3 table(ID int identity(1,1),age varchar(5))
insert @t1 select * from dbo.fn_split(@Name,',')
insert @t2 select * from dbo.fn_split(@Sex,',')
insert @t3 select * from dbo.fn_split(@age,',')
select P.* from P_Info p inner join @t1 t1 on p.name=t1.name
inner join @t2 t2 on p.Sex=t2.Sex
inner join @t3 t3 on p.age=t3.age
and t1.id=t2.id and t2.id=t3.id
go
exec P_GetInfo '张三,赵三','男,女','18,38'
/*
name sex age Work Meno
----- ----- ---------- ---------- ----------
张三 男 18 医生 好医生
赵三 女 38 IT 没什么出息*/
/*
功能:实现split功能的函数
*/
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(1000)
set @s='1,2,3,4,5,6,7,8,55'
select * from dbo.fn_split(@s,',')
drop function dbo.fn_split
CREATE PROC spXXX @NameList XML
AS
DECLARE @r TABLE(Name varchar(10),age int,sex bit)
....
INSERT INTO @r
SELECT *
FROM OPENXML (....)