存儲(chǔ)過(guò)程與視圖適用

DECLARE @DBName VARCHAR(200); DECLARE @ProcName VARCHAR(200)='w_sp_Sms_ExpeOrKeepEmpl'; DECLARE C_TABLES CURSOR FAST_FORWARD FORSELECT NAMEFROM MASTER..SYSDATABASESWHERE NAME LIKE 'AB%' OR NAME LIKE 'DF%' OR NAME='ManChengZhuanYeSL'ORDER BY NAME;OPEN C_TABLES;FETCH NEXT FROM C_TABLES INTO @DBName;WHILE @@FETCH_STATUS=0BEGIN-- 不能使用OBJECTPROPERTY方法和sysobjects表判斷存儲(chǔ)過(guò)程，必須先USE數(shù)據(jù)庫(kù)后才能查到IF OBJECT_ID('[' + @DBName + '].[dbo].[' + @ProcName + ']') IS NOT NULLBEGINPRINT @DBName; -- 打印成功操作數(shù)據(jù)庫(kù)，錯(cuò)誤可在信息查看（普通錯(cuò)誤直接提示，致命錯(cuò)誤直接終止）EXEC('USE [' + @DBName + '];EXEC(''-- 單引號(hào)1個(gè)換4個(gè)-- 可替換成視圖 ALTER PROC [dbo].[' + @ProcName + ']@BillGuid char(36) AS SELECT a.BillGuid,d.EmplPhone,(''''單號(hào)''''+c.BillNo+'''' / ''''+c.MemberName+''''[''''+c.Customer+'''']'''') as FirstData,(b.ItemName+''''[實(shí)收''''+CONVERT(varchar(100),b.ItemAmount)+''''元]'''') as Keyword1Data,CONVERT(INT, b.ItemCount) as Keyword2Data,c.CheckoutTime as Keyword3Data,(a.EmplNo+''''_''''+a.EmplName+''''(''''+e.LevelName+'''')'''' + Char(13) + Char(10) +(case when a.AssignMark=1 then ''''指定'''' else ''''輪牌'''' end) +''''：業(yè)績(jī) ''''+CONVERT(varchar(100),a.Perfor)+'''' / 提成 ''''+CONVERT(varchar(100),(a.BasicRoya+a.AssignRoya))+'''' / 創(chuàng)單 ''''+CONVERT(varchar(100),a.InventRoya)+ Char(13) + Char(10)+ ''''付款方式：'''' + ISNULL(STUFF((SELECT '''' / '''' + PaymentName + '''' '''' + CONVERT(VARCHAR, PayMoney) FROM View_MergeExpePayment WHERE BillGuid=@BillGuid FOR XML PATH('''''''')), 1, 3, ''''''''), ''''無(wú)'''')) as Keyword4Data,'''''''' as RemarkData FROM View_MergeExpeEmpl as a INNER JOIN View_MergeExpeItem as b on a.ExpeItemGuid=b.ExpeItemGuid AND b.CheckoutMark=3 AND b.DeleteMark=0INNER JOIN View_MergeExpeBill as c on a.BillGuid=c.BillGuid AND c.CheckoutMark=3 AND c.DeleteMark=0INNER JOIN SalonEmplBasic as d on a.EmplGuid=d.EmplGuidINNER JOIN SalonEmplLevel e on a.LevelGuid=e.LevelGuid AND a.StoresGuid=e.StoresGuid WHERE a.BillGuid=@BillGuid AND a.CheckoutMark=3 AND a.DeleteMark=0 UNION ALL SELECT a.BillGuid,d.EmplPhone,(''''單號(hào)''''+b.BillNo+'''' / ''''+e.MemberName+''''[''''+b.VipNo+'''']'''') as FirstData,(case when a.KeepType=1 then ''''開(kāi)卡'''' else ''''充值'''' end)as Keyword1Data,1 as Keyword2Data,b.KeepTime as Keyword3Data,(c.EmplNo+''''_''''+c.EmplName+Char(13) + Char(10)+''''業(yè)績(jī) ''''+CONVERT(varchar(100),a.PerforMoney)+'''' / 提成 ''''+CONVERT(varchar(100),a.RoyaMoney)+Char(13) + Char(10)+''''付款方式：'''' + ISNULL(STUFF((SELECT '''' / '''' + PaymentName + '''':'''' + CONVERT(VARCHAR, PayMoney) FROM SalonKeepPayment WHERE BillGuid=@BillGuid FOR XML PATH('''''''')), 1, 3, ''''''''), ''''無(wú)'''')) as Keyword4Data,'''''''' as RemarkData FROM SalonKeepEmpl as aINNER JOIN SalonKeepBill as b on a.BillGuid=b.BillGuidINNER JOIN SalonEmpl as c on a.EmplGuid=c.EmplGuidINNER JOIN SalonEmplBasic as d on c.EmplGuid=d.EmplGuidINNER JOIN SalonMember as e on b.MemberGuid=e.MemberGuid WHERE a.BillGuid=@BillGuid;'')');END;FETCH NEXT FROM C_TABLES INTO @DBName;END CLOSE C_TABLES; DEALLOCATE C_TABLES;

注：使用SP_MSFOREACHDB受2000個(gè)字符限制，不得不改用游標(biāo)

轉(zhuǎn)載于:https://www.cnblogs.com/hcbin/p/10788988.html

總結(jié)

以上是生活随笔為你收集整理的[SQL]批量修改存储过程视图的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。