| Answer: |
Case Statements in Transact SQL
Selection criteria in your Stored Procedures will need to change at times if you really want high reuse out of your Stored Procs. You can use CASE to do this. CASE is very similar to SELECT CASE in any othr data processing language.
The following stored procedure will select a different Price from the database based on the login level of the user.
CREATE PROCEDURE SP_Products
@cat int,
@Price nvarchar(10)
AS
Select CODE,TITLE,Version,Status,
Case @Price
When 'Price' THEN Price
When 'PriceA' Then PriceA
When 'PriceB' Then PriceB
When 'PriceC' Then PriceC
End
,Lots,LotsOf,Description,Pic
From Products
Where Category = @cat ORDER BY CODE
|
Based on what you have just seen you may be thinking CASE will work everywhere. It would be only practical to assume this will work with the ORDER BY clause.
CREATE PROCEDURE SP_Products
@cat int,
@Price nvarchar(10)
AS
Select CODE,TITLE,Version,Status,Lots,LotsOf,Description,Pic
From Products Where Category = @cat ORDER BY
Case @Price
When 'Price' THEN Title
When 'PriceA' Then Version
When 'PriceB' Then Status
When 'PriceC' Then Lots
End
|
Well guess what? It will compile file. The syntax checker in SQL Server will not throw an error but IT DOES NOT WORK. SQL server is very fussy about where CASE is used. And in most cases unlogically.
UPDATE (Feb. 6th, 2002)
The above comments are in regard to Microsoft SQL Server 6.5. The above code works in Microsoft SQL Server 7.0 and Microsoft SQL Server 2000.
Visit SQLteam.com for more great information on Transact SQL! |
