因工作原因最近需要用到调用sqlserver存储过程,并且需要输入输出参数,因为单纯的参考网上的一些例子总有问题,最后自己解决后决定写一写解决的过程。并把自己写的3个方案都放上,记录一下自己的思路过程,经测试方案1为最优方案,性能比方案2,方案3,在10万数量级快6倍左右。
首先存储过程如下(其中带*的需要注意):
方案1:
CREATE PROCEDURE [dbo].[UPDATE_STOCK]
(
@Updatetime varchar(50),
@Orgguid varchar(50),
@ReturnMsg varchar(1000) output, -- 返回提示或错误信息
@ReturnValue varchar(50) output -- 返回提示或错误信息
)
AS
declare @SERVER varchar(500);
declare @UID varchar(200);
declare @PWD varchar(200);
BEGIN
SET NOCOUNT ON; --如果想只获取输出参数,而不被存储过程里面的exec影响则需加上该设置,关闭影响的行数才行
declare @NUM NUMERIC(10,0); --记录个数
declare @per_page NUMERIC(10,0) = 500; --每页记录个数,经测试每页500个时间最快,而且超过1000多个好像就会出问题
DECLARE @temp VARCHAR(500);
DECLARE @dosql NVARCHAR(1000);
SET @SERVER='192.168.100.2';
SET @UID='sa';
SET @PWD='testsql';
--定义表变量(******)
DECLARE @tb1 Table
(
SubCode VARCHAR(50),
ItemCode VARCHAR(50),
StockQuantity NUMERIC(18,4),
UnitPrice NUMERIC(18,6),
IS_EXISTS INT,
NUM INT
);
--远程查询库存信息(******)
SET @dosql = 'SELECT SubCode,ItemCode,StockQuantity,UnitPrice,IS_EXISTS,NUM FROM
(
SELECT a.SubCode,a.ItemCode,ISNULL(StockQuantity, 0) StockQuantity,ISNULL(UnitPrice, 0) UnitPrice,
case when ISNULL(sbs.GOODSGUID, 0)=0 then 0 else 1 end as IS_EXISTS,ROW_NUMBER() over (order by UnitPrice ASC) as NUM
FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL Server};SERVER='+@SERVER+';UID='+@UID+';PWD='+@PWD+''',
WeiShopDB_empty.dbo.i_EntSubInfoNew) AS a
LEFT JOIN
(
SELECT GOODSGUID,BRANCHGUID,ORGGUID FROM SHOP_BRANCH_STOCKCOUNT sbs1 GROUP BY GOODSGUID,BRANCHGUID,ORGGUID
) sbs ON sbs.GOODSGUID=a.ItemCode AND sbs.ORGGUID=a.EntGuid AND sbs.BRANCHGUID=a.SubCode
WHERE Billtime>='''+@Updatetime+''' and EntGuid='''+@Orgguid+'''
) t';
INSERT into @tb1 EXEC(@dosql); --插入表变量 (******)
--获取记录的总个数
SELECT @NUM = count(SubCode) from @tb1;
DECLARE @i INT = 0; --循环计数
DECLARE @now_time VARCHAR(25) = CONVERT(varchar(25), GETDATE(), 21); --当前时间
IF(@NUM > 0)
BEGIN
--循环查询库存记录并进行更新,如果之前库存已经存在则更新,否则插入
WHILE ( @i < CEILING(@NUM/@per_page) )
BEGIN
DECLARE @do_insert_sql nvarchar(MAX) = ''; --最后执行的insert sql语句
DECLARE @do_update_sql nvarchar(MAX) = ''; --最后执行的update sql语句
DECLARE @start_index INT = @i*CAST(@per_page AS INT)+1; --开始的索引值
DECLARE @end_index INT = CAST(@start_index as INT)+CAST(@per_page AS INT)-1; --结束的索引值
DECLARE @SubCode VARCHAR(50),@ItemCode VARCHAR(50),@StockQuantity NUMERIC(18,4),@UnitPrice NUMERIC(18,6),@IS_EXISTS INT,@RECORD_NUM INT;
DECLARE STOCK_CURSOR CURSOR LOCAL for (
SELECT SubCode,ItemCode,StockQuantity,UnitPrice,IS_EXISTS,NUM FROM @tb1
WHERE NUM BETWEEN @start_index AND @end_index
)
Begin Tran;--开启事务
--打开游标
OPEN STOCK_CURSOR;
--开始循环游标变量
FETCH next from STOCK_CURSOR into @SubCode,@ItemCode,@StockQuantity,@UnitPrice,@IS_EXISTS,@RECORD_NUM;
WHILE (@@FETCH_STATUS =0)
BEGIN
IF (@IS_EXISTS = 1)
BEGIN
--更新
SET @do_update_sql = @do_update_sql+'UPDATE SHOP_BRANCH_STOCKCOUNT SET STOCKCOUNT='+CAST(CAST(@StockQuantity as INT) AS VARCHAR(20))+',USERPRICE='+CAST(CAST(@UnitPrice as NUMERIC(18,2)) AS VARCHAR(20))+',MARKETPRICE='+CAST(CAST(@UnitPrice as NUMERIC(18,2)) AS VARCHAR(20))+',UPDATETIME='''+@now_time+'''
WHERE GOODSGUID='+@ItemCode+' AND BRANCHGUID='+@SubCode+';';
END
ELSE
BEGIN
--插入
DECLARE @RECORD_GUID VARCHAR(50)=NEWID();
IF ( ISNULL(@do_insert_sql, '') = '')
BEGIN
SET @do_insert_sql = @do_insert_sql+'SELECT '''+@RECORD_GUID+''','''+@Orgguid+''',&