Multiple Input Parameters in Stored Proc

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

General SQL Server Forums

New to SQL Server Programming

Multiple Input Parameters in Stored Proc

Author

Topic

SQLFOX
Starting Member

45 Posts

Posted - 2011-01-07 : 07:39:26

Hi Guys,

I have the following stored procedure with two input parameters. I can call it with one parameter (Marital Status) but I don't know how to call the stored proc with two parameters (including the Title parameter). Any advice?

--Creating the Procedure--

Create Procedure usp_FindEmployees

@MaritalStatus Varchar(10),
@Title Varchar(100)

As

Select EmployeeID, Title, MaritalStatus From HumanResources.Employee
Where MaritalStatus=@MaritalStatus

--Calling the Procedure--

Exec usp_FindEmployees 'M'

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-07 : 07:45:40

Exec usp_FindEmployees 'M','MR'

SQLFOX
Starting Member

45 Posts

Posted - 2011-01-07 : 07:49:19

I figured it out...I had left out some of the code in the stored proc! It seems to be working now with the amended code:

Alter Procedure usp_FindEmployees
@MaritalStatus Varchar(10),
@Title Varchar(100)

As

Select EmployeeID, Title, MaritalStatus From HumanResources.Employee
Where MaritalStatus=@MaritalStatus And Title=@Title

--Calling the Procedure--
Exec usp_FindEmployees 'M','Tool Designer'

SQLFOX
Starting Member

45 Posts

Posted - 2011-01-07 : 07:52:36

Thanks pk_bohra. I had noticed that I was missing the Title part of the stored proc just after I put the post up.

Thanks for your help.

SQLFOX
Starting Member

45 Posts

Posted - 2011-01-07 : 07:56:42

How can I call a Title where I am only sure of one word in the Title, i.e. Design. I have tried the code below but it's not working...

Alter Procedure usp_FindEmployees
@MaritalStatus Varchar(10),
@Title Varchar(100)

As

Select EmployeeID, Title, MaritalStatus From HumanResources.Employee
Where MaritalStatus=@MaritalStatus And Title Like @Title

--Calling the Procedure--
Exec usp_FindEmployees 'M','%Design%'

SQLFOX
Starting Member

45 Posts

Posted - 2011-01-07 : 07:59:51

If I have 'M' for Marital Status and I am not sure of the Title, is there a way that I can call the proc to return all 'M' and their related Title?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-01-07 : 08:09:12

quote:
Originally posted by SQLFOX

If I have 'M' for Marital Status and I am not sure of the Title, is there a way that I can call the proc to return all 'M' and their related Title?

Try this:
Select EmployeeID, Title, MaritalStatus From HumanResources.Employee
Where MaritalStatus=@MaritalStatus And ( Title = @Title or @Title is null)

Exec usp_FindEmployees 'M',null

SQLFOX
Starting Member

45 Posts

Posted - 2011-01-07 : 08:12:56

Got it... :-)

Alter Procedure usp_FindEmployees
@MaritalStatus Varchar(10),
@Title Varchar(100)

As

Select EmployeeID, Title, MaritalStatus From HumanResources.Employee
Where MaritalStatus=@MaritalStatus And Title Like '%' + @Title + '%'

--Calling the Procedure--
Exec usp_FindEmployees 'M',''

SQLFOX
Starting Member

45 Posts

Posted - 2011-01-07 : 08:23:21

Thanks again! I tried your code using the null and it worked perfectly. I then tried it taking out the brackets from the Title and it returned all records for both Married and Single so the brackets is obviously needed. :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources