declare @i int,@name varchar(1024)
declare tb cursor for select name from CustInfo
open tb
set @i=1
fetch next from tb into @name
while @@fetch_status=1
begin
if @i=1 set @key1=@name
if @i=2 set @key2=@name
if @i=3 set @key3=@name
if @i=4 set @key4=@name
fetch next from tb into @name
set @i=@i+1
end
close tb
deallocate tb
解决方案 »
- 如何使用视图调用存储过程
- 这个sql脚本应该怎么写呢?
- 请教用OPENROWSET实现数据从sql<=>转换问题(难题,高手请进,友情UP有分)
- 紧急求救!SQL2000数据库一字段被删除,求教数据恢复!在线等
- SQL 2012系统管理方面问题,内存及客户资源管理
- 2台服务器 不同数据库 交互的问题(sql servlet)
- SQL Server does not exist or access denied.
- 急求:约束问题?
- 如何设一个字段为getdate(),用户在写入记录时,这个列自动写入当时的时间,时间精确到毫秒?
- 时间类型到底用datetime类型存储好还是用nvarchar存储好
- 再次提问!!大力,马克,邹建,这个是个什么理论??
- 在proxy server中想用数据库来存放缓冲的web对象,欢迎大家对表的字段和结构提出您的看法
declare @key2 varchar(1024)
declare @key3 varchar(1024)
declare @key4 varchar(1024)Select @key1 = (select name from CustInfo where id = 1)
,@key2 = (select name from CustInfo where id = 2)
,@key3 = (select name from CustInfo where id = 3)
,@key4 = (select name from CustInfo where id = 4)
我用你你的分离函数,但是我想把得到的放到变量中,如何放,最多4行,但有可能没有4行
declare @KeyString varchar(1024)
declare @Key1 varchar(1024)
declare @Key2 varchar(1024)
declare @Key3 varchar(1024)
declare @Key4 varchar(1024)
set @KeyString = '1@#2@#3@#4'
select istr from dbo.fsplit(@KeyString,'@#')
declare @key2 varchar(1024)
declare @key3 varchar(1024)
declare @key4 varchar(1024)Select @key1=name from CustInfo where id = 1
Select @key2=name from CustInfo where id = 2
Select @key3=name from CustInfo where id = 3
Select @key4=name from CustInfo where id = 4
declare @key2 varchar(1024)
declare @key3 varchar(1024)
declare @key4 varchar(1024)
create table #a(no [int] identity(1,1),name varchar(1024))
insert #a
select name from CustInfo
set @key1=select name from #a where no=1
set @key2=select name from #a where no=2
set @key3=select name form #a where no=3
set @key4=select name from #a where no=4
create function fsplit(
@str varchar(8000), --要分离的字符
@splichar varchar(10) --分隔符
) returns @re table(id int identity(1,1),istr varchar(100))
as
begin
--为分离表中的数据而设置临时表
declare @tb table(id int identity(1,1),a int)
insert into @tb(a)
select top 8000 1 from
(select top 100 id from syscolumns) a
,(select top 100 id from syscolumns) b
declare @lensplichar int
set @lensplichar=len(@splichar)
insert into @re(istr)
select substring(name,b.id,charindex(@splichar,name+@splichar,b.id)-b.id)
from (select name=@str) a,@tb b
where substring(@splichar+name,b.id,@lensplichar)=@splichar
return
end
go--处理你的数据
declare @KeyString varchar(1024)
declare @Key1 varchar(1024)
declare @Key2 varchar(1024)
declare @Key3 varchar(1024)
declare @Key4 varchar(1024)
set @KeyString = '1@#2@#3@#4'select * into #t from dbo.fsplit(@KeyString,'@#')
select @key1=istr from #t where id=1
select @key2=istr from #t where id=2
select @key3=istr from #t where id=3
select @key4=istr from #t where id=4drop table #t
go
drop function fsplit
create proc p_split
@str varchar(8000), --要分离的字符
@splichar varchar(10), --分隔符
@Key1 varchar(1024) out, --返回参数
@Key2 varchar(1024) out, --返回参数
@Key3 varchar(1024) out, --返回参数
@Key4 varchar(1024) out --返回参数
as
--为分离表中的数据而设置临时表
select top 8000 id=identity(int,1,1) into #tb from
(select top 100 id from syscolumns) a
,(select top 100 id from syscolumns) bdeclare @lensplichar int
set @lensplichar=len(@splichar)
select id=identity(int,1,1)
,a=substring(name,b.id,charindex(@splichar,name+@splichar,b.id)-b.id)
into #t from (select name=@str) a,#tb b
where substring(@splichar+name,b.id,@lensplichar)=@splicharselect @key1=a from #t where id=1
select @key2=a from #t where id=2
select @key3=a from #t where id=3
select @key4=a from #t where id=4
go--处理你的数据
declare @KeyString varchar(1024)
declare @Key1 varchar(1024)
declare @Key2 varchar(1024)
declare @Key3 varchar(1024)
declare @Key4 varchar(1024)
set @KeyString = '1@#2@#3@#4'exec p_split @KeyString,'@#',@Key1 out,@Key2 out,@Key3 out,@Key4 out
select @Key1,@Key2,@Key3,@Key4
go
drop proc p_split
@str varchar(8000), --要分拆的字符串
@spli varchar(10) --字符串分隔符
)
returns @re table(istr varchar(8000))
as
begin
declare @i int,@splen int
select @splen=len(@spli)
,@i=charindex(@spli,@str)
while @i>0
begin
insert into @re values(left(@str,@i-1))
select @str=substring(@str,@i+@splen,8000)
,@i=charindex(@spli,@str)
end
if @str<>'' insert into @re values(@str)
return
end