October 20, 2005 at 1:51 pm
Hi
I have a procedure which runs on evry thursday and takes out the report for the week.Now in this I want to create a table at runtime which will have the column for each date and will populate the rows for all the values for that day..
Now in this lets I want to check that if a column exisits then it shud not create a column for it again lets say a user put the date as 12/10/2005-15/10/2005 now if the column for 13/10/2005 already exisits then it shud not create it again..
The code whcih I'm using in the procedure for this is
declare cur1 cursor for
select column_name as 'Parameters' from information_schema.columns where table_name='LeadReport'
open cur1
declare @colname varchar(30)
fetch next from cur1 into @colname
while @@fetch_status=0
begin
if (@varhd1) <> @colname
begin
set @varhd2='Yes'
print (@varhd1)
print (@colname)
print (@varhd2)
end
else
set @varhd2='No'
print (@varhd1)
print (@colname)
print (@varhd2)
fetch next from cur1 into @colname
end
close cur1
deallocate cur1
if @varhd2='Yes'
begin
set @qstr='alter table dbo.LeadReport add [' + @varhd1 + '] varchar(30) not null default 0'
execute (@qstr)
end
But it is giving error. Please suggesst.....
October 20, 2005 at 1:56 pm
What is the error?
Also, it helps if you post ALL the code, so we don't assume the error is because of missing DECLARE @varhd1 statements, or due to datatype mismatches.
Help me, help you!
A.J.
DBA with an attitude
October 20, 2005 at 2:02 pm
hi
Here is the entire procedure...
CREATE proc proc_LeadReport
@StartDate datetime,@EndDate datetime
as
declare @varhd1 varchar(40)
declare @varhd2 varchar(40)
IF (SELECT DATENAME(dw,dateadd(dd,1,@EndDate))) = 'Thursday'
BEGIN
declare @qstr varchar(6000)
set @varhd1 = convert(varchar(10),dateadd(dd,1,@EndDate),103)
set @varhd2=''
--***************** Alter New Column In LeadRepoert Table ***********
declare cur1 cursor for
select column_name as 'Parameters' from information_schema.columns where table_name='LeadReport'
open cur1
declare @colname varchar(30)
fetch next from cur1 into @colname
while @@fetch_status=0
begin
if (@varhd1) <> @colname
begin
set @varhd2='Yes'
print (@varhd1)
print (@colname)
print (@varhd2)
end
else
set @varhd2='No'
print (@varhd1)
print (@colname)
print (@varhd2)
fetch next from cur1 into @colname
end
close cur1
deallocate cur1
if @varhd2='Yes'
begin
set @qstr='alter table dbo.LeadReport add [' + @varhd1 + '] varchar(30) not null default 0'
execute (@qstr)
end
--**********************************************************************
if exists(select * from sysobjects where name='proc_LeadReport_table1' and type='U')
begin
drop table dbo.proc_LeadReport_table1
end
if exists(select * from sysobjects where name='proc_LeadReport_table2' and type='U')
begin
drop table dbo.proc_LeadReport_table2
end
set @qstr='select * into dbo.proc_LeadReport_table1 from [con-rec].AACleansing.dbo.callList where saledate between '''+Convert(varchar (23), @StartDate, 121)+''' and '''+CONVERT(varchar (23), @EndDate, 121)+''' union select * from [con-rec].AACleansi
ng.dbo.callList_used where saledate between '''+Convert(varchar (23), @StartDate, 121)+''' and '''+CONVERT(varchar (23), @EndDate, 121)+''''
execute(@qstr)
set @qstr='select * into dbo.proc_LeadReport_table2 from [con-rec].AALeadGen.dbo.callList where saledate between '''+Convert(varchar (23), @StartDate, 121)+''' and '''+CONVERT(varchar (23), @EndDate, 121)+''' union select * from [con-rec].AALeadGen.d
bo.callList_used where saledate between '''+Convert(varchar (23), @StartDate, 121)+''' and '''+CONVERT(varchar (23), @EndDate, 121)+''''
execute(@qstr)
--************************************************* Home Confirmed ********************************************************************************************* Home Confirmed *************************************************************** Home Confirmed
********************************************************************************************* Home Confirmed *********************************************************************************************
--2>--Focus Default Cont & Build_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname like ''%focus%''and dispotype in (''984'',''981'') and renewdtcontent=renewdtbuilding and con_estcontent = ''C'' and isnull(renewdtbuildin
g,'''') <> '''' and isnull(renewdtcontent,'''') <> '''' and isnull(CntLdNature,'''') = ''FD'' and isnull(BldLdNature,'''')=''FD'') where isnull(LeadCriteria,'''') = ''Focus Default Cont & Build_HC'' '
execute(@qstr)
--3>--Focus Default Contents_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname like ''%focus%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estcontent = ''C'') OR (dispotype in (''
983'',''978'') and con_estcontent = ''C'')) and isnull(CntLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Focus Default Contents_HC'' '
execute(@qstr)
--4>--Focus Default Buildings_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname like ''%focus%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estbuilding = ''C'') OR (dispotype in ('
'982'',''979'') and con_estbuilding = ''C'')) and isnull(BldLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Focus Default Buildings_HC'' '
execute(@qstr)
--5>--Telemarketing Cont & Build_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname NOT like ''%focus%''and dispotype in (''984'',''981'') and renewdtcontent=renewdtbuilding and con_estcontent = ''C'' and isnull(renewdtbui
lding,'''') <> '''' and isnull(renewdtcontent,'''') <> '''' and isnull(CntLdNature,'''') = ''FD'' and isnull(BldLdNature,'''')=''FD'') where isnull(LeadCriteria,'''') = ''Telemarketing Cont & Build_HC'' '
execute(@qstr)
--6>--Telemarketing Contents_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname NOT like ''%focus%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estcontent = ''C'') OR (dispotype in
(''983'',''978'') and con_estcontent = ''C'')) and isnull(CntLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Telemarketing Contents_HC'' '
execute(@qstr)
--7>--Telemarketing Buildings_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname NOT like ''%focus%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estbuilding = ''C'') OR (dispotype
in (''982'',''979'') and con_estbuilding = ''C'')) and isnull(BldLdNature,'''')= ''FD'') where isnull(LeadCriteria,'''') = ''Telemarketing Buildings_HC'' '
execute(@qstr)
--8>--Member Renewals Cont & Build_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname like ''%Member Renewals%'' and dispotype in (''984'',''981'') and renewdtcontent=renewdtbuilding and con_estcontent = ''C'' and isnull(ren
ewdtbuilding,'''') <> '''' and isnull(renewdtcontent,'''') <> '''' and isnull(CntLdNature,'''') = ''FD'' and isnull(BldLdNature,'''')=''FD'') where isnull(LeadCriteria,'''') = ''Member Renewals Cont & Build_HC'' '
execute(@qstr)
--9>--Member Renewals Contents_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname like ''%Member Renewal%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estcontent = ''C'') OR (dispoty
pe in (''983'',''978'') and con_estcontent = ''C'')) and isnull(CntLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Member Renewals Contents_HC'' '
execute(@qstr)
--10>--Member Renewals Buildings_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname like ''%Member Renewals%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estbuilding = ''C'') OR (disp
otype in (''982'',''979'') and con_estbuilding = ''C'')) and isnull(BldLdNature,'''')= ''FD'') where isnull(LeadCriteria,'''') = ''Member Renewals Buildings_HC'' '
execute(@qstr)
--11>--Welcome Calls Cont & Build_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname NOT like ''%Member Renewals%''and dispotype in (''984'',''981'') and renewdtcontent=renewdtbuilding and con_estcontent = ''C'' and isnull(
renewdtbuilding,'''') <> '''' and isnull(renewdtcontent,'''') <> '''' and isnull(CntLdNature,'''') = ''FD'' and isnull(BldLdNature,'''')=''FD'') where isnull(LeadCriteria,'''') = ''Welcome Calls Cont & Build_HC'' '
execute(@qstr)
--12>--Welcome Calls Contents_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname NOT like ''%Member Renewals%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estcontent = ''C'') OR (di
spotype in (''983'',''978'') and con_estcontent = ''C'')) and isnull(CntLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Welcome Calls Contents_HC'' '
execute(@qstr)
--13>--Welcome Calls Buildings_HC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname NOT like ''%Member Renewals%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estbuilding = ''C'') OR (d
ispotype in (''982'',''979'') and con_estbuilding = ''C'')) and isnull(BldLdNature,'''')= ''FD'') where isnull(LeadCriteria,'''') = ''Welcome Calls Buildings_HC'' '
execute(@qstr)
--************************************************* Home Estimated ************************************************* Home Estimated ************************************************* Home Estimated ************************************************* Home Est
imated ************************************************* Home Estimated ************************************************* Home Estimated ************************************************* Home Estimated
--15>Focus Default Cont & Build_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname like ''%focus%''and dispotype in (''984'',''981'') and renewdtcontent=renewdtbuilding and con_estcontent = ''E'' and isnull(renewdtbuild
ing,'''') <> '''' and isnull(renewdtcontent,'''') <> '''' and isnull(CntLdNature,'''') = ''FD'' and isnull(BldLdNature,'''')=''FD'') where isnull(LeadCriteria,'''') = ''Focus Default Cont & Build_HE'' '
execute(@qstr)
--16>--Focus Default Contents_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname like ''%focus%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estcontent = ''E'') OR (dispotype in ('
'983'',''978'') and con_estcontent = ''E'')) and isnull(CntLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Focus Default Contents_HE'' '
execute(@qstr)
--17>--Focus Default Buildings_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname like ''%focus%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estbuilding = ''E'') OR (dispotype in
(''982'',''979'') and con_estbuilding = ''E'')) and isnull(BldLdNature,'''')= ''FD'') where isnull(LeadCriteria,'''') = ''Focus Default Buildings_HE'' '
execute(@qstr)
--18>--Telemarketing Cont & Build_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname NOT like ''%focus%''and dispotype in (''984'',''981'') and renewdtcontent=renewdtbuilding and con_estcontent = ''E'' and isnull(renewdtb
uilding,'''') <> '''' and isnull(renewdtcontent,'''') <> '''' and isnull(CntLdNature,'''') = ''FD'' and isnull(BldLdNature,'''')=''FD'') where isnull(LeadCriteria,'''') = ''Telemarketing Cont & Build_HE'' '
execute(@qstr)
--19>--Telemarketing Contents_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname NOT like ''%focus%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estcontent = ''E'') OR (dispotype i
n (''983'',''978'') and con_estcontent = ''E'')) and isnull(CntLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Telemarketing Contents_HE'' '
execute(@qstr)
--20>--Telemarketing Buildings_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname NOT like ''%focus%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estbuilding = ''E'') OR (dispotyp
e in (''982'',''979'') and con_estbuilding = ''E'')) and isnull(BldLdNature,'''')= ''FD'') where isnull(LeadCriteria,'''') = ''Telemarketing Buildings_HE'' '
execute(@qstr)
--21>--Member Renewals Cont & Build_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname like ''%Member Renewals%''and dispotype in (''984'',''981'') and renewdtcontent=renewdtbuilding and con_estcontent = ''E'' and isnull(ren
ewdtbuilding,'''') <> '''' and isnull(renewdtcontent,'''') <> '''' and isnull(CntLdNature,'''') = ''FD'' and isnull(BldLdNature,'''')=''FD'') where isnull(LeadCriteria,'''') = ''Member Renewals Cont & Build_HE'' '
execute(@qstr)
--22>--Member Renewals Contents_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname like ''%Member Renewals%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estcontent = ''E'') OR (dispot
ype in (''983'',''978'') and con_estcontent = ''E'')) and isnull(CntLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Member Renewals Contents_HE'' '
execute(@qstr)
--23>--Member Renewals Buildings_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname like ''%Member Renewals%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estbuilding = ''E'') OR (dis
potype in (''982'',''979'') and con_estbuilding = ''E'')) and isnull(BldLdNature,'''')= ''FD'') where isnull(LeadCriteria,'''') = ''Member Renewals Buildings_HE'' '
execute(@qstr)
--24>--Welcome Calls Cont & Build_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname NOT like ''%Member Renewals%''and dispotype in (''984'',''981'') and renewdtcontent=renewdtbuilding and con_estcontent = ''E'' and isnull
(renewdtbuilding,'''') <> '''' and isnull(renewdtcontent,'''') <> '''' and isnull(CntLdNature,'''') = ''FD'' and isnull(BldLdNature,'''')=''FD'') where isnull(LeadCriteria,'''') = ''Welcome Calls Cont & Build_HE'' '
execute(@qstr)
--25>--Welcome Calls Contents_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname NOT like ''%Member Renewals%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estcontent = ''E'') OR (di
spotype in (''983'',''978'') and con_estcontent = ''E'')) and isnull(CntLdNature,'''') = ''FD'') where isnull(LeadCriteria,'''') = ''Welcome Calls Contents_HE'' '
execute(@qstr)
--26>--Welcome Calls Buildings_HE
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname NOT like ''%Member Renewals%'' and ((dispotype in (''984'',''981'') and renewdtcontent<>renewdtbuilding and con_estbuilding = ''E'') OR
(dispotype in (''982'',''979'') and con_estbuilding = ''E'')) and isnull(BldLdNature,'''')= ''FD'') where isnull(LeadCriteria,'''') = ''Welcome Calls Buildings_HE'' '
execute(@qstr)
--************************************************* Motor Confirmed ************************************************* Motor Confirmed ************************************************* Motor Confirmed ************************************************* Motor
Confirmed ************************************************* Motor Confirmed
--28>--Focus Default_MC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname like ''%focus%'' and con_estMotor = ''C'' and isnull(renewdtMotor,'''') <> '''' and isnull(MotLdNature,'''') = ''FD'') where isnull(LeadC
riteria,'''') = ''Focus Default_MC'' '
execute(@qstr)
--26>--Telemarketing_MC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname not like ''%focus%'' and con_estMotor = ''C'' and isnull(renewdtMotor,'''') <> '''' and isnull(MotLdNature,'''') = ''FD'') where isnull(Le
adCriteria,'''') = ''Telemarketing_MC'' '
execute(@qstr)
--27>--Member Rnls_MC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname like ''%Member Renewal%'' and con_estMotor = ''C'' and isnull(renewdtMotor,'''') <> '''' and isnull(MotLdNature,'''') = ''FD'') where isn
ull(LeadCriteria,'''') = ''Member Rnls_MC'' '
execute(@qstr)
--28>--Welcome Calls_MC
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname not like ''%Member Renewal%'' and con_estMotor = ''C'' and isnull(renewdtMotor,'''') <> '''' and isnull(MotLdNature,'''') = ''FD'') where
isnull(LeadCriteria,'''') = ''Welcome Calls_MC'' '
execute(@qstr)
--************************************************* Motor Estimated ************************************************* Motor Estimated ************************************************* Motor Estimated ************************************************* Motor
Estimated ************************************************* Motor Estimated
--29>--Focus Default_ME
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname like ''%focus%'' and con_estMotor = ''E'' and isnull(renewdtMotor,'''') <> '''' and isnull(MotLdNature,'''') = ''FD'') where isnull(LeadC
riteria,'''') = ''Focus Default_ME'' '
execute(@qstr)
--30>--Telemarketing_ME
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table1 where campaignname not like ''%focus%'' and con_estMotor = ''E'' and isnull(renewdtMotor,'''') <> '''' and isnull(MotLdNature,'''') = ''FD'') where isnull(Le
adCriteria,'''') = ''Telemarketing_ME'' '
execute(@qstr)
--31>--Member Rnls_ME
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname like ''%Member Renewal%'' and con_estMotor = ''E'' and isnull(renewdtMotor,'''') <> '''' and isnull(MotLdNature,'''') = ''FD'') where isnu
ll(LeadCriteria,'''') = ''Member Rnls_ME'' '
execute(@qstr)
--32>--Welcome Calls_ME
set @qstr='update LeadReport set [' + @varhd1 + ']=(select count(*) from proc_LeadReport_table2 where campaignname not like ''%Member Renewal%'' and con_estMotor = ''E'' and isnull(renewdtMotor,'''') <> '''' and isnull(MotLdNature,'''') = ''FD'') where
isnull(LeadCriteria,'''') = ''Welcome Calls_ME'' '
execute(@qstr)
---SUM---SUM---SUM---SUM---SUM---SUM---SUM---SUM---SUM
--1>
--Home Confirmed
set @qstr='update LeadReport set [' + @varhd1 + ']=(select sum(cast(([' + @varhd1 + ']) as int)) from [ProcessSales].[dbo].LeadReport where leadid between 2 and 13) where isnull(LeadCriteria,'''') = ''Home Confirmed'' '
execute(@qstr)
--14>
--Home Estimated
set @qstr='update LeadReport set [' + @varhd1 + ']=(select sum(cast(([' + @varhd1 + ']) as int)) from [ProcessSales].[dbo].LeadReport where leadid between 15 and 26 ) where isnull(LeadCriteria,'''') = ''Home Estimated'' '
execute(@qstr)
--27>
--Motor Confirmed
set @qstr='update LeadReport set [' + @varhd1 + ']=(select sum(cast(([' + @varhd1 + ']) as int)) from [ProcessSales].[dbo].LeadReport where leadid between 28 and 32 ) where isnull(LeadCriteria,'''') = ''Motor Confirmed'' '
execute(@qstr)
--32>
--Motor Estimated
set @qstr='update LeadReport set [' + @varhd1 + ']=(select sum(cast(([' + @varhd1 + ']) as int)) from [ProcessSales].[dbo].LeadReport where leadid between 33 and 36 ) where isnull(LeadCriteria,'''') = ''Motor Estimated'' '
execute(@qstr)
if exists(select * from sysobjects where name='proc_LeadReport_table1' and type='U')
begin
drop table dbo.proc_LeadReport_table1
end
if exists(select * from sysobjects where name='proc_LeadReport_table2' and type='U')
begin
drop table dbo.proc_LeadReport_table2
end
set @qstr='select leadid,leadcriteria,[' + @varhd1 + '] from LeadReport order by leadid'
execute(@qstr)
end
else
BEGIN
PRINT 'Sorry Prcedure cannot execute because execution day is not Thursday'
PRINT 'So Please enter another date range'
PRINT ' '
Return
END
--select substring(@qstr,1,250)
--select substring(@qstr,251,250)
--select substring(@qstr,501,250)
--return
select * from LeadReport
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply