|
Is it possible to do something like the following inside of a stored procedure? Where, say, some other table keeps more info on employees and their company Windows computer-use stuff. After all, not all employees may have or need computer access. The alternative is to pass it in as a parameter much like the other four columns... but if the database can look it up itself...? Essentially, then, this other stored procedure would return only the WindowsLogonID, taking an EmployeeID as a parameter to look it up, and thus automatically provide the value for this Would I be better off (or stuck) doing it like this?
(comments are locked)
|
|
Sorry but you can't. However, you do have some options. If only one value is being returned, you can rewrite your stored procedure as a user-defined scalar function, and call it like this.
INSERT INTO [Database].[Schema].[Employees]
( [EmployeeID],
[WindowsLogonID],
[Status],
[Name],
[Address] )
SELECT @EmployeeID,
dbo.tfn_GetWindowsLogonIDByEmpID (@EmployeeID),
@Status,
@Name,
@Address
If multiple values are being returned by your stored proc, you can rewrite it as a table-valued function and call it like this.
INSERT INTO [Database].[Schema].[Employees]
( [EmployeeID],
[WindowsLogonID],
[SomeOtherColumn],
[Status],
[Name],
[Address] )
SELECT @EmployeeID,
tfn.[WindowsLogonID], -- from the table valued function
tfn.[SomeOtherColumn], -- also from the function
@Status,
@Name,
@Address
FROM dbo.tfn_GetWindowsLogonIDByEmpID (@EmployeeID) tfn
However, you may wish to preserve your code as a stored proc, because the procedure does some other things which functions can't normally do, such as logging or other data manipulation, or calling other procedures. (I say "normally" because CLR makes a number of things possible, but that is far outside the scope of your question.) If this is the case, just catch the value(s) from your stored procedure into a temporary table or table variable and do your final insert from there.
CREATE TABLE #temp (with schema to match output of stored procedure)
INSERT INTO #temp
EXEC [usp_GetWindowsLogonIDByEmpID] @EmpID = @EmployeeID)
INSERT INTO [Database].[Schema].[Employees]
( [EmployeeID],
[WindowsLogonID],
[SomeOtherColumn],
[Status],
[Name],
[Address] )
SELECT @EmployeeID,
tfn.[WindowsLogonID], -- from #temp
tfn.[SomeOtherColumn], -- also from #temp
@Status,
@Name,
@Address
FROM #temp tfn
Formatting problem was because it was in the middle of an ordered list. Putting pre tags round it gives you something close to what you want (no highlighting, but then it's pretty rubbish with that anyway!)
Oct 28 '09 at 02:11 PM
Melvyn Harbour 1 ♦♦
Melvin, I figured it out and you just trashed my edits !! :) :) :)
Oct 28 '09 at 02:12 PM
Bob Hovious
Have them back in now, and thanks for the tip about the pre tags.
Oct 28 '09 at 02:17 PM
Bob Hovious
(comments are locked)
|
|
You need to do the latter really. The execution of a stored procedure is a statement, and not an expression, and insert values lists only take expressions. Take a look at this question.
(comments are locked)
|
|
You can't call a stored procedure inside an insert statement, but you can call a user defined function, in fact that is what you should do because a scalar function can return only one value mean while a stored procedure does not return any value. The code will be something like this Stored procedures certainly do return values - see http://msdn.microsoft.com/en-us/library/ms174998.aspx
Oct 28 '09 at 02:01 PM
Matt Whitfield ♦♦
(comments are locked)
|
|
One other option is to take whatever the procedure is doing and turn it into a common table expression (CTE) within your code. That way you can refer to it almost like it was a table.
(comments are locked)
|
