Help with sorting, selecting top n and datetype

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

SQL Server Development (2000)

Help with sorting, selecting top n and datetype

Author

Topic

GrandVizier
Starting Member

14 Posts

Posted - 2007-04-19 : 20:45:20

Hej
I've been working on this query that will be converted into a Stored Procedure and then used for a report. I've pieced it together, but now I'm running into an issue with the datetype parameter. If I remove the WHERE clause which uses this parameter it runs fine, otherwise I get an error similar to:
Line 8: Incorrect syntax near '5'. depending on the date used for @StartDate.


Declare @vSQL varchar(1000)
Declare @StartDate datetype
Declare @EndDate datetype
Declare @Show int
Declare @Order int
Set @StartDate = '2006/04/05'
Set @EndDate = '2007/04/12'
Set @Show = 8
Set @Order = 2

select @vSQL = 'Select top ' + convert(varchar, @Show) + ' * From (
	SELECT Roster_Master.LASTNAME, Roster_Master.MEMBER_NO, 
		COUNT(*) AS Visits,  SUM(vUsage.Itemizer1) as Food,
		max(tbl_d_business_date.business_date) as LastDate
	FROM vUsage INNER JOIN 
		Roster_Master ON vUsage.Ref_Info_1 = Roster_Master.Member_No_DW  INNER JOIN
		tbl_d_business_date ON vUsage.fk_business_date = tbl_d_business_date.seq_num 
	WHERE (tbl_d_business_date.business_date > ' +  convert(varchar, @StartDate) + ') 
		and (tbl_d_business_date.business_date < ' +  convert(varchar, @EndDate) + ')	
	Group by LASTNAME, MEMBER_NO
) a
order by CASE WHEN ' + convert(varchar, @Order) + ' = 1 THEN Food 
              WHEN ' + convert(varchar, @Order) + ' = 2 THEN Visits 
         END Desc'

EXEC(@vSQL)

Using PRINT creates the following String:


Select top 8 * From (
	SELECT Roster_Master.LASTNAME, Roster_Master.MEMBER_NO, 
		COUNT(*) AS Visits,  SUM(vUsage.Itemizer1) as Food,
		max(tbl_d_business_date.business_date) as LastDate
	FROM vUsage INNER JOIN 
		Roster_Master ON vUsage.Ref_Info_1 = Roster_Master.Member_No_DW  INNER JOIN
		tbl_d_business_date ON vUsage.fk_business_date = tbl_d_business_date.seq_num 
	WHERE (tbl_d_business_date.business_date > Apr  5 2006 12:00AM) 
		and (tbl_d_business_date.business_date < Apr 12 2007 12:00AM)	
	Group by LASTNAME, MEMBER_NO
) a
order by CASE WHEN 2 = 1 THEN Food 
              WHEN 2 = 2 THEN Visits 
         END Desc

I've used the '#' between dates before when passing them to CR. That doesn't seem to help in this case, but is there something else equally simple that I'm missing, or is my strategy way off?
In the table design, the 'business_date' uses the Data Type: DATETYPE (datetime)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-19 : 20:58:19

First off, in any SQL statement, non-numeric values must be delimited by ''.

However ...

Why are you using dynamic SQL ??? There is absolutely no reason to be using this. If you are using dynamic sql, then you shouldn't be using things like CASE in your ordering to make it conditional, you should just be simply appending an ORDER BY expression to your string.

All you need here is something like this:


set rowcount @show  -- this sets the row count so that only the top @show rows are returned..

select 
..... 
from 
.... 
where tbl_d_business_date.businessDate > @StartDate and tbl_d_business_date.businessDate < @EndDate
...
order by case when  @order = 1 then Food else Visits end desc

That's it ... there is absolutely no need for dynamic sql. Keep it simple and just reference the values of your parameters directly in standard SQL code, don't convert everything to strings and try to concatenate it all together and then pass that string to an EXEC() ... it is completely over-complicating things.

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-04-20 : 02:39:56

"Set @StartDate = '2006/04/05'"

You ought to use yyyymmdd to be sure the "string dates" are acceptable on all locales, thus:

Set @StartDate = '20060405'

Kristen

GrandVizier
Starting Member

14 Posts

Posted - 2007-04-20 : 12:34:20

thanks for the input - I was using the dynamic string approach to use the Top N functionality - but you are correct in that the rowcount cleans up all that unnecessariness - thanks again - I always hate when I over complicate things

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-20 : 12:40:11

No problem, glad I could help!

Simple = good !!!

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-04-20 : 13:31:08

Henry Ford said the essence of good engineering was "to simplicate and add lightness"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources