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?
asked Oct 28 '09 at 01:33 PM in Default
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
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
answered Oct 28 '09 at 01:50 PM
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.
answered Oct 28 '09 at 04:01 PM
Grant Fritchey ♦♦