Thursday, March 22, 2012

CASE Problem in Stored Procedure

Can anyone see a problem with this stored procedure.
When I do this one it works fine.

CREATE Procedure SS_SoftList
(
@.CompanyID nvarchar(10),
@.Order varchar(20)
)
As

SELECT
SS_Soft_deploy.Softwarename,
( COUNT(SS_Soft_deploy.Softwarename)) as recordcount

FROM
SS_Soft_deploy

WHERE
SS_Soft_deploy.CompanyID = @.CompanyID

GROUP BY
SS_Soft_deploy.Softwarename

ORDER BY recordcount
--CASE WHEN @.Order = 'softname' THEN Softwarename END,
--CASE WHEN @.Order = 'count' THEN recordcount END
GO

However when I un-rem the CASE statement
ORDER BY
CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN recordcount END
GO

It reports an error that says "Invalid Column Name recordcount"
Any Ideas??Recordcount is an alias. You cannot sort on that using the alias.


CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN ( COUNT(SS_Soft_deploy.Softwarename)) END
|||Why does it work when I do the
ORDER BY recordcount
??|||Hmm. I did not realize that would work using the alias in the ORDER BY, but apparently it does. It does not work inside the case statement because that is a different expression, and that expression is unaware of the alias. Repeating the expression, however, works fine. If the expression were terribly complex, you could alternately create a user defined function and use that for the row and the ORDER BY (knowing that performance would likely be less than great).

No comments:

Post a Comment