SqlServer存储过程输出参数

发布时间：2020-12-12 13:29:46 所属栏目：MsSql教程来源：网络整理

导读：if exists(select 1 from sysobjects where name='P_PreOrderInfo') drop Procedure P_PreOrderInfo go Create Procedure P_PreOrderInfo? ? ?@RefSheetID varchar(64),? ?@OutShopID varchar(64),? ?@CustomID varchar(64),? ?@InShopID varchar(64),? ? ?

if exists(select 1 from sysobjects where name='P_PreOrderInfo') drop Procedure P_PreOrderInfo go Create Procedure P_PreOrderInfo? ? ?@RefSheetID varchar(64),? ?@OutShopID varchar(64),? ?@CustomID varchar(64),? ?@InShopID varchar(64),? ? ? ?@Operator varchar(64),? ?@Name varchar(64),? ?@Tax varchar(64),? ?@CertType varchar(64),? ? ?@CertNo varchar(64),? ? ?@Mobile varchar(64),? ?@LinkMan varchar(64),? ?@LinkTele varchar(64),? ?@Address varchar(64),? ?@Notes varchar(64),? ? ? ?@GoodsID varchar(2000),? ?@BarCodeID varchar(2000),? ?@CustomPrice varchar(2000),? ?@PKNum varchar(2000),? ?@PKName varchar(2000),? ?@PKSpec varchar(2000) -- ?,@a varchar(8000) out WITH ENCRYPTION AS BEGIN ? declare @SheetID char(16); ? --select @OutShopID=RationShopID,@CustomID=CustomerID from shop where ID=@ShopID ? select @SheetID=max(SheetID) from PreCustomerOrder0 where left(sheetID,8)=CONVERT(varchar(100),GETDATE(),112) ? if @SheetID is null ? ? begin ? ? set @SheetID=CONVERT(varchar(100),112)+'00000001' ? end ? else ? begin ? ? set @SheetID=substring(@SheetID,1,8)+ right('0000000'+convert(varchar(8),convert(int,substring(@SheetID,9,8))+1),8) ? end ? declare @sql varchar(2000) ? set @sql='Insert Into PreCustomerOrder0( ?SheetID,RefSheetID,OutShopID,CustomID,InShopID,PurDate,Editor,EditDate,Operator,'+ ? ? ? ? ? ?'Notes,[Address],Mobile,LinkTele,LinkMan,CertType,CertNo,Tax,CertName) values('''+ ? ? ? ? ? ?@SheetID+''','''+@RefSheetID+''','''+@OutShopID+''','+@CustomID+','''+@InShopID+''',getdate(),'''+@Operator+''','''+@Operator +''','''+ ? ? ? ? ? ?@Notes+''','''+@Address+''','''+@Mobile+''','''+@LinkTele+''','''+@LinkMan+''','''+@CertType+''','''+ ? ? ? ? ? ?@CertNo+''','''+@Tax+''','''+@Name+''');'; ? ? ? ? declare @tempGoodsID varchar(100) ? declare @tempBarCodeID varchar(100) ? declare @tempCustomPrice varchar(100) ? declare @tempPKNum varchar(100) ? declare @tempPKName varchar(100) ? declare @tempPKSpec varchar(100) ? declare @sql2 varchar(8000) ? set @sql2=''; ? while ?charindex(',',@GoodsID)>0? ? begin ? ? set @tempGoodsID=substring(@GoodsID,charindex(',@GoodsID)-1); ? ? set @GoodsID=substring(@GoodsID,? ?charindex(',@GoodsID)+1,? ?len(@GoodsID)-charindex(',@GoodsID) ? ?); ? ?? ? ? set @tempBarCodeID=substring(@BarCodeID,@BarCodeID)-1); ? ? set @BarCodeID=substring(@BarCodeID,@BarCodeID)+1,len(@BarCodeID)-charindex(',@BarCodeID)); ? ? set @tempCustomPrice=substring(@CustomPrice,@CustomPrice)-1); ? ? set @CustomPrice=substring(@CustomPrice,@CustomPrice)+1,len(@CustomPrice)-charindex(',@CustomPrice)); ? ? set @tempPKNum=substring(@PKNum,@PKNum)-1); ? ? set @PKNum=substring(@PKNum,@PKNum)+1,len(@PKNum)-charindex(',@PKNum)); ? ? set @tempPKName=substring(@PKName,@PKName)-1); ? ? set @PKName=substring(@PKName,@PKName)+1,len(@PKName)-charindex(',@PKName)); ? ? set @tempPKSpec=substring(@PKSpec,@PKSpec)-1); ? ? set @PKSpec=substring(@PKSpec,@PKSpec)+1,len(@PKSpec)-charindex(',@PKSpec)); ? ? set @sql2=@sql2+'Insert Into PreCustomerOrderItem0( ?SheetID,GoodsID,BarCodeID,CustomPrice,PKNum,PKName,PKSpec) values('''+ ? ? ? ? ? ?@SheetID+''','''+@tempGoodsID+''','''+@tempBarCodeID+''','+@tempCustomPrice+','+@tempPKNum+','''+@tempPKName+''','''+@tempPKSpec+''');';? ?? ? end? ? --select @a= @sql+@sql2 ?? ? exec(@sql+@sql2) end? ?go ?? ?? ?? ?declare @b varchar(8000); ? Exec P_PreOrderInfo N'P113201605180006','-1','020L06','1','2','0','3','4',? '5','56','7','10140102,'6914032100010,'158.00,?'1,'F,'1*1,@b out ? select @b

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!