Dynamic Stored Procedure - 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

SQL Server Development (2000)

Dynamic Stored Procedure

Author

Topic

karan@talash.net
Starting Member

12 Posts

Posted - 2002-06-26 : 03:44:52

My problem is that i want to create dynamic stored procedure.
Suppose i take four parameters in my stored procedure and value in the parameters may be or may not be blank.If the argument is blank then the argument shouldn't be added to sql query otherwise it will be added to query.
How is it possible.
I need your help.
Regards
Karan Bajoria

nr
SQLTeam MVY

12543 Posts

Posted - 2002-06-26 : 04:04:39

depends on what you want the query to look like

something like
where (fld1 = @fld1 or @fld1 is null)
and (fld2 = @fld2 or @fld2 is null)
....

similarly
where isnull(@fld1,fld1) = fld1
and isnull(@fld2,fld2) = fld2
...

if you want empty string instead of null
where (fld1 = @fld1 or @fld1 = '')
and (fld2 = @fld2 or @fld2 = '')
....

similarly
where fld1 like @fld1 + '%'
and fld2 like @fld2 + '%'
.....

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

dsdeming

479 Posts

Posted - 2002-06-26 : 08:34:56

You could also try something like this:

select * FROM TableName
where
CASE when @fld1 is null then 1 when fld1 = @fld1 THEN 1 else 0 END = 1
AND
CASE when @fld2 is null then 1 when fld2 = @fld2 THEN 1 else 0 END = 1
AND
CASE when @fld3 is null then 1 when fld3 = @fld3 THEN 1 else 0 END = 1

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-26 : 08:45:47

I think your best option is ...


...
where (column1 = @column1 and @column1 is not null)
...

I know its a minor distinction, but if you run the following code and look at the execution plans, I think you'll see the major index advantages of the 'and' instead of the 'or'....


use pubs
declare @lname varchar(15)
select @lname = 'afonso'
select * from employee
where lname = isnull(@lname,lname)

select * from employee
where (lname = @lname or @lname is null)

select * from employee
where case when @lname is null then 1 when @lname = lname then 1 else 0 end = 1

select * from employee
where (lname = @lname and @lname is not null)

select @lname = null
select * from employee
where lname = isnull(@lname,lname)

select * from employee
where (lname = @lname or @lname is null)

select * from employee
where case when @lname is null then 1 when @lname = lname then 1 else 0 end = 1

select * from employee
where (lname = @lname and @lname is not null)

<O>

Edited by - Page47 on 06/26/2002 08:46:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources