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.
| Author |
Topic |
|
CSharpNewbie
Starting Member
39 Posts |
 Posted - 2009-02-28 : 17:47:18
|
| Hi,I am attemting to create a storedproc that reads like:Select ep.EmployeeID, GetEmployeeFirstName(ep.EmployeeID), GetEmployeeLastName(ep.EmployeeID), ed.EmployeeDateOfBirth, ed.EmployeeAddress, ed.EmployeeAddress2, ed.City, ed.State, ed.ZipCodeFROM EmployeeProfile ep, EmployeeDetail edWhere ep.EmployeeID = ed.EmployeeIDThis block of code will be a stored procedure.My issue is that GetEmployeeFirstName is a stored proc that has to be passed an EmployeeID to get the employees first and last name.How can I call a storedproc within a stored proc.ThanksMike |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-03-01 : 07:07:39
|
SQL does not allow you to embed a call to a stored proc in the select list. So you have to find alternatives.One obvious way, which you probably won't like much, is to bring in the code from the second stored procedure into your stored proc and dump the GetEmployeeName stored proc.You can embed a UDF in the select list. So another alternative is to create an equivalent UDF that will take in the EmployeeID as a parameter and return the name. UDF's have a set of restrictions in what they are allowed to do, so you will need to be cognizant of them while creating the UDF.If you are able to modify the GetEmployeeName stored proc, a third alternative is to bring in the data from the stored proc in to a table variable and then join with that table. So you would do something like this:declare @table table (employeeid,firstname, lastname);insert into @table exec GetEmployeeNames Obviously, you will need to create a stored proc that returns the three columns. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-03-02 : 03:10:53
|
| http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-02 : 10:28:08
|
quote:
Originally posted by CSharpNewbie
Hi,I am attemting to create a storedproc that reads like:Select ep.EmployeeID, GetEmployeeFirstName(ep.EmployeeID), GetEmployeeLastName(ep.EmployeeID), ed.EmployeeDateOfBirth, ed.EmployeeAddress, ed.EmployeeAddress2, ed.City, ed.State, ed.ZipCodeFROM EmployeeProfile ep, EmployeeDetail edWhere ep.EmployeeID = ed.EmployeeIDThis block of code will be a stored procedure.My issue is that GetEmployeeFirstName is a stored proc that has to be passed an EmployeeID to get the employees first and last name.How can I call a storedproc within a stored proc.ThanksMike
is it really complex logic to get first name and last name of employee? if not, it would be worth putting logic inline in your procedure as sunita suggested.making udf causes it to be called for each row which might have great overhead than doing the logic inline |
|
|
|
|
|
|
|
|
Calling a stored proc within a stored proc