存储过程中用exec执行sql语句问题
1-------创建的一个存贮过程
CREATE procedure up_update_all_kit
@str_table varchar(50),
@int_kit varchar(20),
@str_column varchar(20),
@str_where_key varchar(20)
as
begin
SET XACT_ABORT ON
begin transaction
exec('update ' + @str_table + ' set ' + @int_kit + '= ' + @int_kit + ' + 1 '+' where '+ @str_column + ' = '+ @str_where_key )
if(@@error=0 and @@rowcount=1)
commit transaction
else
rollback transaction
end
GO
2---------执行一个存贮过程
INSERT INTO @IndexTable(NId) EXEC('SELECT ModuleCode FROM SModule')这句话在存储过程中出现以下错误:
无法在向表插入变量时将 EXECUTE 用作源。
方法一
create table #IndexTable(NID int) --创建一个临时表
--插入数据
INSERT INTO #IndexTable(NId) EXEC('SELECT ModuleCode FROM SModule')
方法二
declare @sql varchar(8000)
set @sql='INSERT INTO '''+@IndexTable+''' (NId) SELECT ModuleCode FROM SModule'
exec (@sql)
实例二:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure up_Flower_Order --up_Flower_Order '2742'
@sOrderID varchar(255)
As
CREATE TABLE #BZ_Flower_order
(
Serial_id numeric,
Order_id varchar (50),
Order_flower_num int,
Flower_id varchar (50),
Flower_name varchar(50),
P_user_id varchar(50),
Accept_flower_time varchar (50),
Accept_flower_hour varchar (50),
Accept_man_name varchar (50),
Accept_man_tel varchar (14),
Accept_man_mobile varchar (14),
Accept_man_addr text,
Order_total_money real,
Send_man_status varchar (5),
LeaveMessage nvarchar (200),
Remark text,
Busi_id varchar(50),
Order_CommuName varchar(50),
Order_CoomuMobile varchar(50),
Order_CommuTel varchar(50),
Order_CommuEmail varchar(100),
Order_CommuAddr ntext,
certificate_style varchar(50),
Flower_shop_name varchar(100),
Flower_shop_id numeric,
OrderNum varchar(100),
order_status varchar(50)
)
declare @sSQL nvarchar(2000)
set @sSQL =N'Select
BZ_flower_order.Serial_id ,
BZ_flower_order.Order_id,
BZ_flower_order.Order_flower_num,
BZ_flower_order.Flower_id ,
BZ_flower_order.Flower_name,
BZ_flower_order.P_user_id,
BZ_flower_order.Accept_flower_time,
BZ_flower_order.Accept_flower_hour,
BZ_flower_order.Accept_man_name ,
BZ_flower_order.Accept_man_tel ,
BZ_flower_order.Accept_man_mobile ,
BZ_flower_order.Accept_man_addr ,
BZ_flower_order.Order_total_money ,
BZ_flower_order.Send_man_status ,
BZ_flower_order.LeaveMessage ,
BZ_flower_order.Remark ,
BZ_flower_order.Busi_id,
BZ_Data_Order.Order_CommuName,
BZ_Data_Order.Order_CoomuMobile,
BZ_Data_Order.Order_CommuTel,
BZ_Data_Order.Order_CommuEmail,
BZ_Data_Order.Order_CommuAddr,
BZ_Data_Order.certificate_style,
'''',0,BZ_Data_Order.OrderNum,
BZ_Data_Order.Order_status from BZ_Flower_order,BZ_Data_Order WHERE BZ_Flower_order.Order_id=BZ_Data_Order.Order_ID and BZ_flower_order.Order_id ='+@sOrderID
insert into #BZ_Flower_order
EXECUTE sp_executesql @sSQL
--????
Update #BZ_Flower_order Set #BZ_Flower_order.Flower_shop_name=BZ_flower_shop.Flower_shop_name,#BZ_Flower_order.Flower_shop_id=BZ_flower_shop.Flower_shop_id
From #BZ_Flower_order,BZ_flower_shop
where #BZ_Flower_order.Busi_id =BZ_flower_shop.Busi_id
--????
update #BZ_Flower_order set #BZ_Flower_order.certificate_style=Sys_Dic_Normal.sName
from Sys_Dic_Normal,#BZ_Flower_order
where Sys_Dic_Normal.sCode=#BZ_Flower_order.certificate_style
--????
update #BZ_Flower_order set #BZ_Flower_order.order_status=Sys_Dic_Normal.sName
from Sys_Dic_Normal,#BZ_Flower_order
where Sys_Dic_Normal.sCode=#BZ_Flower_order.order_status
select * from #BZ_Flower_order
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//执行存贮过程
string sqlread="exec up_Flower_Order '"+sorderid+"'";
System.Data.DataTable dt = Framework.ComLib.DataBase.Query(sqlread).Tables[0];