Stored procedure question

Question

Stored procedure question

ranuganti

Ten Centuries

Points: 1092
More actions
May 4, 2011 at 12:34 pm

#237655

create proc usp_Test
(@ID varchar (30),
@code varchar (10),
@startdate smalldatetime = NUll,
@enddate smalldatetime = Null
) as
begin
select * from vw_ABC DB
where @ID = D.ID
and ((@startdate is Null or @enddate is null) or ((Date between @startdate and @enddate)
and ((isnull(@code,'') != '' and DB.Code = @code) or isnull(@code,'') = '')))
END
I created the above stored procedure to return the rows in the following scenarios
pass all the parameters to get result
pass ID and code to get result or
pass ID and date to get result
First 2 scenarios are working fine but when am pasing the ID and date i could not return any rows
Can any one help me with this?
Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 1

ranuganti (5/4/2011)
create proc usp_Test
(@ID varchar (30),
@code varchar (10),
@startdate smalldatetime = NUll,
@enddate smalldatetime = Null
) as
begin
select * from vw_ABC DB
where @ID = D.ID
and ((@startdate is Null or @enddate is null) or ((Date between @startdate and @enddate)
and ((isnull(@code,'') != '' and DB.Code = @code) or isnull(@code,'') = '')))
END
I created the above stored procedure to return the rows in the following scenarios
pass all the parameters to get result
pass ID and code to get result or
pass ID and date to get result
First 2 scenarios are working fine but when am pasing the ID and date i could not return any rows
Can any one help me with this?
Thanks.

You need to have the @code variable also set to reurn a null if nothing is passed to it.

create proc usp_Test

(@ID varchar (30),

@code varchar (10) = NULL,

@startdate smalldatetime = NUll,

@enddate smalldatetime = Null

) as

begin

select * from vw_ABC DB

where @ID = D.ID

and ((@startdate is Null or @enddate is null) or ((Date between @startdate and @enddate)

and ((isnull(@code,'') != '' and DB.Code = @code) or isnull(@code,'') = '')))

As it stands you have to pass a value to @Code.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

LutzM SSC Guru Points: 107049 More actions · Answer 2

You might also want to have a look at Gails blog regarding the caveats of catch-all-queries[/url]

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]