In the code below I get an Internal SQL Server error in Query
Analyzer. Ultimately I want to get thos code working in a function.

DECLARE @WeekNumber tinyint
DECLARE @Comp decimal(18,2)
DECLARE @PeriodFromDate datetime
DECLARE @PeriodToDate datetime

SET @WeekNumber = 27

SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
Calendar WHERE WeekNumber = @WeekNumber)

SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
WHERE WeekNumber = @WeekNumber)

SET @Comp = (SELECT SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate)


But this code runs fine:


DECLARE @WeekNumber tinyint
DECLARE @Comp decimal(18,2)
DECLARE @PeriodFromDate datetime
DECLARE @PeriodToDate datetime

SET @WeekNumber = 27

SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
Calendar WHERE WeekNumber = @WeekNumber)

SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
WHERE WeekNumber = @WeekNumber)

SELECT SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate


The only difference is that here I'm not setting @Comp to the return
value of the SELECT. This code also runs fine when I sum other
columns. Any ideas?

I didn't actually try this but it looks like you want this instead:

SELECT @Comp = SUM(ActualGrossComp)
FROM dbo.fnc_ProgramLineUp_1_2()
WHERE Delay > 0
AND ClearanceCode <> 3
AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate

Instead of trying to select into a set statement you simply use the select
statement to set your own variable.

Best,
Muhd


"Carl Ganz" <seton.software@verizon.net> wrote in message
news:a1d35f16.0409271012.32733e01@posting.google.c om...
> In the code below I get an Internal SQL Server error in Query
> Analyzer. Ultimately I want to get thos code working in a function.
>
> DECLARE @WeekNumber tinyint
> DECLARE @Comp decimal(18,2)
> DECLARE @PeriodFromDate datetime
> DECLARE @PeriodToDate datetime
>
> SET @WeekNumber = 27
>
> SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
> Calendar WHERE WeekNumber = @WeekNumber)
>
> SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
> WHERE WeekNumber = @WeekNumber)
>
> SET @Comp = (SELECT SUM(ActualGrossComp)
> FROM dbo.fnc_ProgramLineUp_1_2()
> WHERE Delay > 0
> AND ClearanceCode <> 3
> AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate)
>
>
> But this code runs fine:
>
>
> DECLARE @WeekNumber tinyint
> DECLARE @Comp decimal(18,2)
> DECLARE @PeriodFromDate datetime
> DECLARE @PeriodToDate datetime
>
> SET @WeekNumber = 27
>
> SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
> Calendar WHERE WeekNumber = @WeekNumber)
>
> SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
> WHERE WeekNumber = @WeekNumber)
>
> SELECT SUM(ActualGrossComp)
> FROM dbo.fnc_ProgramLineUp_1_2()
> WHERE Delay > 0
> AND ClearanceCode <> 3
> AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate
>
>
> The only difference is that here I'm not setting @Comp to the return
> value of the SELECT. This code also runs fine when I sum other
> columns. Any ideas?

Thanks for the feedback. Your solution did work. Microsoft tech
support believes my initial problem to be a bug and suggested
INSERTING into an in-memory table.

Thanks

Carl Ganz

"Muhd" <eat@joes.com> wrote in message news:<pQ_5d.546211$gE.52683@pd7tw3no>...
> I didn't actually try this but it looks like you want this instead:
>
> SELECT @Comp = SUM(ActualGrossComp)
> FROM dbo.fnc_ProgramLineUp_1_2()
> WHERE Delay > 0
> AND ClearanceCode <> 3
> AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate
>
> Instead of trying to select into a set statement you simply use the select
> statement to set your own variable.
>
> Best,
> Muhd
>
>
> "Carl Ganz" <seton.software@verizon.net> wrote in message
> news:a1d35f16.0409271012.32733e01@posting.google.c om...
> > In the code below I get an Internal SQL Server error in Query
> > Analyzer. Ultimately I want to get thos code working in a function.
> >
> > DECLARE @WeekNumber tinyint
> > DECLARE @Comp decimal(18,2)
> > DECLARE @PeriodFromDate datetime
> > DECLARE @PeriodToDate datetime
> >
> > SET @WeekNumber = 27
> >
> > SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
> > Calendar WHERE WeekNumber = @WeekNumber)
> >
> > SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
> > WHERE WeekNumber = @WeekNumber)
> >
> > SET @Comp = (SELECT SUM(ActualGrossComp)
> > FROM dbo.fnc_ProgramLineUp_1_2()
> > WHERE Delay > 0
> > AND ClearanceCode <> 3
> > AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate)
> >
> >
> > But this code runs fine:
> >
> >
> > DECLARE @WeekNumber tinyint
> > DECLARE @Comp decimal(18,2)
> > DECLARE @PeriodFromDate datetime
> > DECLARE @PeriodToDate datetime
> >
> > SET @WeekNumber = 27
> >
> > SET @PeriodFromDate = (SELECT MIN(StartDate) AS StartDate FROM
> > Calendar WHERE WeekNumber = @WeekNumber)
> >
> > SET @PeriodToDate = (SELECT MAX(EndDate) AS StartDate FROM Calendar
> > WHERE WeekNumber = @WeekNumber)
> >
> > SELECT SUM(ActualGrossComp)
> > FROM dbo.fnc_ProgramLineUp_1_2()
> > WHERE Delay > 0
> > AND ClearanceCode <> 3
> > AND TeleCastDate BETWEEN @PeriodFromDate AND PeriodToDate
> >
> >
> > The only difference is that here I'm not setting @Comp to the return
> > value of the SELECT. This code also runs fine when I sum other
> > columns. Any ideas?