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