help about create t-sql codes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help about create t-sql codes

Author

Topic

tempali
Starting Member

3 Posts

Posted - 2011-10-13 : 03:26:33

hi
i created a sp in sql 2000. after run, sp take about 8 min to create my table and show it.
this table have 479 row and 20 column .
my code is:

CREATE procedure KAF
as
begin
declare @x varchar(4000)
declare @Y as varchar(5000)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TKAF]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TKAF]

set @x='create table TKAF (Pname varchar(20)'
select @x=@x +', ['+cast(max([customer id]) as varchar(10))+'] varchar(4) default(0)'
from [_mojodi_total_]
group by [customer id]
order by [customer id]
set @x=@x+')'
exec (@x)
set @y='insert into tkaf(pname) select distinct [part no] from _mojodi_total_'
exec(@y)
declare @b varchar (5000)
declare @count1 int
declare @count2 int
declare @n int
declare @p int

declare @PN varchar(50)
declare @PCount int
/*-------------------*/
select @count1=count([pname]) from tkaf
select @count2=count([customer id]) from _customer_
/*--------------------*/
print (@count1)
print (@count2)
/*-----------------*/
set @n=0
set @p=1
/*---------------*/
deallocate tb3
declare tb3 cursor
FOR
select pname from tkaf
open tb3
while @p<>@count1
begin
set @p=@p+1
set @n=1
while @n<=@count2
begin
set @pcount=0
select @pcount=[rtotal] from cus_mojodi(@n ,@pn)
set @pcount=isnull(@pcount,0)
set @y='update tkaf set [' + cast(@n as varchar(5))+ ']='+ cast(@pcount as varchar(50)) + ' where pname=''' + @pn + ''''
exec(@y)
set @n=@n+1

end
fetch next from tb3 into @pn
end
exec ('select * from tkaf')
deallocate tb3
end
GO

(if you need a copy of db backup plz e-mail to me)
just forum managers
( tempali59[at]yahoo[dot]com )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-13 : 05:17:02

are you trying to crosstab dynamically in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources