Dynamic Where Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic Where Clause

Author

Topic

MarcLaferriere
Starting Member

4 Posts

Posted - 2002-12-09 : 12:53:57

Hello,
Can anyone tell me how to dynamically create a where clause in a stored procedure? I'm wanting to select certain data based on what a user 'checks' in a checkbox. In theory, i'm thinking that if i store what they selected in a string variable, i could do the statement like so:

SELECT var1,var2
FROM table1
WHERE "String Varable i created"

As another expample, say a user selects that they are from Chicago, and are French.

I'd want to set my varialbe to

STRINGVARA = 'CITY = Chicago AND Language = French'

THEN have my select statment be:

SELECT var1,var2
FROM table1
WHERE STRINGVARA

is it possible to do a stored procedure dynamcially?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-09 : 13:02:23

yes - look at dynamic sql, exec and sp_executesql in bol

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-09 : 14:18:06

Also, consider you might not need to dynamically create the WHERE.

If the user can pick 3 different pieces of criteria (such as @City, @State and @Language), you could do something like:

SELECT
*
FROM
Table
WHERE City = @City AND
State = @State AND
Lanuage = @Lanuage

If there is a default value of "ALL" that the user might pick for any of these, you could do:

SET @City = CASE WHEN @City = "ALL" THEN "%" ELSE @CITY END;
SET @State = CASE WHEN @State= "ALL" THEN "%" ELSE @State END;
SET @Lanuage = CASE WHEN @Lanuage = "ALL" THEN "%" ELSE @Languge END;

SELECT *
FROM Table
WHERE City LIKE @City
AND State LIKE @State
AND Language LIKE @Language

It depends on how complex your filter form is (if you can pick multiple values for the same field, that can make things much more complicated).

Anyway, I don't use dynamic SQL unless I absolutely need to.

- Jeff

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-12-12 : 12:28:43

Why Does this happen??

SET NOCOUNT ON

CREATE TABLE #customer
(custno char(6))
INSERT INTO #customer VALUES ('123abc')
INSERT INTO #customer VALUES ('456def')
INSERT INTO #customer VALUES ('789ghi')

DECLARE @cusno char(6)
SET @cusno = '123abc'
SET @cusno = CASE WHEN @cusno = 'xxxxxx' then '%' ELSE @cusno END
SELECT * FROM #customer WHERE custno LIKE @cusno
GO

--WORKS AS EXPECTED

DECLARE @cusno char(6)
SET @cusno = 'xxxxxx'
SET @cusno = CASE WHEN @cusno = 'xxxxxx' then '%' ELSE @cusno END
SELECT * FROM #customer WHERE custno LIKE @cusno
GO

--DOSEN'T WORK

DECLARE @cusno char(6)
SET @cusno = 'xxxxxx'
SET @cusno = CASE WHEN @cusno = 'xxxxxx' then '%%%%%%' ELSE @cusno END
SELECT * FROM #customer WHERE custno LIKE @cusno
GO

--WORKS???

DROP TABLE #customer

Voted best SQL forum nickname...."Tutorial-D"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-12 : 12:35:42

DECLARE @cusno char(6)
pads it with blanks
try
DECLARE @cusno varchar(6)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-12-12 : 12:39:46

nice one nr, Thank you

should have heard me the last hour trying to get this !!??

!?#

Voted best SQL forum nickname...."Tutorial-D"

vijmeena
Starting Member

10 Posts

Posted - 2003-03-12 : 04:34:44

Have you got the solution to your problem. I have a similar problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources