Parameters

[simma]

Hello,
I have an sp running on two parameters.If I give only one parameter and not other then is there anyway to pick all records for second parameter?
Foe example.
I have 2 paramateres investor and pool
If I give Investor A AND I didnt give anypool then is there anyway to pick investor A with all pools..
Thanks

 

[dan1967]

In your proc you will want to tell SQL that you will allow a NULL value to be passed to this parameter:

Create Procedure MyProc
     Investor char(1),
     Pool char(1) = NULL

Dan.

 

[simma]

Thanks Dan1967
But if I do that it will give me Investor A with null in pool field.I want to specify Investor and get all pools related to A.

Investor Pool
A 1
A 2
A 3
Thanks

 

[psprague]

You can do a several different things. The first would be to switch within the proc firing different queries based on which param is null.

Using if statements and static sql:

if pool is not null and investor is not null
begin
--select using both as selection criterion
end
if pool is null
begin
-- select using investor as a criteria
end
if investor is null
begin
--select using pool as a criteria
end

You could also build up dynamic sql using if or case statements.

 

[simma]

Thanks for reading my mind psprague!! I was thinking on those lines but didnt know how to proceed.Thanks a lot...

 

[dan1967]

You might also want to explore using:

Where (IsNull(@Investor, -1) = -1 Or Investor = @Investor)
And (IsNull(@Pool, -1) = -1 Or Pool= @Pool)

This might perform a table scan instead of using any index you create for these columns. What this does is ignores any NULL value passed to the parameter and returns all, or if a value is passed uses it. Using this you can limit the number of Selects in your proc.

Dan.

 

[simma]

Thanks Dan1967!! I will try it..

 

[JohnDTampaBay]

Or this way:

WHERE Investor = IsNull(@Investor, Investor)
AND Pool = IsNull(@Pool, Pool)


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[simma]

Thanks AngelWPB !!
I am trying to run sp in query analyser with null values
like
sp_investor '',''
but it doesnt work..
Please advice
Thanks

 

[sunila7]

Both Angels and Dan's code should work if you execute the procedure like this


sp_investor NULL,NULL


Sunil

 

[simma]

Thanks Sunil it works.But Im trying to do same from Crystal reports but it doesnt work.I am passing null values and it doesnt work..I know this is SQL Forum but still....

 

[sunila7]

Can you post the code for your SP?

As in Dan's first post are you setting both the input variables to NULL if nothing is passed in?

In crystal report prompt, are you entering something or leaving it blank and executing?


Sunil

 

[simma]

Thanks sunil
Here is sp..I am not passing blank values from crystal
CREATE PROCEDURE dbo.Rpt_Test
@vendor varchar(75),
@category varchar(20)

AS
BEGIN
SET NOCOUNT ON
-------------------------------
select f.loan_number
,f.classification
,i.category
,v.[name]
from fcs_master f (nolock)
inner join loaninfo l (nolock)
on (f.loan_id = l.loan_id)
inner join property_info p (nolock)
on (b.loan_id = p.loan_id)
inner join investor_info i (nolock)
on (l.loan_id = i.loan_id)
inner join vendor v (nolock)
on (i.company_id = v.company_id)
where b.other_id='01' and f.classification<>'B'
and v.name = IsNull(@vendor,v.name) AND i.category = IsNull(@category, i.category)

 

[simma]

Hello
Thanks everyone..I finally found solution.I am using Crystal7.0 may be that was making things harder.I handled this in sp,if the parameter values are '' then I made the parameters take null..It works.
Thanks