Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored procedure to return all records if no parameter is passed ??? 1

Status
Not open for further replies.

zeeshan13

Programmer
Jan 28, 2005
82
US
Hi all,

I have a table called TABLE1.
With following definition
Zone nvarchar(20)
Store nvarchar(20)

I have following records in that table:

Zone Store
0001 10000
0001 20000
0001 30000
0001 40000
0001 50000
0002 80000


I have created the following stored procedure:

--Start of procedure
Create Procedure TestProd
@Zone nvarchar(20)
as
select * from TABLE1
where Zone=@Zone
--End of procedure


Now,If i execute this procedure using the following statement:

Execute TestProd '0001'


I will get every record for zone 0001, I will get the following:

Zone Store
0001 10000
0001 20000
0001 30000
0001 40000
0001 50000


Now, I want to alter my Procedure in a way that even if i have thousands of zones, & in my exeucte statement if i don't pass anay parameter it will run all Zones records, & if I pass a parameter it should return records for only that particular zone.

Any quick idea how I can achieve this???

I would apprecitae if you write the code for the procedure & the EXEC statement for calling the procedure.


Thanks a million,















 
Code:
Create Procedure TestProd
@Zone nvarchar(20) NULL
as
select * 
from   TABLE1
where  Zone = Case When IsNull(@Zone, '') = ''
                   Then Zone
                   Else @Zone
                   End

With this, @Zone can be null or an empty string and you will get all the data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
gmmastros,

Thanks for the quick reply.
The procedure definition that you just was having problems, so I remove the NULL keyword, I ran the following procedure:

Create Procedure TestProd
@Zone nvarchar(20)
as
select *
from #TABLE1
where Zone = Case When IsNull(@Zone, '') = ''
Then Zone
Else @Zone
End


Now, when I am running the following Exec statment:

Execute TestProd


I am getiing this erorr:

Procedure 'TestProd' expects parameter '@Zone', which was not supplied.

Any Idea????


Thanks,
 
I missed the = (but Denis didn't)


Code:
Create Procedure TestProd
@Zone nvarchar(20) [!]= NULL[/!]
as
select * 
from   TABLE1
where  Zone = Case When IsNull(@Zone, '') = ''
                   Then Zone
                   Else @Zone
                   End


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
gmmastros ,

I figured it out,

In your procedure definition before Null , "=" was missing. I put it & I ran the following sp which works:

Create Procedure TestProd
@Zone nvarchar(20)=NULL
as
select *
from #TABLE1
where Zone = Case When IsNull(@Zone, '') = ''
Then Zone
Else @Zone
End

Thanks a lot to both of u :)
 
Create Procedure TestProd

@Zone nvarchar(20)=NULL

as

select *
from #TABLE1
where ((@Zone IS NULL) OR (Zone = @Zone))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top