Calling a stored procedure from another stored procedure..

[sspeedy00]

So hello all!
I'm learning about stored procedures. Right now, I'm in the process of creating two of them, but having trouble with the syntax (I'm guessing).

So, first I have this one stored procedure:

CREATE PROCEDURE dbo.getEmpIDAndSSNFromHR
@SSN char(9),
@EmpID char(4) OUTPUT

AS

select	@EmpID = e.emplid
from	hr.dbo.tblemployee e
where	e.ssn = @SSN
GO

Let me explain what I'm trying to do here. getEmpIDAndSSNFromHR will receive one parameter, the @SSN one. The @EmpID is suppose to store the employee ID and return it (it's an output variable) to another stored procedure.

The other stored procedure will take the emplid from this one and use it as an argument.

When I try to execute the procedure, I get an error:

execute getEmpIDAndSSNFromHR '012345678'

Error: Procedure 'getEmpIDAndSSNFromHR' expects parameter '@EmpID', which was not supplied.

I've tried doing this, but keep receiving a syntax error:

DECLARE @var char(4);
execute getEmpIDAndSSNFromHR '012345678' '@var'

I'm intentionally not including the 2nd procedure so I don't confuse anyone. I'll do it if I run into any problems.

Why is it it complaining? I'm pretty new to this and appreciate any help.

 

[gmmastros]

You were close.

DECLARE @var char(4);
execute getEmpIDAndSSNFromHR '012345678'[!], @var Output[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Tell me this other proc will never need to process more than one person at a time. Tell me the calling proc isn't running some sort of cursor or loop to get everyone it needs.

Also beware of using char datatypes. If you have employee id 123 then char datatype would not match it becasue in a char datatype it would be 123<space>. If your employee id is an integer it woul dbe better to use int datatype.

Personally I almost never use output variables as I would rather return a dataset. But then I never process records one at a time if I have more than one and I always assume at tsome point there will be nmore than one. I find in learning database work it is best to start right from the beginning to think in sets of data rather than one record. It will save you enormous time and effort later as you move to more and more complex issues and find you need to worry more about performance.



"NOTHING is more important in a database than integrity." ESquared

 

[sspeedy00]

Thanks to both of you. Good to know I was pretty close! I'll reply in detail later on tonight to your post, SQLSister.

Is there a way to print the @var variable so I can see that it's collecting the correct data?

 

[SQLDenis]

>>Is there a way to print the @var variable so I can see that it's collecting the correct data?


sure

PRINT @var

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions