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

[zeeshan13]

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,

 

[gmmastros]

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

 

[SQLDenis]

here we go

Create Procedure TestProd
@Zone nvarchar(20) = NULL
as
IF @Zone is NULL
select * from TABLE1
else
select * from TABLE1
where Zone=@Zone

all records exec TestProd
zone's only TestProd '0001'


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[zeeshan13]

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,

 

[gmmastros]

I missed the = (but Denis didn't)

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

 

[zeeshan13]

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

 

[SQLDenis]

replace Create Procedure TestProd
@Zone nvarchar(20)


with

Create Procedure TestProd
@Zone nvarchar(20) = NULL


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[JasonL99]

Create Procedure TestProd

@Zone nvarchar(20)=NULL

as

select *
from #TABLE1
where ((@Zone IS NULL) OR (Zone = @Zone))