declare @strResult varchar(8000)
declare @str varchar(8000)
declare @i int
set @strResult=''
set @i=0
set @str='Account,GroupID,FaxNum,Name,Company,PhoneNum,Email,part'
while patindex('%,%',@str)<>0
begin
if @i = 3
break
set @i =@i +1
set @strResult=@strResult+','+substring(@str,0,patindex('%,%',@str))
set @str=substring(@str,patindex('%,%',@str)+1,len(@str))
end
print stuff(@strResult,1,1,'')
declare @str varchar(8000)
declare @i int
set @strResult=''
set @i=0
set @str='Account,GroupID,FaxNum,Name,Company,PhoneNum,Email,part'
while patindex('%,%',@str)<>0
begin
if @i = 3
break
set @i =@i +1
set @strResult=@strResult+','+substring(@str,0,patindex('%,%',@str))
set @str=substring(@str,patindex('%,%',@str)+1,len(@str))
end
print stuff(@strResult,1,1,'')
set QUOTED_IDENTIFIER ON
GO
create function test(@str varchar(8000),@cnt int)
returns varchar(8000)
as
begin
declare @strResult varchar(8000)declare @i int
set @strResult=''
set @i=0while patindex('%,%',@str)<>0
begin
if @i = @cnt
break
set @i =@i +1
set @strResult=@strResult+','+substring(@str,0,patindex('%,%',@str))
set @str=substring(@str,patindex('%,%',@str)+1,len(@str))
end
return stuff(@strResult,1,1,'')end
as
declare @s varchar(1000)
set @r=''
set @s='Account,GroupID,FaxNum,Name,Company,PhoneNum,Email,part'
while @c>0
begin
set @r=@r+left(@s,charindex(',',@s))
set @s=right(@s,len(@s)-charindex(',',@s))
set @c=@c-1
end
set @r=left(@r,len(@r)-1)
return
GOdeclare @r varchar(100)
exec sp 2,@r output --2就是要传入的参数
select @r as result/**
result
----------------------------------------------------------------------------------------------------
Account,GroupID(所影响的行数为 1 行)
**/
create table ta (id int , name varchar(100))
insert into ta
select 1,'a,b,c,d,e'create function func_a(@val int)
returns varchar(100)
as
begin
declare @strs varchar(100)
declare @re varchar(50)
declare @n int
set @n = 0
set @strs = ''
set @re = ''
select @strs = name from ta where id = 1
while @n < @val
begin
set @n = @n + 1
select @re = @re +substring(@strs,1,charindex(',',@strs,0)) from ta
select @strs = substring(@strs,charindex(',',@strs,0)+1,len(@strs)-charindex(',',@strs,0))
end
return substring(@re,1,len(@re)-1)
endselect dbo.func_a(3) as name from ta
/*
name
a,b,c
*/
declare @传入参数 as varchar(50)
set @传入参数 = '....'exec('select ' + @传入参数 + ' from tb')
exec('select ' + @传入参数 + ' Name,Company,PhoneNum,Email,part from tb')