July 1, 2009 at 5:23 am
Hi Guys, this one has really got me stumped, I may have just been looking at it too long and missing an easier way to acomplish the same task. I'm providing a simple example to make it easier instead of my development TSQL.
What I'm looking to do is to execute a dynamic TSQL string via EXECUTE or sp_executesql while passing in a dynamic parameter dependend on another such as in the case below. I do not want to use a CASE statement if i can get away with it to set a common variable as the input parameter's vary at each runtime. The solution must be 2000 compatible. I've tried using sp_executesql as well although the common error i get back is:
Must declare the scalar variable @Date1
-- PARAMETERS COME FROM USER INPUT AND CAN INCREASE BASED ON REPORTING
DECLARE @Date1 DATETIME, @Date2 DATETIME, @Date3 DATETIME, @Date4 DATETIME
SET @Date1 = '1991/06/01'
SET @Date2 = '1991/06/29'
SET @Date3 = '1991/08/01'
SET @Date4 = '1991/09/04'
DECLARE @Count INT, @Month INT, @TSQL NVARCHAR(4000)
SET @Count = 1
SET @Month = 5
WHILE @Count <= @Month
BEGIN
SET @TSQL = 'Select COUNT(*)
FROM dbo.titles
WHERE pubdate >= @Date' + CONVERT(NVARCHAR(1),@Count) + '
AND pubdate < @Date' + CONVERT(NVARCHAR(1),@Count+1) + ''
EXEC (@TSQL)
PRINT (@TSQL)
SET @Count = @Count + 1
END
[/code]
Any help or suggestions are appreciated.
July 1, 2009 at 5:43 am
The error is happening as @Date1 is out of scope inside the dynamic sql statement.
There are a few potential solutions i can think of , but how are you receiving the input ?.
Is it a comma delimited list or do you have @Date1 through to @Date255.
July 1, 2009 at 5:54 am
Hey Dave,
Inputs will be parameters @Date1 to @Date255 and anywhere in between.
In time (3 - 6 months) these dates will be in stored in a table (which i believe I may be able to cursor through) but until then it is all manual input.
The dates follow no particular order and are custom to our company.
July 1, 2009 at 6:18 am
My personal option would be
DECLARE @Date1 DATETIME, @Date2 DATETIME, @Date3 DATETIME, @Date4 DATETIME
SET @Date1 = '1991/06/01'
SET @Date2 = '1991/06/29'
SET @Date3 = '1991/08/01'
SET @Date4 = '1991/09/04'
;with CteDates(Dates)
as
(
Select @Date1 union Select @Date2 union Select @date3 union Select @Date4 -- Thru 255
),
cteDatePairs(LoDate,HiDate)
as
(
Select cteDates.Dates,
nextDate.Dates
from cteDates cross apply (Select top 1 innerdates.Dates
from cteDates innerdates
where innerdates.Dates > cteDates.Dates order by cteDates.Dates) as nextdate
where cteDates.Dates is not null
)
Select cteDatePairs.LoDate,cteDatePairs.HiDate,COUNT(*)
FROM dbo.titles,
cteDatePairs
WHERE pubdate >= cteDatePairs.LoDate
AND pubdate < cteDatePairs.HiDate
group by cteDatePairs.LoDate,cteDatePairs.HiDate
July 1, 2009 at 7:13 am
Use This:
-- PARAMETERS COME FROM USER INPUT AND CAN INCREASE BASED ON REPORTING
DECLARE @TSQL1 NVARCHAR(4000)
DECLARE @TSQLAll NVARCHAR(4000)
SET @TSQL1 = '
DECLARE @Date1 DATETIME
SET @Date1 = ''1991/06/01''
DECLARE @Date2 DATETIME
SET @Date2 = ''1991/06/29''
DECLARE @Date3 DATETIME
SET @Date3 = ''1991/08/01''
DECLARE @Date4 DATETIME
SET @Date4 = ''1991/09/04'';'
--PRINT @TSQL1
DECLARE @Count INT, @Month INT, @TSQL NVARCHAR(4000)
SET @Count = 1
SET @Month = 1
WHILE @Count = @Date' + CONVERT(NVARCHAR(1),@Count) + '
AND pubdate < @Date' + CONVERT(NVARCHAR(1),@Count+1) + ''
SET @TSQLAll=@TSQL1 + @TSQL
EXEC (@TSQLAll)
PRINT (@TSQLAll)
SET @Count = @Count + 1
END
July 1, 2009 at 8:32 am
Dave Ballantyne (7/1/2009)
My personal option would be
DECLARE @Date1 DATETIME, @Date2 DATETIME, @Date3 DATETIME, @Date4 DATETIMESET @Date1 = '1991/06/01'
SET @Date2 = '1991/06/29'
SET @Date3 = '1991/08/01'
SET @Date4 = '1991/09/04'
;with CteDates(Dates) as
This won't work for sql 2000, as requested .
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 1, 2009 at 8:36 am
Dave, you are actually pretty close.
Instead of using EXEC(), use sp_executesql. This will allow you to pass in the appropriate parameter also - see BOL for how to do this.
In order to enable execution plan reuse, you might want to have one parameter (@Date) inside the dynamic sql, and then just pass the appropriate @Date1, etc. variable to it as the parameter.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 1, 2009 at 9:23 am
Dave,
Here's a solution that eliminates the dynamic sql.
-- declare and set the looping variables
DECLARE @Count INT, @Month INT
SET @Count = 1
SET @Month = 5
-- build a table to hold the dates
declare @test-2 TABLE (
RowID int IDENTITY,
DateValue datetime)
-- insert the test data
insert into @test-2
SELECT '1991/06/01' UNION ALL
SELECT '1991/06/29' UNION ALL
SELECT '1991/08/01' UNION ALL
SELECT '1991/09/04'
-- build a table to hold the titles... only need the pubdate field for this test
declare @Titles table (
pubdate datetime)
-- put @Month # of entries in for each date so we will get some counts.
insert into @Titles
select DateValue
from @test-2
cross join master.dbo.spt_values
where type = 'P'
and number between 1 and @Month
--get separate results per @count - this duplicates what you're doing now
set @Count = 1
while @Count = t1.DateValue
and t.pubdate = t1.DateValue
and t.pubdate < t2.DateValue
and c.number between 1 and @Month
and c.[type] = 'P'
group by c.number
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply