dynamic sql 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 sql where clause

Author

Topic

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-12-05 : 07:45:37

Hi all,

How can i write dynamic query in a stored procedure??

Condition is this

In my user interface there is 10 textbox and having one button.
Its actually a search form. In this form user can enter one or more and left it empty
and click on search button.

I want to run a stored procedure that will take care of all parameter.
i send all the textbox value to the procedure.
so how can i create this type of stored procedure which having dynamic where clause

Please help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-05 : 07:52:09

check up sp_executesql or use exec() in Books OnLine

-----------------
[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-05 : 07:53:36

In sp use as many parameters as there are textboxes then validate them accordingly

Start with this
http://www.sommarskog.se/dyn-search.html

Madhivanan

Failing to plan is Planning to fail

sanjay_jadam
Yak Posting Veteran

53 Posts

Posted - 2005-12-05 : 08:02:59

can we do something like this
first we create variable for all parameter and then using condition (If ELSE) we create a where clause

like

Create proc test
@Id int = 0;
@name varchar(40) = null,
@Address varchar(100) = null,
@WhereClause varchar(100) = null

AS
SET @whereClause = 'WHERE = '
if @ID <> 0
@whereClause =@whereClause + "ID = " + @ID
else if @name <> null
@whereClause =@whereClause + "name = " + @name

-----at the last we write

SELECT * FROM test @whereClause

Guide Me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-05 : 08:41:30

quote:
SELECT * FROM test @whereClause

you can't do this but you can do this

exec ('SELECT * FROM test ' + @whereClause)

-----------------
[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-05 : 08:57:55

Read the article I specified fully

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-05 : 16:43:29

decalre @sSQL varchar (1000), @sSQLWhere varchar (1000)

-- Create the where clause using the incoming parameters as follows
-- assume parameter a --> value = 5, parameter d, value = 'ppp', parameter h = '12/05/2005'
-- from parameters, create the following:
Set @sSQLWhere = ' and a = 5 and d = ''ppp'' and h = ''12/05/2005'' '
Set @sSQL = 'Select * from MyTble Where 0 = 0' + @sSQLWhere
Exec (@sSQL )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources