存储过程中变量和表操作的问题这是我的代码：SQL codeALTER PROCEDURE [dbo].[GETNEXTSEQUENCE](@SEQUENCEN
存储过程中变量和表操作的问题
这是我的代码：
SQL codeALTER PROCEDURE [dbo].[GETNEXTSEQUENCE] ( @SEQUENCENAME NVARCHAR(20) , @SEQUENCEVALUE NUMERIC(16,0) OUT ) AS /* 检查SEQUENCE表的完整性先 */ IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='SEQUENCE' AND TYPE='U') IF EXISTS(SELECT ID FROM SEQUENCE WHERE ID=0) BEGIN DECLARE @CURRENT NUMERIC(16,0) SET @CURRENT=(SELECT @SEQUENCENAME FROM SEQUENCE WHERE ID=0) IF @CURRENT IS NOT NULL BEGIN SET @SEQUENCEVALUE=@CURRENT+1 UPDATE SEQUENCE SET @SEQUENCENAME=@CURRENT+1 WHERE ID=0 END END RETURN;
其中我用变量@SEQUENCENAME来表示字段名，并用其进行SELECT和UPDATE的操作，这是不符合语法规范的。
请问，是否有方法能够实现类似的操作，即：通过传递变量来指定列名。
我这个存储过程实现的功能是，建立一个表，向其中添加列，每一个列就是一个SEQUENCE，实际上这个表永远只有一行，每取一次SEQUENCE值（有多少SEQUENCE列，就表示有多少个sequence），就更新一次值。
[解决办法]我这个存储过程实现的功能是，建立一个表，向其中添加列，每一个列就是一个SEQUENCE，实际上这个表永远只有一行，每取一次SEQUENCE值（有多少SEQUENCE列，就表示有多少个sequence），就更新一次值。
你要实现这个目标,建议使用函数和游标了.给你参考代码:
if exists(select * from sysobjects where name='tab1')
drop table tab1
go
create table tab1(
ID int,
NAME varchar(10),
QQ varchar(10),
PHONE varchar(20)
)
go
insert into tab1 values(1 ,'张无忌' ,'10102800' ,'13500000')
insert into tab1 values(2 ,'赵敏' ,'10378' ,'13600000')
insert into tab1 values(3 ,'韦小宝' ,'10000' ,'13900000')
if exists(select * from sysobjects where name='tab2')
drop table tab2
go
create table tab2(
ID int,
NAME varchar(10) ,
usetime datetime,
admin varchar(10)
)
go
insert into tab2 values(1,'张无忌' ,cast('2007-10-1' as datetime),'东邪')
insert into tab2 values(2,'张无忌' ,cast('2007-10-12' as datetime),'西毒')
insert into tab2 values (3,'赵敏' ,cast('2007-10-13' as datetime),'南帝')
insert into tab2 values(4,'张无忌' ,cast('2007-10-13' as datetime),'北丐')
insert into tab2 values(5,'赵敏' ,cast('2007-10-13' as datetime),'东邪')
insert into tab2 values(6,'张无忌' ,cast('2007-10-13' as datetime),'东邪')
insert into tab2 values(7,'韦小宝' ,cast('2007-10-13' as datetime),'鳌拜')
--创建函数，传入参数是网名，传出参数是该网友对应的版主
create function GetNameStr(@name nvarchar(10))
returns nvarchar(800)
as
begin
declare @nameStr nvarchar(800)
declare @tempStr nvarchar(800)
declare @flag int
declare myCur cursor for ( select admin from tab2 where tab2.NAME = @name )
open myCur
fetch next from myCur into @tempStr
set @flag = 0
while @@fetch_status = 0
begin
if @flag = 0
begin
set @nameStr = @tempStr
end
else
begin
set @nameStr = @nameStr + ',' + @tempStr
end
set @flag = @flag + 1
fetch next from myCur into @tempStr
end
close myCur
deallocate myCur
return @nameStr
end
select * from tab1
select * from tab2
SELECT tab1.NAME AS 姓名, COUNT(tab2.ID) AS 上机次数
FROM tab1 LEFT OUTER JOIN
tab2 ON tab1.NAME = tab2.NAME
GROUP BY tab1.name
select tab2.NAME as 姓名, count(ID) as 上机次数, dbo.GetNameStr(tab2.NAME) as 管理员
from tab2
where tab2.NAME in ( select tab1.NAME from tab1 )
group by tab2.NAME
[解决办法]create table sequence(id int,sequence int)
go
insert into sequence(id,sequence)values(0,10)
go
alter proc dbo.mypro
@colname nvarchar(50),
@value int output
as
begin
declare @sql as nvarchar(4000)
set @sql='declare @temp as int select @temp ='+@colname+' from sequence where id=0 '
set @sql=@sql+'update sequence set '+@colname+'=@temp+1'
--print @sql
exec(@sql)
end
mypro 'sequence',1
