动态生成SQL Server视图作业

01 www.2cto.com

DECLARE @SQL VARCHAR(8000), @SALETABEL VARCHAR(100)

02

SET @SQL='ALTER VIEW DBO.SCOST AS '

03

04

DECLARE SALELIST CURSOR SCROLL FOR SELECT 'SELECT * FROM SALECOST UNION ALL ' TABLENAME UNION ALL SELECT 'SELECT * FROM '+LTRIM(RTRIM(NAME))+' UNION ALL ' FROM SYSOBJECTS WHERE NAME LIKE 'SALECOST201%' AND XTYPE='U'

05

OPEN SALELIST

06

FETCH NEXT FROM SALELIST INTO @SALETABEL

07

WHILE @@FETCH_STATUS=0

08

BEGIN

09

SET @SQL=@SQL+@SALETABEL

10

FETCH NEXT FROM SALELIST INTO @SALETABEL

11

END

12

CLOSE SALELIST

13

DEALLOCATE SALELIST

14

15

SET @SQL=SUBSTRING(@SQL,1,LEN(@SQL)-10)

16

EXEC(@SQL)