how to sql variable

[Jesus4u]

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

 

[mwolf00]

You would have to create a string in your procedure and EXEC(yourString).

The better way to do it is:

IF @strDirection = "DESC"
BEGIN
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 DESC
END
ELSE
BEGIN
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 ASC
END


The reason this is better is because your procedure will execute faster since it is not using a dynamic query which it cannot develop an execution plan for...

 

[Jesus4u]

thanks that is what I thought

 

[mwolf00]

FYI - The way you were trying to do it would be:

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

DECLARE sqlStr VARCHAR(400)

SELECT sqlStr = '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

EXEC(sqlStr)
GO