How to Variable in Stored Proc? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to Variable in Stored Proc?

Author

Topic

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-10-03 : 14:58:35

I want to pass in the word ASC or DESC and use it in the code. But the SQL compiler doesn't let me. Why?


CREATE PROCEDURE dbo._GetLinkSummaryByAllDomains
 	@strDirection varchar (10) = null
AS

SELECT     COUNT(dbo.TheTracking.TrackID) AS TotalClicks, dbo.TheUsers.DomainName, dbo.TheUsers.Activity, dbo.TheUsers.id
FROM         dbo.TheUsers INNER JOIN
                      dbo.TheUserDetails ON dbo.TheUsers.id = dbo.TheUserDetails.UserID LEFT OUTER JOIN
                      dbo.TheTracking ON dbo.TheUserDetails.TrackID = dbo.TheTracking.TrackID
GROUP BY dbo.TheUsers.DomainName, dbo.TheUsers.Activity, dbo.TheUsers.id
ORDER BY dbo.TheUsers.DomainName @strDirection
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-03 : 15:17:18

just doesn't

order by case when @strDirection = 'asc' then TheUsers.DomainName else null end asc, TheUsers.DomainName desc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jesus4u
Posting Yak Master

204 Posts

Posted - 2002-10-03 : 15:19:05

quote:

just doesn't

order by case when @strDirection = 'asc' then TheUsers.DomainName else null end asc, TheUsers.DomainName desc

huh???

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-03 : 15:41:52

Take a look here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20348

What Nigel meant was that ASC and DESC are not data values, they are part of the ORDER BY syntax and therefore have to be explicit. You can't use CASE or a variable to include them in the ORDER BY clause. Therefore you need to explicitly indicate both ASC and DESC in the clause, then use a CASE expression to substitute NULL for the appropriate sort expressions.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-03 : 16:40:11

That's what I said wasn't it

.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources