Stored Proc with Parameters - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc with Parameters

Author

Topic

srujanavinnakota
Starting Member

34 Posts

Posted - 2011-04-26 : 16:30:07

Can anyone help with Stored Procedure with optional Input Parameters.
I have created a procedure as below.

CREATE PROCEDURE dbo.Proc_customer

@RegionID int
@FirstName CHAR(50)
@LastName CHAR(50)
@CustomerID Int

AS

SELECT * FROM
Customer
WHERE Customer.RegionID=@RegionID
AND Customer.FirstName like @FirstName
AND Customer.LastName like @LastName
AND Customer.CustomerID like @CustomerID

Now I want to execute this procedure in the follwing format.

exec Proc_customer 58, '', '', NULL------should return all the customer data with regionID=58

exec Proc_customer 58, 'John', '', NULL------should return all the customer data with regionID=58 and FirstName like 'John'

exec Proc_customer 58, 'John', 'Steve', NULL------should return all the customer data with regionID=58 and FirstName like 'John' and LastName like 'Steve'

exec Proc_customer NULL, '', '', 1234------should return all the customer data with customerID=1234

If Pass in the parameters, I need to pull the data for those paramaters, or else I need to bring up all data from Customer Table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-26 : 16:53:46

You can use COLAESCE/ISNULL for the optional parameters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-26 : 16:59:16

Does something like this help?

CREATE PROCEDURE dbo.Proc_customer

@RegionID int
@FirstName CHAR(50)
@LastName CHAR(50)
@CustomerID Int

AS

SELECT *
FROM Customer

WHERE ISNULL(Customer.RegionID, '') LIKE CASE WHEN @RegionID IS NOT NULL THEN @RegionID
ELSE ISNULL(Customer.RegionID , '')
END
AND ISNULL(Customer.FirstName, '') LIKE CASE WHEN @FirstName IS NOT NULL THEN @FirstName
ELSE ISNULL(Customer.FirstName , '')
END
AND ISNULL(Customer.LastName, '') LIKE CASE WHEN @LastName IS NOT NULL THEN @LastName
ELSE ISNULL(Customer.LastName , '')
END
AND ISNULL(Customer.CustomerID, '') LIKE CASE WHEN @CustomerID IS NOT NULL THEN @CustomerID
ELSE ISNULL(Customer.CustomerID , '')
END

Hey, it compiles.

srujanavinnakota
Starting Member

34 Posts

Posted - 2011-04-26 : 16:59:46

Thanks for the quick reply.
I am not a SQL expert. Can you please give sample query.

quote:
Originally posted by tkizer

You can use COLAESCE/ISNULL for the optional parameters.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

srujanavinnakota
Starting Member

34 Posts

Posted - 2011-04-26 : 17:01:55

Thank you. Let me try it. Will let you know.

quote:
Originally posted by ajthepoolman

Does something like this help?

CREATE PROCEDURE dbo.Proc_customer

@RegionID int
@FirstName CHAR(50)
@LastName CHAR(50)
@CustomerID Int

AS

SELECT *
FROM Customer

WHERE ISNULL(Customer.RegionID, '') LIKE CASE WHEN @RegionID IS NOT NULL THEN @RegionID
ELSE ISNULL(Customer.RegionID , '')
END
AND ISNULL(Customer.FirstName, '') LIKE CASE WHEN @FirstName IS NOT NULL THEN @FirstName
ELSE ISNULL(Customer.FirstName , '')
END
AND ISNULL(Customer.LastName, '') LIKE CASE WHEN @LastName IS NOT NULL THEN @LastName
ELSE ISNULL(Customer.LastName , '')
END
AND ISNULL(Customer.CustomerID, '') LIKE CASE WHEN @CustomerID IS NOT NULL THEN @CustomerID
ELSE ISNULL(Customer.CustomerID , '')
END

Hey, it compiles.

srujanavinnakota
Starting Member

34 Posts

Posted - 2011-04-26 : 17:17:59

Thanks, it worked.

quote:
Originally posted by ajthepoolman

Does something like this help?

CREATE PROCEDURE dbo.Proc_customer

@RegionID int
@FirstName CHAR(50)
@LastName CHAR(50)
@CustomerID Int

AS

SELECT *
FROM Customer

WHERE ISNULL(Customer.RegionID, '') LIKE CASE WHEN @RegionID IS NOT NULL THEN @RegionID
ELSE ISNULL(Customer.RegionID , '')
END
AND ISNULL(Customer.FirstName, '') LIKE CASE WHEN @FirstName IS NOT NULL THEN @FirstName
ELSE ISNULL(Customer.FirstName , '')
END
AND ISNULL(Customer.LastName, '') LIKE CASE WHEN @LastName IS NOT NULL THEN @LastName
ELSE ISNULL(Customer.LastName , '')
END
AND ISNULL(Customer.CustomerID, '') LIKE CASE WHEN @CustomerID IS NOT NULL THEN @CustomerID
ELSE ISNULL(Customer.CustomerID , '')
END

Hey, it compiles.

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 19:10:50

AND!!!!!

It is POOL Weather!!!!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-27 : 09:04:38

quote:
Originally posted by X002548

AND!!!!!

It is POOL Weather!!!!

AWWWW YYEEEAAAAHHHH!!!!!

Hey, it compiles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources