problem with storeprocedure - 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

General SQL Server Forums

New to SQL Server Programming

problem with storeprocedure

Author

Topic

ameya_amu
Starting Member

25 Posts

Posted - 2009-07-05 : 01:37:58

hello friends

i want to make storeprocedure in which, where clause must change at runtime, can i pass where clause as a parameter. is it possible;

i am having following storeprocedure how can it be reduce withless use of if statement

@POid varchar(50),
@fromdate datetime,
@todate datetime,
@Prtid bigint

any of the above parameter can contain blank value

if @POid != "" then
select * from Tablename where poid = @poid
else

if @fromdate!= "" and @todate != "" and @prtid!=0 then
select * from tablename where prtid = @prtid and (POdate>= @fromdate and PODate <= @todate )
elseif @fromdate!= "" and @todate != "" and @prtid=0 then
select * from tablename where (POdate>= @fromdate and PODate <= @todate )

else if @fromdate != "" and @todate == "" and @Prtid != 0 then
select * from tablename where POdate = @fromdate and prtid=@prtid
elseif @fromdate != "" and @todate == "" and @Prtid = 0 then
select * from tablename where POdate = @fromdate

sorry for syntax error in storeprocedure. just understand the logic and please help me out with better solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 02:29:29

looks like this


@POid varchar(50),
@fromdate datetime,
@todate datetime,
@Prtid bigint

any of the above parameter can contain blank value

select * from tablename 
where (poid = @poid or @POid = '')
and (prtid = @prtid or @Prtid = 0)
and ((POdate>= @fromdate or @fromdate='')
and (PODate <= @todate or @todate = ''))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-05 : 02:51:03

should pass in NULL instead of ''. Only string data type is able to take ''. You will have problem with other data type


select *
from   Tablename
where  (
           @poid is null
       or  poid  = @poid
       )
and    (
           @fromdate is null
       or  podate >= @fromdate
       )
and    (
           @todate is null
       or  podate <= @todate
       )
and    (
           @prtid is null
       or  prtid  = @prtid
        )

KH
[spoiler]Time is always against us[/spoiler]

ameya_amu
Starting Member

25 Posts

Posted - 2009-07-05 : 04:02:03

thanks for the reply

but there is one more problem. if value is blank then respective field must not be as part of where clause. is it possible. i want to avoid nested if statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 05:07:45

quote:
Originally posted by ameya_amu

thanks for the reply

but there is one more problem. if value is blank then respective field must not be as part of where clause. is it possible. i want to avoid nested if statement.

it wont be in my and tans suggestion. the or s will make sure that conditions wont be evaluated at all

ameya_amu
Starting Member

25 Posts

Posted - 2009-07-05 : 06:55:16

quote:
Originally posted by visakh16

quote:
Originally posted by ameya_amu

thanks for the reply

but there is one more problem. if value is blank then respective field must not be as part of where clause. is it possible. i want to avoid nested if statement.

it wont be in my and tans suggestion. the or s will make sure that conditions wont be evaluated at all

i am sorry.
i am unable understand please elobarate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 12:53:21

see the below condition for example
where (
@poid is null
or poid = @poid
)

whenever @poid is null then first part will be true so that second condition poid = @poid wont be evaluated at all. so effectively it will be like you're not applying this filter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources