如何在存储过程中执行set命令

如何在存储过程中执行set命令

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

--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());