[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Using variable condition on where statement in stored procedure

Posted on 2008-01-31
7
Medium Priority
?
1,167 Views
Last Modified: 2010-04-21
Please resolve the best way to make this work...

I know the below would never work, I've tried a hlaf dozen solutions including dynamic sql, but this is just the best way to describe what I'm trying to do.

If city not passed as null then I want the query to include it in the where statement.
 
CREATE PROCEDURE sp_CityStateSearch

@City  VarChar(50),
@State VarChar(2)

SELECT *
FROM TABLE
WHERE
State = @State

CASE WHEN @CIty IS NOT NULL THEN
AND City = @City
Else
0
Comment
Question by:lexo
  • 3
  • 2
  • 2
7 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 20792961
You need to build your SQL as a VarChar variable and then

EXEC (@sql)

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 20792986
This should work.
CREATE PROCEDURE sp_CityStateSearch
 
@City  VarChar(50),
@State VarChar(2)
 
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT *
FROM TABLE
WHERE State = ' + CHAR(39) + @State + CHAR(39)
 
CASE WHEN @CIty IS NOT NULL THEN
SET @SQL = @SQL + ' AND City = ' + CHAR(39) + @City + CHAR(39)
END
 
EXEC (@SQL)

Open in new window

0
 

Author Comment

by:lexo
ID: 20793008
TextReport I get the following errors:
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'EXEC'.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 800 total points
ID: 20793065
Think I gopt the CASE statement wrong so replaced with IF and it checked out OK
CREATE PROCEDURE sp_CityStateSearch
( 
@City  VarChar(50),
@State VarChar(2)
)
 
AS
 
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT *
FROM TABLE
WHERE State = ' + CHAR(39) + @State + CHAR(39)
 
IF ISNULL(@CIty,'') <> '' 
	SET @SQL = @SQL + ' AND City = ' + CHAR(39) + @City + CHAR(39)
 
 
EXEC (@SQL)

Open in new window

0
 
LVL 39

Accepted Solution

by:
appari earned 1200 total points
ID: 20793169
try this

CREATE PROCEDURE sp_CityStateSearch

@City  VarChar(50),
@State VarChar(2)

SELECT *
FROM TABLE
WHERE
State = @State
and City =
CASE WHEN @CIty IS NOT NULL THEN
@City else City end
0
 
LVL 39

Expert Comment

by:appari
ID: 20793181
or this
CREATE PROCEDURE sp_CityStateSearch

@City  VarChar(50),
@State VarChar(2)

SELECT *
FROM TABLE
WHERE
State = @State
and ( City = @City or @City is null)
0
 

Author Closing Comment

by:lexo
ID: 31426985
Sorry Text report, your solution worked but I like aaparis better because it doesnt use dynamic SQL which can be a pain sometimes.  Thank you both, split points 60/40
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question