那你是如何生成表的,表字段名称到那里取?
我刚昨天完成类似的存储过程,希望对你有用。
描述:
STORAGE 为库存表,DEPOTINFO为仓库信息表,为了能使用每个仓库库存数量以列的形式表现给客户,特设计以下过程,程序还没有优化,若有BUG请EMIAL:[email protected],谢谢。
CREATE PROCEDURE [ZGKCPROC]
(
@fdepotuserid char(4),
@ldepotuserid char(4),
@fgoodsuserid char(15),
@lgoodsuserid char(15),
@dispnull int
)
AS
--生成临时表fdtmp
declare @sqlstring char(200),@fd char(4),@fdid int,@fdname char(20),@sumsqlstring char(200),@retusqlstring char(200)
declare @depotid int,@userid char(4),@depotname char(20)
CREATE TABLE [#fdtmp](
[AUTOINC] [int] IDENTITY (1, 1) NOT NULL ,
[fd] [char](20) NULL,
[fdid] [int] NULL,
[fdname] [char] (20) NULL,
) ON [PRIMARY]
CREATE TABLE [#kctmp](
[AUTOINC] [int] IDENTITY (1, 1) NOT NULL ,
[goodsid] [int] NULL,
[tota] [numeric](14,4) NULL,
) ON [PRIMARY]
if isnull(@ldepotuserid,'')<>''
Declare curtmp1 cursor for select depotid,userid,depotname from depotinfo where userid between @fdepotuserid and @ldepotuserid and used=1 order by userid
else
Declare curtmp1 cursor for select depotid,userid,depotname from depotinfo where used=1 order by userid
Open curtmp1
FETCH NEXT FROM curtmp1 INTO @depotid,@userid,@depotname
WHILE (@@fetch_status<>-1)
BEGIN
--增加#FDTMP记录
insert #fdtmp(fd,fdid,fdname) values(@userid,@depotid,@depotname)
--增加#KCTMP字段
select @sqlstring='Alter table #kctmp add '+'K'+@userid+' numeric(14,4) null'
exec (@sqlstring)
FETCH NEXT FROM curtmp1 INTO @depotid,@userid,@depotname
END
DEALLOCATE curtmp1
--生成临时表kctmp
if isnull(@lgoodsuserid,'')<>''
insert #kctmp(goodsid) select goodsid from goods where userid between @fgoodsuserid and @lgoodsuserid and used=1 order by userid
else
insert #kctmp(goodsid) select goodsid from goods where used=1 order by userid
--更新kctmp
select @sumsqlstring='update #kctmp set tota='
select @retusqlstring='select g.userid as 货号,g.goodsname as 品名,g.spec as 规格,g.goodsunit as 单位,k.tota as 合计'
declare curtmp2 cursor for select fd,fdid,fdname from #fdtmp
open curtmp2
FETCH NEXT FROM curtmp2 INTO @fd,@fdid,@fdname
WHILE (@@fetch_status<>-1)
BEGIN
--生成SQLSTRING
select @sqlstring='update #kctmp set '+'K'+rtrim(@fd)+'=s.rstnum from #kctmp k,storage s where s.goodsid=k.goodsid and s.depotid='+cast(@fdid as char);
exec (@sqlstring)
select @sumsqlstring=rtrim(@sumsqlstring)+'isnull(K'+rtrim(@fd)+',0)+'
select @retusqlstring=rtrim(@retusqlstring)+',K'+rtrim(@fd)+' as '+rtrim(@fdname)
FETCH NEXT FROM curtmp2 INTO @fd,@fdid,@fdname
END
DEALLOCATE curtmp2
--更新合计tota
select @sumsqlstring=substring(@sumsqlstring,1,len(@sumsqlstring)-1)
exec (@sumsqlstring)
--返回记录集
--select * from #kctmp
if isnull(@dispnull,0)=0
exec (@retusqlstring+' from #kctmp k join goods g on k.goodsid=g.goodsid order by userid')
else
exec (@retusqlstring+' from #kctmp k join goods g on k.goodsid=g.goodsid where tota<>0 order by userid')
我刚昨天完成类似的存储过程,希望对你有用。
描述:
STORAGE 为库存表,DEPOTINFO为仓库信息表,为了能使用每个仓库库存数量以列的形式表现给客户,特设计以下过程,程序还没有优化,若有BUG请EMIAL:[email protected],谢谢。
CREATE PROCEDURE [ZGKCPROC]
(
@fdepotuserid char(4),
@ldepotuserid char(4),
@fgoodsuserid char(15),
@lgoodsuserid char(15),
@dispnull int
)
AS
--生成临时表fdtmp
declare @sqlstring char(200),@fd char(4),@fdid int,@fdname char(20),@sumsqlstring char(200),@retusqlstring char(200)
declare @depotid int,@userid char(4),@depotname char(20)
CREATE TABLE [#fdtmp](
[AUTOINC] [int] IDENTITY (1, 1) NOT NULL ,
[fd] [char](20) NULL,
[fdid] [int] NULL,
[fdname] [char] (20) NULL,
) ON [PRIMARY]
CREATE TABLE [#kctmp](
[AUTOINC] [int] IDENTITY (1, 1) NOT NULL ,
[goodsid] [int] NULL,
[tota] [numeric](14,4) NULL,
) ON [PRIMARY]
if isnull(@ldepotuserid,'')<>''
Declare curtmp1 cursor for select depotid,userid,depotname from depotinfo where userid between @fdepotuserid and @ldepotuserid and used=1 order by userid
else
Declare curtmp1 cursor for select depotid,userid,depotname from depotinfo where used=1 order by userid
Open curtmp1
FETCH NEXT FROM curtmp1 INTO @depotid,@userid,@depotname
WHILE (@@fetch_status<>-1)
BEGIN
--增加#FDTMP记录
insert #fdtmp(fd,fdid,fdname) values(@userid,@depotid,@depotname)
--增加#KCTMP字段
select @sqlstring='Alter table #kctmp add '+'K'+@userid+' numeric(14,4) null'
exec (@sqlstring)
FETCH NEXT FROM curtmp1 INTO @depotid,@userid,@depotname
END
DEALLOCATE curtmp1
--生成临时表kctmp
if isnull(@lgoodsuserid,'')<>''
insert #kctmp(goodsid) select goodsid from goods where userid between @fgoodsuserid and @lgoodsuserid and used=1 order by userid
else
insert #kctmp(goodsid) select goodsid from goods where used=1 order by userid
--更新kctmp
select @sumsqlstring='update #kctmp set tota='
select @retusqlstring='select g.userid as 货号,g.goodsname as 品名,g.spec as 规格,g.goodsunit as 单位,k.tota as 合计'
declare curtmp2 cursor for select fd,fdid,fdname from #fdtmp
open curtmp2
FETCH NEXT FROM curtmp2 INTO @fd,@fdid,@fdname
WHILE (@@fetch_status<>-1)
BEGIN
--生成SQLSTRING
select @sqlstring='update #kctmp set '+'K'+rtrim(@fd)+'=s.rstnum from #kctmp k,storage s where s.goodsid=k.goodsid and s.depotid='+cast(@fdid as char);
exec (@sqlstring)
select @sumsqlstring=rtrim(@sumsqlstring)+'isnull(K'+rtrim(@fd)+',0)+'
select @retusqlstring=rtrim(@retusqlstring)+',K'+rtrim(@fd)+' as '+rtrim(@fdname)
FETCH NEXT FROM curtmp2 INTO @fd,@fdid,@fdname
END
DEALLOCATE curtmp2
--更新合计tota
select @sumsqlstring=substring(@sumsqlstring,1,len(@sumsqlstring)-1)
exec (@sumsqlstring)
--返回记录集
--select * from #kctmp
if isnull(@dispnull,0)=0
exec (@retusqlstring+' from #kctmp k join goods g on k.goodsid=g.goodsid order by userid')
else
exec (@retusqlstring+' from #kctmp k join goods g on k.goodsid=g.goodsid where tota<>0 order by userid')
sqlserver
syscolumns
oracle
all_tab_columns前提是你要有访问这些表的权限