mssql存储过程demo

ALTER PROCEDURE [dbo].[sp_get_saleData]
AS
BEGIN
set nocount on
-- 获取最近上传数据的时间戳
declare @dd datetime
select @dd=LastUpload from sys_upload where UploadType='SaleData'
-- 将要上传哪些销售单
declare @billIds varchar(400)
declare @billId varchar(40)
DECLARE c1 CURSOR FOR
select top 5 SaleNo from Pos_Master where SaleDate>@dd -- 一次上传5笔
open c1
FETCH NEXT FROM c1 INTO @billId
WHILE @@FETCH_STATUS = 0
BEGIN
if @billIds is null begin
set @billIds=''''+@billId+''''
end else begin
set @billIds=@billIds+','+''''+@billId+''''
end
FETCH NEXT FROM c1 INTO @billId
end
close c1
deallocate c1

declare @sql varchar(2000)
declare @time2 datetime
create table #tmp(
time2 datetime
)
set @sql='insert into #tmp select max(SaleDate) from Pos_Master where SaleNo in ('+@billids+')'
exec(@sql)
select @time2=time2 from #tmp

-- 获取要上传的销售数据
set @sql='select b.SaleNo as billId,b.SaleDate,a.GoodsID,a.GoodsName,a.RetailPrice as price'
+',a.Qty,a.Amount,dbo.f_GetCookType(b.saleDate) as cooktype'
+',a.bigKindId,a.bigKindName,a.smallKindId,a.smallKindName'
+',a.UnitCode as unitid,a.Unit as unitname,'''' as vipid,'''' as vipname,b.Employee as skyid'
+',b.Createtor as skyname,'''' as ywyid,'''' as ywyname,'+''''
+convert(varchar,@time2,21)+''''+' as time2'
+' from pos_detail a'
+' inner join Pos_Master b on a.SaleNo=b.SaleNo'
+' where b.SaleNo in ('+@billIds+')'
exec (@sql)

drop table #tmp

set nocount off
END

转载于:https://www.cnblogs.com/hnxxcxg/p/3809403.html