如何在存储过程中执行set命令
答案:2 悬赏:60 手机版
解决时间 2021-02-06 08:16
- 提问者网友：美人性情
- 2021-02-05 20:02
如何在存储过程中执行set命令
最佳答案
- 五星知识达人网友：行雁书
- 2021-02-05 20:24
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);
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
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);
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
全部回答
- 1楼网友：痴妹与他
- 2021-02-05 21:56
--1.ouput参数返回值create procedure [dbo].[nb_order_insert](@o_buyerid int ,@o_id bigint output)asbeginset nocount on;begininsert into [order](o_buyerid )values (@o_buyerid )set @o_id = @@identityendend存储过程中获得方法：
declare @o_buyerid intdeclare @o_id bigintexec [nb_order_insert] @o_buyerid,@o_id output
--2.return过程返回值create procedure [dbo].[nb_order_insert](@o_buyerid int ,@o_id bigint output)asbeginset nocount on;if(exists(select * from [shop] where [s_id] = @o_shopid))begininsert into [order](o_buyerid )values (@o_buyerid )set @o_id = @@identityreturn 1 — 插入成功返回1endelsereturn 0 — 插入失败返回0end存储过程中获得方法：
declare @o_buyerid intdeclare @o_id bigintdeclare @result bitexec @result = [nb_order_insert] @o_buyerid ,o_id bigint
以下是程序获得存储过程值
1、获取output输出参数值
//存储过程//create procedure mysql// @a int,// @b int,// @c int output//as// set @c = @a + @b//gosqlconnection conn = new sqlconnection(configurationmanager.connectionstrings["localsqlserver"].tostring());conn.open();sqlcommand mycommand = new sqlcommand("mysql", conn);mycommand.commandtype = commandtype.storedprocedure;mycommand.parameters.add(new sqlparameter("@a", sqldbtype.int));mycommand.parameters["@a"].value = 20;mycommand.parameters.add(new sqlparameter("@b", sqldbtype.int));mycommand.parameters["@b"].value = 20;mycommand.parameters.add(new sqlparameter("@c", sqldbtype.int));mycommand.parameters["@c"].direction = parameterdirection.output;mycommand.executenonquery();response.write(mycommand.parameters["@c"].value.tostring());2、获取return返回值
//存储过程//create procedure mysql// @a int,// @b int//as// return @a + @b//gosqlconnection conn = new sqlconnection(configurationmanager.connectionstrings["localsqlserver"].tostring());conn.open();sqlcommand mycommand = new sqlcommand("mysql", conn);mycommand.commandtype = commandtype.storedprocedure;mycommand.parameters.add(new sqlparameter("@a", sqldbtype.int));mycommand.parameters["@a"].value = 10;mycommand.parameters.add(new sqlparameter("@b", sqldbtype.int));mycommand.parameters["@b"].value = 20;mycommand.parameters.add(new sqlparameter("@return", sqldbtype.int));mycommand.parameters["@return"].direction = parameterdirection.returnvalue;mycommand.executenonquery();response.write(mycommand.parameters["@return"].value.tostring());
我要举报
如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！
点此我要举报以上问答信息
大家都在看
推荐资讯