SQL Server創建存儲過程——動態SQL

来源:http://www.cnblogs.com/Brambling/archive/2017/04/18/6725859.html
-Advertisement-
Play Games

簡介： 存儲過程(stored procedure)是一組為了完成特定功能的SQL語句集合，經編譯後存儲在伺服器端的資料庫中，利用存儲過程可以加速SQL語句的執行。 自定義存儲過程，由用戶創建並能完成某一特定功能的存儲過程，存儲過程既可以有參數又有返回值，但是它與函數不同，存儲過程的返回值只是指明執 ...


簡介：

存儲過程(stored procedure)是一組為了完成特定功能的SQL語句集合，經編譯後存儲在伺服器端的資料庫中，利用存儲過程可以加速SQL語句的執行。

自定義存儲過程，由用戶創建並能完成某一特定功能的存儲過程，存儲過程既可以有參數又有返回值，但是它與函數不同，存儲過程的返回值只是指明執行是否成功，

存儲過程並不能像函數那樣被直接調用，只能利用 execute 來執行存儲過程。

優點：

1、提高應用程式的通用性和可移植性：存儲過程創建後，可以在程式中被多次調用，而不必重新編寫該存儲過程的SQL語句。並且資料庫專業人員可以隨時對存儲過程進行

修改，且對程式源代碼沒有影響，這樣就極大的提高了程式的可移植性。

2、可以提高SQL的速度，存儲過程是編譯過的，如果某一個操作包含大量的SQL代碼或分別被執行多次，那麼使用存儲過程比直接使用單條SQL語句執行速度快的多。

3、減輕伺服器的負擔：當用戶的操作是針對資料庫對象的操作時，如果使用單條調用的方式，那麼網路上還必須傳輸大量的SQL語句，如果使用存儲過程，

則直接發送過程的調用命令即可，降低了網路的負擔。

 

語法：

 1 CREATE PROC [ EDURE ] procedure_name [ ; number ]
 2     [ { @parameter data_type }
 3         [ VARYING ] [ = default ] [ OUTPUT ]
 4     ] [ ,...n ]
 5 [ WITH
 6     { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
 7 [ FOR REPLICATION ]
 8 AS 
 9 [ begin ]
10     T-SQL 語句
11 [ end ]

無參數存儲過程：

 1 --創建名為 GetStuCou 的無參數存儲過程
 2 create procedure GetStuCou
 3 as
 4 begin
 5     select * 
 6     from Student s
 7     left join Course c on s.C_S_Id=c.C_Id
 8 end
 9 
10 --執行名為 GetStuCou 的無參數存儲過程
11 execute GetStuCou

有返回值的存儲過程：

 1 --創建名為 GetStuCou_Re 的有返回值的存儲過程
 2 create procedure GetStuCou_Re
 3 as
 4 begin
 5     insert into Course(C_Name) values('HTML5')
 6     return SCOPE_IDENTITY();        -- 返回為當前表插入數據最後生成的標識值。
 7 end
 8 
 9 --執行名為 GetStuCou 的有返回值的存儲過程
10 execute GetStuCou_Re

有輸入參數的存儲過程：

 1 --創建名為 GetStuCou_In 的有輸入參數的存儲過程
 2 create procedure GetStuCou_In
 3 @StuNo    nvarchar(64)='001'        --設置預設值
 4 as
 5 begin
 6     select * from Student where S_StuNo=@StuNo
 7 end
 8 
 9 --執行名為 GetStuCou_In 的有輸入參數的存儲過程（不傳參數，即使用預設值）
10 execute GetStuCou_In
11 
12 --執行名為 GetStuCou_In 的有輸入參數的存儲過程（傳入參數）
13 execute GetStuCou_In '005'

有輸入、輸出參數的存儲過程：

 1 --創建名為 GetStuCou_Out 的有輸入參數和輸出參數的存儲過程
 2 create procedure GetStuCou_Out
 3 @StuNo    nvarchar(64),
 4 @Height nvarchar(32) output
 5 as
 6 begin
 7     if(@StuNo is not null and @StuNo <> '')
 8     begin
 9         select @Height=S_Height 
10         from Student 
11         where S_StuNo=@StuNo
12     end
13     else
14     begin
15         set @Height='185'
16     end
17 end
18 
19 --執行名為 GetStuCou_Out 的有輸入參數和輸出參數的存儲過程
20 execute GetStuCou_Out '005',null

有輸入、輸出參數和結果集的存儲過程：

 1 --創建名為 GetStuCou_DS 的有輸入參數、輸出參數和結果集的存儲過程
 2 create procedure GetStuCou_DS
 3 @StuNo    nvarchar(64),
 4 @Height nvarchar(32) output
 5 as
 6 begin
 7     if(@StuNo is not null and @StuNo <> '')
 8     begin
 9         select @Height=S_Height 
10         from Student 
11         where S_StuNo=@StuNo
12     end
13     else
14     begin
15         set @Height='185'
16     end
17 
18     select s.S_Id,s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,s.S_BirthDate,c.C_Id,c.C_Name
19     from Student s
20     left join Course c on s.C_S_Id=c.C_Id
21     where S_StuNo=@StuNo
22 end
23 
24 --執行名為 GetStuCou_DS 的有輸入參數、輸出參數和結果集的存儲過程
25 execute GetStuCou_DS '005',null

返回多個結果集的存儲過程：

 1 --創建名為 GetStuCou_DSS 的返回多個結果集的存儲過程
 2 create procedure GetStuCou_DSS
 3 @StuNo    nvarchar(64),
 4 @Height nvarchar(32)
 5 as
 6 begin
 7     if(@StuNo is not null and @StuNo <> '')
 8     begin
 9         select *
10         from Student 
11         where S_StuNo=@StuNo
12     end
13 
14     if(@Height is not null and @Height <> '')
15     begin
16         select * 
17         from Student
18         where S_Height=@Height
19     end
20 end
21 
22 --執行名為 GetStuCou_DSS 的返回多個結果集的存儲過程
23 execute GetStuCou_DSS '005','185'

存儲過程裡面不僅可以進行查詢，還可以進行各種增刪改操作。其實存儲就是由很多 T-SQL 語句組成的代碼塊。

 

存儲過程中創建變數、賦值變數、創建表變數和臨時表：

 1 --創建名為 GetStuCou_Ext 的返回多個結果集的存儲過程
 2 create procedure GetStuCou_Ext
 3 @StuNo    nvarchar(64),
 4 @Height nvarchar(32)
 5 as
 6 begin
 7     declare @Var nvarchar(10)    --定義變數
 8 
 9     set @Var='123'        --賦值變數
10 
11     --定義表變數
12     declare @StuTab table 
13     (
14         ID     int not null primary key,
15         StuNo    nvarchar(50) unique,
16         Name varchar(50),
17         Sex varchar(10),
18         Height varchar(10)
19     )
20     --表變數只能在定義的時候添加約束
21 
22     --定義臨時表
23     create table #Tab
24     (
25         ID     int not null primary key,
26         StuNo    nvarchar(50),
27         Name varchar(50),
28         Sex varchar(10),
29         Height varchar(10)
30     )
31 
32     alter table #Tab add constraint S_UNIQUE unique(StuNo)
33 
34     --臨時表可以在之後添加約束
35     
36     if(@StuNo is not null and @StuNo <> '')
37     begin
38         insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把數據插入表變數
39         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
40         from Student 
41         where S_StuNo=@StuNo
42 
43         insert into #Tab(ID,StuNo,Name,Sex,Height)    --把數據插入臨時表
44         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
45         from Student 
46         where S_StuNo=@StuNo
47     end
48 
49     if(@Height is not null and @Height <> '')
50     begin
51         insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把數據插入表變數
52         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
53         from Student
54         where S_Height=@Height
55 
56         insert into #Tab(ID,StuNo,Name,Sex,Height)    --把數據插入臨時表
57         select S_Id,S_StuNo,S_Name,S_Sex,S_Height
58         from Student
59         where S_Height=@Height
60     end
61 
62     SELECT * FROM @StuTab
63     select * from #Tab
64 end
65 
66 --執行名為 GetStuCou_DSS 的返回多個結果集的存儲過程
67 execute GetStuCou_Ext '005','185'

存儲過程動態執行 SQL 語句：

以上可以看出我們傳入的參數（學號）是單個的，那麼如果一次性傳入多個學號呢（使用逗號隔開，即 '005,006,007' ），這就需要用到動態拼接並執行 sql 語句。

 1 create proc GetStus
 2 @StuNo nvarchar(500)
 3 as
 4 begin
 5     declare @Sql nvarchar(3000)
 6 
 7     if(@StuNo is not null and @StuNo <> '')
 8     begin
 9         set @Sql=' select * from Student where S_StuNo in ('+@StuNo+') '
10     end
11 
12     exec (@Sql)    --執行動態 sql 
13 end
14 
15 exec GetStus '003,005,009'        --執行存儲過程 GetStus

現在還有一個問題，我想要執行動態 sql 的時候並返回變數值，比如我現在需要執行動態 sql 的時候返回課程 ID ,然後根據課程 ID 查詢課程。

使用系統存儲過程 sp_executesql 動態 sql 給變數賦值。

修改存儲過程之後：

 1 ALTER proc [dbo].[GetStus]
 2 @StuNo nvarchar(500)
 3 as
 4 begin
 5     declare @Sql nvarchar(3000)
 6     declare @C_Id int
 7     declare @Cou int
 8 
 9     if(@StuNo is not null and @StuNo <> '')
10     begin
11         set @Sql=' select @CId=C_S_Id,@cou=count(1) from Student where S_StuNo in ('+@StuNo+') group by C_S_Id '
12     end
13 
14     exec sp_executesql @Sql,N'@CId int output,@cou int output',@CId = @C_Id output,@cou = @Cou output;
15 
16     select @C_Id,@Cou    --查看返回變數的值
17 
18     select * from Course where C_Id=@C_Id 
19 end
20 
21 exec GetStus '005'        --執行存儲過程 GetStus

PS：sp_executesql 要求動態 Sql 和動態 Sql 參數列表必須是 NVARCHAR 類型。

動態Sql的參數列表與外部提供值的參數列表順序必需一致，例如：N'@CId int output,@cou int output',@CId = @C_Id output,@cou = @Cou output;，@CId 對應 @C_Id，@cou 對應 @Cou。

動態SQl的參數列表與外部提供參數的參數列表參數名可以同名，如果不一樣，需顯示註明，例如：N'@CId int output,@cou int output',@CId = @C_Id output,@cou = @Cou output;，即 @CId = @C_Id 和 @cou = @Cou 。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 一個新的博客園Android客戶端
    關於博客園Android客戶端 作為一名經常逛博客園的C 開發者，發現博客園沒有官方的Android APP，其他第三方的APP要麼年代久遠，要麼功能不完善，所以決定自己動手寫一個博客園的第三方Android APP試一試，同時也給需要Android APP的朋友提供一個選擇。APP內容由博客園官方 ...
  • iOS 技術疑問點
    待續 ...
  • Android開發學習——高德地圖的實現
    1.首先做好下邊的準備： 1.1 http://lbs.amap.com/ 註冊賬號 1.2 下載 定位sdk 和 地圖sdk 下載後是這樣的 1.3 對下載的進行解壓 將他們加入 中，對每個jar,右鍵-Add As Library. 此時 build.gradle生成 1.4 申請API Key ...
  • React Native組件的結構和生命周期
    React Native組件的結構和生命周期 一、組件的結構 1、導入引用 可以理解為C++編程中的頭文件。 導入引用包括導入react native定義的組件、API，以及自定義的組件。 1.1 導入組件 1.2 導入API 1.3 導入自定義組件 2、組件的聲明 組件的生命分為組件的定義和樣式。 ...
  • Git命令行和Xcode結合使用(我來告訴你這行代碼誰寫的)
    現在一直使用Git來管理代碼，對於有強迫症的我來說，依舊選擇了命令行，下麵這段話可以更好的解釋我為什麼喜歡使用終端敲命令。 There are a lot of different ways to use Git. There are the original command line tools, ...
  • Xamarin.Forms+Prism（2）—— 基本使用 NavigationService
    本文主要對Prism框架下的導航服務NavigationService進行一次介紹和使用。 1、打開VS，可以看到左側的已安裝模版裡面有： 2、創建完成後，從PCL項目中，看到App.xaml.cs中的代碼知道，程式現在的邏輯是打開APP->NavigationPage->MainPage（傳遞ti ...
  • iOS ----------如何修改mac的host文件
    第一步：前往文件夾 或者 按快捷鍵組合 Shift+Command+G 三個組合按鍵。 第二步：找到Hosts 文件 複製到桌面 修改 然後 保存 。 第三步：把修改過的文件 替換原來文件 就ok了。 ...
  • 從一次記憶體峰值說起
    最近把一個游戲內嵌到app里，選用了微信開源的Mars，結果遇到了記憶體峰值。解決的方法很容易，加上@autoreleasepool就可以了。但是做實驗的時候又有了好多疑惑，不停地往深處挖，最終瞭解了autoreleasepool的實現，Tagged Pointer，和NSString記憶體管理的特殊性 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類