SQL Server Stored Procedure - Executing Various Queries with CASE
I have two parameters for my stored procedure. Based on the value Searching_Condition
, you need to search for the corresponding column. In pseudocode format, it should be something like this
//CASE @Search_Condition
// WHEN 'UserID' THEN SELECT * FROM user_table WHERE UserID LIKE '@Keywords'
// WHEN 'UserName' THEN SELECT * FROM user_table WHERE UserName LIKE '@Keywords'
// WHEN 'UserAddress' THEN SELECT * FROM user_table WHERE UserAddress LIKE '@Keywords'
Below is the code I was working on and where it got stuck. It should be simple, but man ... since I'm not familiar with SQL Server, I struggle with it so much and CASE
in SQL Server it doesn't work as I thought.
Thank!
CREATE PROCEDURE [dbo].[USP_SP_NAME]
@Searching_Condition NVARCHAR(100),
@Keywords NVARCHAR(100)
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 3000
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
SELECT
CASE WHEN
@Searching_Condition = 'user_id' THEN
(select count(*) from user_table)
WHEN
@Searching_Condition = 'user_name' THEN
(select * from user_table)
END
END TRY
source to share
A key concept to help you get this right is the distinction between expressions and statements.
The statement is procedural and directs the flow of control. You can think of an instruction pointer going from statement to statement and each statement is isolated from the other statements (although they can choose which statements are executed after them or not). They can be thought of as verbs.
An expression is something that boils down to a value — a scalar value, a string, or even a set of strings — but the expression does not command anything. They can be thought of as nouns. These nouns cannot exist on their own, they must be in the context of a statement.
A statement CASE
in SQL Server is an expression. It is not a procedural statement such Select Case
as Visual Basic. And the trick is that when a language expects an expression, you cannot substitute an operator - and furthermore, except for some special uses, you cannot expose procedural statements in the middle of expressions (other than stringsets that can be evaluated as an expression e.g. one-column and one-line SELECT
, or EXISTS
). An expression can contain expressions containing expressions. They are like a tree that is destroyed in order to the end.
Think of the parts in EXECUTE dbo.MyStoredProcedure (8 + @@SPID) / 2
: it's a single statement with a single parameter expression, made up of three subexpressions, evaluated in a specific order, that resolve a single value that is used as an argument to a stored procedure. You couldn't follow through on your (8 + @@SPID) / 2
own because it's not a statement. (Never mind that the expression is stupid, it's simple, for example.)
I said that in some cases strings can be values, but the expected type of almost all expressions is a single value, not a set of strings. Which is the problem that comes up here - your outer operator SELECT
expects a single value to define the first column in a single row (since you don't have a clause FROM
), but you are trying to provide a whole rowset when your search term is 'user_name'
.
You can solve this problem by completely discarding CASE
and using IF
- because IF
is a procedural expression.
CREATE PROCEDURE [dbo].[USP_SP_NAME]
@Searching_Condition NVARCHAR(100),
@Keywords NVARCHAR(100)
AS
SET NOCOUNT ON;
SET LOCK_TIMEOUT 3000;
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRY
IF @Searching_Condition = 'user_id' BEGIN
select count(*) from user_table;
END
ELSE IF @Searching_Condition = 'user_name' BEGIN
select * from user_table;
END;
END TRY;
I advocate a version IF
that does not use BEGIN
and END
, and accepts a single statement - this form leads to confusion and errors. I use BEGIN
and END
every time, which seems like a pain, until you find out how much time and effort it takes to save you along the way ...
source to share
You can try this, no need to give cases where the condition will change depending on the value @Searching_Condition
:
CREATE PROCEDURE [dbo].[USP_SP_NAME]
@Searching_Condition NVARCHAR(100),
@Keywords NVARCHAR(100)
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 3000
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
exec('Select * from user_table WHERE'+ @Searching_Condition+' LIKE '+ @Keywords);
END TRY
source to share