select中变量是否支持代替多个字段?
RT,我想构造个变量来代替多个字段,但是写存储过程时,insert时我知道会提示字段个数不匹配,
请教高手有没有解决办法?叶子大哥,小三,小F来解决
PS:第一次在SQL版提问,希望高手给力啊!!
--表中有多个r字段,例如r1,r2,r3,r4,.....r100CREATE TABLE tab(期号 VARCHAR(5),r1 INT,r2 INT,r3 INT,r4 INT,r5 INT,类别 VARCHAR(5))INSERT INTO tab VALUES('0101',10,4,8,9,22,'A')INSERT INTO tab VALUES('0102',10,11,8,9,22,'B')CREATE PROCEDURE UpdateAddAS DECLARE @r VARCHAR(100) SET @r='' DECLARE @result VARCHAR(200) DECLARE @i INT SET @i=1 WHILE @i<6 BEGIN SET @r=@r+'r'+CAST(@i AS VARCHAR(2))++'+1'+',' SET @i=@i+1 END SELECT @result=SUBSTRING(@r,1,LEN(@r)-1)--构造好了,@result=r1+1,r2+1,r3+1,r4+1,r5+1 PRINT @result INSERT INTO tab SELECT 期号,@result,类别 FROM tab WHERE r2<9 AND r3 <9 --这儿提示字段个数不匹配
INSERT INTO tab EXEC('SELECT 期号,'+@result+',类别 FROM tab WHERE r2<9 AND r3 <9')
[解决办法]
set @result ='SELECT 期号,'+@result+',类别 FROM tab WHERE r2<9 AND r3 <9' --这儿提示字段个数不匹配exec(@result)
[解决办法]
+1