SQL存储过程动态创建视图

浏览： ℃

字体：大中小

发布时间：2013-12-12 14:50:52

来源：

CREATE PROCEDURE CREAR_PF_VIEW
AS BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'CREATE VIEW dbo.V_PF
AS select * from (select distinct XMBH as XMBH From YDS_SHPB ) X1 '
DECLARE @uid VARCHAR(MAX)
DECLARE @sql_ls VARCHAR(MAX)
DECLARE cur CURSOR --定义一个游标
READ_ONLY
FOR SELECT SPBLX FROM YDS_SHBPDY--为所获得的数据集指定游标
open cur --打开游标
FETCH NEXT FROM cur INTO @uid --把提取操作的列数据放到局部变量中
WHILE(@@fetch_status=0) --返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
BEGIN
SET @sql_ls = ' left join (select B.XMBH as '+ @uid+'_BH,A.YJMC as ' + @uid + '_YJ,A.JBRMC as ' +@uid +'_QZ,
A.JBRQMC as '+@uid+'_RQ,B.JBRYJ as '+ @uid +'_BT from YDS_SHBPDY as A,YDS_SHPB as B where A.SPBLX = B.SPBLX and A.SPBLX ='''+@uid+'''
) '+@uid+' on X1.XMBH = '+@uid+'.'+@uid +'_BH'
SET @sql = @sql + @sql_ls
--提前下一位信息
FETCH NEXT FROM cur INTO @uid
END
CLOSE cur --关闭游标
DEALLOCATE cur--删除游标
--PRINT @sql
IF exists --判断视图是否存在若存在则删除后创建
(SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[V_PF]') and OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [dbo].[V_PF]
EXEC(@sql)
END
GO

>更多相关文章