June 6, 2005 at 6:54 am
I have a long piece of SQL code, which I am trying to add to a DTS package for automation.
However it complains about a couple of lines of the code, which seem to be required in ISQLW. As soon as I remove the bad lines before and after the function, it complains about the Declare.
What am I doing wrong?
The supposed bad lines are the first two and the go after the function.
use IPCC
go
create function GetQandAbyCampaign
(
@Campaign CHAR(10)
)
returns table
as
return
(
select Q.Campaign_ID Campaign_ID, Q.Question_Number Question_Number, Q.Question_Text Question_Text, A.Answer_Number Answer_Number, A.Answer_Text Answer_Text
from tbl_Answers A
join tbl_Questions Q on Q.Question_ID = A.Question_ID
where Q.Campaign_ID = @Campaign
)
----go
----select * from GetQandAbyCampaign('CV20050524')
----drop function GetQandAbyCampaign
go
declare @BeginDateTime datetime
declare @EndDateTime datetime
set @EndDateTime = convert(varchar, '05/25/2005 00:00:00', 101)
----set @EndDateTime = convert(varchar, getdate(), 101)
set @BeginDateTime = dateadd(Day, -1, @EndDateTime)
----print @BeginDateTime
----print @EndDateTime
declare @CampaignID nchar(10)
declare @RowNumber int
declare CampaignList cursor for
select distinct campid from CustOutbound where dt between @BeginDateTime and @EndDateTime
open CampaignList
fetch next from CampaignList
into @CampaignID
set @RowNumber = 0
While @@FETCH_STATUS = 0
begin
set @RowNumber = @RowNumber + 1
----print cast(@RowNumber as char(1)) + ' ' + @CampaignID
----Get Answers for each question----
print @CampaignID
----Question1----
select Q.Question_Text Question, Q.Answer_Text Answer, count (*) Total
from CustOutbound C
join GetQandAbyCampaign(@CampaignID) Q on Q.Answer_Number = C.ans1
where C.campid = @CampaignID
and C.dt between @BeginDateTime
and @EndDateTime
and Q.Question_Number = 1
group by Q.Question_Text, Q.Answer_Text
----End of Question----
----Question2----
union
select Q.Question_Text Question, Q.Answer_Text Answer, count (*) Total
from CustOutbound C
join GetQandAbyCampaign(@CampaignID) Q on Q.Answer_Number = C.ans2
where C.campid = @CampaignID
and C.dt between @BeginDateTime
and @EndDateTime
and Q.Question_Number = 2
group by Q.Question_Text, Q.Answer_Text
----End of Question----
----Repeats for another 23 Questions.
----I didn't design the table, but I have to live with it sad.gif
----End Of Answers for each Question----
fetch next from CampaignList
into @CampaignID
end
close CampaignList
deallocate CampaignList
drop function GetQandAbyCampaign
June 6, 2005 at 8:15 am
You should not be using the "GO" statements.
Set your default database on the connection that you are using instead of trying to use the "USE" statement within the TSQL.
Break the TSQL into two separate steps withing the DTS.
The first step needs to create the "getQandAbyCampaign" while the second step will execute the remaining code.
Rick
June 7, 2005 at 4:45 am
Do I break it up using Execute SQL Taks blocks? IF so how do I join them into the flow. I created the original version using the Wizard.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply