mssql存儲過程調用存儲過程
存儲過程中執行動態Sql語句
MSSQL為我們提供了兩種動態執行SQL語句的命令，分別是EXEC和sp_executesql;通常,sp_executesql則更具有優勢，它提供了輸入輸出介面，而EXEC沒有。還有一個最大的好處就是利用sp_executesql，能夠重用執行計劃，這就大大提供了執行性能，還可以編寫更安全的代碼。EXEC在某些情況下會更靈活。除非您有令人信服的理由使用EXEC，否側盡量使用sp_executesql.
1.EXEC的使用
EXEC命令有兩種用法，一種是執行一個存儲過程，另一種是執行一個動態的批處理。以下所講的都是第二種用法。
下面先使用EXEC演示一個例子,代碼1
DECLARE @TableName VARCHAR(50),@Sql NVARCHAR (MAX),@OrderID INT;
SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql =
'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+
CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
EXEC(@sql);
註：這里的EXEC括弧中只允許包含一個字元串變數，但是可以串聯多個變數，如果我們這樣寫EXEC：
EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+
QUOTENAME(@TableName) +' ORDER BY ORDERID DESC');
SQL編譯器就會報錯，編譯不通過，而如果我們這樣：
EXEC(@sql+@sql2+@sql3);
編譯器就會通過；
所以最佳的做法是把代碼構造到一個變數中，然後再把該變數作為EXEC命令的輸入參數，這樣就不會受限制了。
EXEC的缺點是不提供介面，這里的介面是指，它不能執行一個包含一個帶變數符的批處理，如下
DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
'WHERE OrderID = @OrderID ORDER BY ORDERID DESC'
EXEC(@sql);
關鍵就在SET @sql這一句話中，如果我們運行這個批處理，編譯器就會產生一下錯誤
Msg 137, Level 15, State 2, Line 1
必須聲明標量變數 "@OrderID"。
使用EXEC時，如果您想訪問變數，必須把變數內容串聯到動態構建的代碼字元串中，如：
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + 'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
串聯變數的內容也存在性能方面的弊端。SQL Server為每一個的查詢字元串創建新的執行計劃，即使查詢模式相同也是這樣。為演示這一點，先清空緩存中的執行計劃
DBCC FREEPROCCACHE (這個不是本文所涉及的內容，您可以查看MS的MSDN)
將代碼1運行3次，分別對@OrderID 賦予下面3個值，10251，10252，10253。然後使用下面的代碼查詢
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cach%' AND sql NOT LIKE '%sys.%'
點擊F5運行，我們可以看到，每執行一次都要產生一次的編譯，執行計劃沒有得到充分重用。
EXEC除了不支持動態批處理中的輸入參數外，他也不支持輸出參數。默認情況下，EXEC把查詢的輸出返回給調用者。例如下面代碼返回Orders表中所有的記錄數
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
EXEC(@sql);
然而，如果你要把輸出返回給調用批處理中的變數，事情就沒有那麼簡單了。為此，你必須使用INSERT EXEC語法把輸出插入到一個目標表中，然後從這表中獲取值後賦給該變數，就像這樣：
代碼
DECLARE @sql NVARCHAR(MAX),@RecordCount INT
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders';
CREATE TABLE #T(TID INT);
INSERT INTO #T EXEC(@sql);
SET @RecordCount = (SELECT TID FROM #T)
SELECT @RecordCount
DROP TABLE #T2
2.sp_executesql的使用
sp_executesql命令在SQL Server中引入的比EXEC命令晚一些，它主要為重用執行計劃提供更好的支持。
為了和EXEC作一個鮮明的對比,我們看看如果用代碼1的代碼，把EXEC換成sp_executesql,看看是否得到我們所期望的結果
代碼
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT ,@sql2 NVARCHAR(MAX);
SET @TableName = 'Orders ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
EXEC sp_executesql @sql
注意最後一行；事實證明可以運行；
sp_executesql提供介面
sp_executesql命令比EXEC命令更靈活，因為它提供一個介面，該介面及支持輸入參數也支持輸出參數。這功能使你可以創建帶參數的查詢字元串，這樣就可以比EXEC更好的重用執行計劃,sp_executesql的構成與存儲過程非常相似，不同之處在於你是動態構建代碼。它的構成包括：代碼快，參數聲明部分，參數賦值部分。說了這么多，還是看看它的語法：
EXEC sp_executesql
@stmt= <statement>,--類似存儲過程主體
@params = <params>, --類似存儲過程參數部分,聲明參數類型
<params assignment> --類似存儲過程調用，為參數賦值,參數值要和參數順序要一一對應，也可以通過為參數指明參數值的方式為其賦值
@stmt參數是輸入的動態批處理，它可以引入輸入參數或輸出參數，和存儲過程的主體語句一樣，只不過它是動態的，而存儲過程是靜態的，不過你也可以在存儲過程中使用sp_executesql;
@params參數與定義輸入/輸出參數的存儲過程頭類似，實際上和存儲過程頭的語法完全一樣；
@<params assignment> 與調用存儲過程的EXEC部分類似。
其實@stmt,@params可以省略，那麼exec sp_executesql的語法就可以簡寫成如下格式：
EXEC sp_executesql
<statement>,
<params>,
<params assignment>
為了說明sp_executesql對執行計劃的管理優於EXEC,我將使用前面討論EXEC時用到的代碼。
代碼
DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID INT;
SET @TableName = 'Orders ';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'
--注意當要對動態sql語句的表名實行參數化時，不可以如下表示：
--set @sql='select * from @TableName where OrderID=@OID ORDER BY Orderid desc',
--如果這樣會提示必須聲明標量變數@TableName,只可以如上面所寫的一樣，將表名@TableName作為變數名進行拼接
EXEC sp_executesql
@sql,
N'@OID int ',
@OID = @OrderID
下面我們看看exec sp_executesql的執行效率，在調用該代碼和檢查它生成的執行計劃前，先清空緩存中的執行計劃；
DBCC FREEPROCCACHE
將上面的動態代碼執行3次，每次執行都賦予@OrderID 不同的值，然後查詢sys.syscacheobjects表，並注意它的輸出，優化器只創建了一個備用計劃，而且該計劃被重用的3次
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' AND sql NOT LIKE '%sp_executesql%'
點擊F5運行。
sq_executesql的另一個與其介面有關的強大功能是，你可以使用輸出參數為調用批處理中的變數返回值。利用該功能可以避免用臨時表返回數據，從而得到更高效的代碼和更少的重新編譯。定義和使用輸出參數的語法與存儲過程類似。也就是說，你需要在聲明參數時指定OUTPUT子句。例如，下面的靜態代碼簡單的演示了如何從動態批處理中利用輸出參數@p把值返回到外部批處理中的變數@i.
DECLARE @sql AS NVARCHAR(12),@i AS INT;
SET @sql = N' SET @p = 10';
EXEC sp_executesql
@sql,
N'@p AS INT OUTPUT',
@p = @i OUTPUT
SELECT @i --該代碼返回輸出10
以字母 N 為前綴標識 Unicode 字元串常量
總結以下幾點：
一.使用exce sp_executesql效率比exec要高，同一類型的語句，只需編譯一次即可，而exec執行幾次就需要編譯幾次。
二.構造動態sql的where子句，也就是條件子句時，exec無法使用變數來進行站位，需要將變數轉換成字元串，然後和動態sql進行拼接，這就可能引起Sql注入問題，如下：
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +
' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
而若使用exec sp_executesql則可以使用變數來進行站位，以後再給這個參數傳值的放式構造動態sql，就避免的Sql注入的問題，如下：
SET @sql = 'SELECT * FROM '+@TableName + ' WHERE OrderID = @OID ORDER BY ORDERID DESC'
三.無論是Exec還是Exec sp_executesql，如果想要將表名和列名進行動態參數化，不可以使用表名參數和列名參數來進行站位，而且表名參數和列名參數需要使用存儲過程的參數.對 於exec sp_executesql來說，不可以將表名參數和列名參數在指定為在exec sp_executesql參數聲明部分聲明的參數，如：
代碼
create PROCEDURE GetData
@tbName nvarchar(10),
@colName nvarchar(10),
@Name nvarchar(10)
AS
BEGIN
declare @sql nvarchar(50);
set @sql='select '+ @colName+' from ' +@tbName+ ' where name=@whereName';
--注意此句不可以寫成如下:
-- set @sql='select @colName from @tbName where name=@whereName';
exec sp_executesql
@sql,
N'@whereName nvarchar(10)',
@Name
END
也就是說exec sp_executesql語句的參數聲明部分只能聲明動態sql的where子句的參數。
B. MSSQL資料庫中定時存儲的存儲過程
您好！要實現你要的功能；操作如下：
一、可以先寫好存儲過程的內容；放在目標資料庫中
二、在MSSQL的SQL server代理——〉作業——〉新建一個作業 在[常規]選項卡中 設置 名稱AUTOZY——〉在[步驟]選項卡中 點擊[新建作業]
三、在彈出的作業步驟窗口中；填寫步聚名稱（自己取一個）－－運行身份選擇SA或者WINDOWS運行都可以－－－在資料庫處選擇 目標資料庫（這步很關鍵）－－－
在命令 處 寫 exec 存儲過程的名稱（這個不要寫錯）－－－點擊[確定] 返回上級窗口
四、選擇[計劃]選項卡 －－在計劃窗口中可以具體設置怎麼樣定時運行的方案
五、設置好後 [確定]返回上級窗口－－－再點擊[確定]保存了這個作業
如有不明之處；歡迎CALL 我
C. 關於MSSQL存儲過程
使用參數方式調用存儲過程就行了
<%
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = GetConn(); '資料庫連接
Command1.CommandText = "dbo.xp_dgdb_Service"; '存儲過程名
Command1.CommandType = 4;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Parameters.Append(Command1.CreateParameter("@RETURN_VALUE", 3, 4)); '以下添加參數
Command1.Parameters.Append(Command1.CreateParameter("@P_orgAddr", 200, 1,21,Command1__P_orgAddr));
Command1.Parameters.Append(Command1.CreateParameter("@p_destAddr", 200, 1,21,Command1__p_destAddr));
Command1.Parameters.Append(Command1.CreateParameter("@p_servicecode", 200, 1,20,Command1__p_servicecode));
Command1.Parameters.Append(Command1.CreateParameter("@p_msg", 200, 1,200,Command1__p_msg));
Command1.Parameters.Append(Command1.CreateParameter("@LinkId", 200, 1,20,Command1__LinkId));
Command1.Parameters.Append(Command1.CreateParameter("@p_ret", 3, 2));
Command1.Execute(); '執行存儲過程
%>
具體的可以在網路網頁找一下,很多的
D. mssql 新建查詢中 如何調用存儲過程
exec procere_name 參數
例如：
創建：
create procere p_1
@sno int
as
select * from sc where sno=@sno
調用：
exec p_1 1
查詢 s c表中學號為 1 的學生的所有記錄
E. PHP怎樣調用MSSQL的存儲過程
//執行存儲過程 for($i=0;$i<$ses_basket_items;$i++) { $query="exec add_ddxx @p_account=\"$session_account\", @p_name=\"$name[$i]\", @p_num=\"$num[$i]\", @p_marketPRice=\"$marketprice[$i]\", @p_memberprice=\"$memberprice[$i]\", @p_priceoftax=\"$notaxprice[$i]\", @p_priceoflast=\"$lastprice[$i]\", @p_sumprice=\"$price[$i]\", @p_buy_date=\"$date\", @p_flag=\"$p_flag\""; $result=mssql_query($query,$connection) or die("存儲過程執行錯誤，無法執行該SQL：$query"); }
F. PHP怎樣調用MSSQL的存儲過程
下面的例子代碼， 僅僅用於演示 in out ， 沒有任何資料庫檢索的處理。
註：Oracle 的 IN OUT 寫在變數名後面。SQL> CREATE OR REPLACE PROCEDURE HelloWorld2 ( 2 p_user_name IN VARCHAR2, 3 p_out_val OUT VARCHAR2, 4 p_inout_val IN OUT VARCHAR2 5 ) AS 6 BEGIN 7 dbms_output.put_line('Hello ' || p_user_name || p_inout_val || '!'); 8 p_out_val := 'A'; 9 p_inout_val := 'B'; 10 END HelloWorld2; 11 /Procere created.SQL> DECLARE 2 p_outval VARCHAR2(10); 3 p_inoutval VARCHAR2(10) := '~Hi~'; 4 BEGIN 5 HelloWorld2('Edward', p_outval, p_inoutval); 6 7 dbms_output.put_line('p_outval=' || p_outval); 8 dbms_output.put_line('p_inoutval=' || p_inoutval); 9 END; 10 /Hello Edward~Hi~!p_outval=Ap_inoutval=BPL/SQL procere successfully completed.SQL>
G. mssql 存儲過程
MS SQL基礎教程：創建存儲過程
在MS SQL Server 2000 中，創建一個存儲過程有兩種方法：一種是使用Transaction-SQL 命令Create Procere， 另一種是使用圖形化管理工具Enterprise Manager。 用Transaction- SQL 創建存儲過程是一種較為快速的方法，但對於初學者，使用Enterprise Manager 更易理解，更為簡單。
當創建存儲過程時，需要確定存儲過程的三個組成部分；
所有的輸入參數以及傳給調用者的輸出參數。 被執行的針對資料庫的操作語句，包括調用其它存儲過程的語句； 返回給調用者的狀態值，以指明調用是成功還是失敗。 12.2.1 使用Enterprise Manager 創建存儲過程
按照下述步驟用Enterprise Manager 創建一個存儲過程：
啟動Enterprise Manager， 登錄到要使用的伺服器。 選擇要創建存儲過程的資料庫，在左窗格中單擊Stored Procere 文件夾，此時在右窗格中顯示該資料庫的所有存儲過程，如圖12-1 所示。 右擊Stored Procere 文件夾，在彈出菜單中選擇New Stored Procere, 此時打開創建存儲過程對話框，
輸入存儲過程正文。 單擊Check Syntax， 檢查語法是否正確。 單擊OK， 保存。 在右窗格中，右擊該存儲過程，在彈出菜單中選擇All task, 選擇
ManagePermissions， 設置許可權，
12.2.2 用CREATE PROCEDURE 命令創建存儲過程
通過運用Create Procere 命令能夠創建存儲過程，在創建存儲過程之前，應該考慮到以下幾個方面：
在一個批處理中，Create Procere 語句不能與其它SQL 語句合並在一起； 資料庫所有者具有默認的創建存儲過程的許可權，它可把該許可權傳遞給其它的用戶； 存儲過程作為資料庫對象其命名必須符合命名規則； 只能在當前資料庫中創建屬於當前資料庫的存儲過程。 用Create Procere 創建存儲過程的語法規則如下：
CREATE PROC [ EDURE ] procere_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
H. c#如何調用mssql2005的存儲過程
疑問：1、你的規則是m和n兩個數與1、10、20來對比，在1和10之間是10-m，在10和20之間是n-10，那在20和30之間是n-20嗎？還是30-n？我採用的是n-20;
2、你的數據表裡的三列數據是否是動態的呢，即1、10、20這三個數可能是其他數值？
我的答案：說明：未用你表裡的三列數據，只寫了個存儲過程，並在C#中調用，按你的規則輸出數值。
//按鈕點擊
privatevoidbutton1_Click(objectsender,EventArgse)
{
intm=4;//m和n這兩個數可以在窗體中輸入，這里為了清晰，直接賦值了
intn=18;
intt1=0;//t1、t2、t3為輸出的結果
intt2=0;
intt3=0;
GetNum(m,n,outt1,outt2,outt3);//調用存儲過程方法
textBox1.Text=t1+","+t2+","+t3;//將結果輸出到窗體的textBox中
}
//連接資料庫調用存儲過程
privatevoidGetNum(intm,intn,outintt1,outintt2,outintt3)
{
stringconnstring="server=127.0.0.1\SQLEXPRESS;database=JIN;uid=ruixin;pwd=ruixin";
SqlConnectionconn=newSqlConnection(connstring);
SqlCommandcmd=newSqlCommand("pro_number",conn);//pro_number為存儲過程名稱
conn.Open();
//設置為存儲過程
cmd.CommandType=CommandType.StoredProcere;
//設置參數
cmd.Parameters.Add("@m",SqlDbType.Int);
cmd.Parameters.Add("@n",SqlDbType.Int);
cmd.Parameters.Add("@t1",SqlDbType.Int,10);
cmd.Parameters.Add("@t2",SqlDbType.Int,10);
cmd.Parameters.Add("@t3",SqlDbType.Int,10);
//設置輸出參數類型
cmd.Parameters["@t1"].Direction=ParameterDirection.Output;
cmd.Parameters["@t2"].Direction=ParameterDirection.Output;
cmd.Parameters["@t3"].Direction=ParameterDirection.Output;
//參數賦值
cmd.Parameters["@m"].Value=m;
cmd.Parameters["@n"].Value=n;
//執行
cmd.ExecuteNonQuery();
//輸出參數賦值
t1=Convert.ToInt32(cmd.Parameters["@t1"].Value);
t2=Convert.ToInt32(cmd.Parameters["@t2"].Value);
t3=Convert.ToInt32(cmd.Parameters["@t3"].Value);
conn.Close();
cmd.Dispose();
}
//sqlserver2008資料庫中存儲過程
createprocere[dbo].[pro_number]
(@mint,
@nint,
@t1intoutput,
@t2intoutput,
@t3intoutput)
as
BEGIN
set@t1=0
set@t2=0
set@t3=0
if@mbetween1and10
begin
select@t1=10-@m
end
if@mbetween10and20
begin
select@t2=@m-10
end
if@mbetween20and30
begin
select@t3=@m-20
end
if@nbetween1and10
begin
select@t1=10-@n
end
if@nbetween10and20
begin
select@t2=@n-10
end
if@nbetween20and30
begin
select@t3=@n-20
end
end
I. oracel 通過dblink 調用mssql 存儲過程
ORACLE的DB_LINK不能調用MSSQL的存儲過程，但可以調用遠程ORACLE的存儲過程。
連接到mssql的存儲過程
方法我知道的有兩種
1、把MSSQL的存儲移植到ORACLE裡面，使用本地調用。
2、使用腳本命令調用，比如hostd:xxxxxxxsqlcmd.....
J. sqlalchemy 調用 mssql存儲過程如何獲取返回值
請參參考以下代碼：
from pyodbc import drivers, connect, Connection, Cursor
def output_cursor(cursor: Cursor):
...."""列印輸出當前結果集"""
....print('-' * 80)
....print(','.join(_[0] for _ in rst.description))
....for row in cursor:
........print(row)
spt = '''
declare @returns int,@count int,@lastDoTime datetime
exec @returns = test_proc_call @count output,@lastDoTime output
select @returns returns,@count count,@lastDoTime lastDoTime
'''
cur = db.execute(spt)
for rst in iter_cursor(cur):
....output_cursor(rst)
輸出：
--------------------------------------------------------------------------------
returns,count,lastDoTime
(18, 21, datetime.datetime(2020, 3, 4, 14, 43, 46, 923000))
存儲過程：test_proc_call
create procere test_proc_call
(@p1 int output
,@p2 datetime output
)
as
begin
--此過程有返回值,有output參數,有結果集
select @p1=max(id),@p2=max(LastDoTime)
from Tasks with(nolock)
select * from Tasks with(nolock)
return @@rowcount
end