I recently got many emails requesting to write a simple article. I also got a request to explain different ways to insert the values from a stored procedure into a table. Let us quickly look at the conventional way of doing the same.
Please note that this only works with the stored procedure with only one resultset. Let us create a stored procedure that returns one resultset.
/* Create Stored Procedure */
CREATE PROCEDURE TestSP
AS
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
GO
Traditional Method:
/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)
GO
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSP
GO
/* SELECT from TempTable */
SELECT *
FROM #tempTable
GO
/* Clean up */
DROP TABLE #tempTable
GO
Alternate Method: Table Valued Function
/* Create table valued function*/
CREATE FUNCTION dbo.TestFn()
RETURNS @retTestFn TABLE
(
MyDate SMALLDATETIME,
IntValue INT
)
AS
BEGIN
DECLARE @MyDate SMALLDATETIME
DECLARE @IntValue INT
INSERT INTO @retTestFn
SELECT GETDATE() AS MyDate, 1 AS IntValue
UNION ALL
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
RETURN;
END
GO
/* Select data from Table Valued Function */
SELECT *
FROM dbo.TestFn()
GO
It is clear from the resultset that option 2, where I have converted stored procedures logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. Performance of the stored procedure is “usually” better than that of functions.
We will discuss in another post regarding the type of stored procedure that can be converted into a table valued function. Let me know what you all think about this post.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
Pinal,
Thanks for such great updates… this is really helpfull
ya looking forward for next update…
Pls make me correct if i am wrong as per my understanding we can’t use all kind of SP in TVF.. (which is limited) we can use only select SP only… we can’t use delete / update statement
Looking forward for your reply
Thanks again Pinal! Good examples.
Very interesting, I always like to read what you suggest. Thanks.
Hi…
I have ans SQL Query that calls the same table name a number of times
When the table name changes I need to change all references to the table in the query
I am trying to only have to change the table name once
simplistically …
—Begin SQL Query
‘Set’ @TableName = ‘Table1′
SELECT key,column1 FROM @TableName
SELECT key, column2 FROM @TableName
SELECT x.column1,y.column3
FROM
@TableName as x
INNER JOIN
dbo.MasterTable as y
ON x.key = y.key
Warde, you will need to use dynamic sql as this is the only way to dynamically change the table name. Be sure to use sp_executesql to execute the generated sql script. Books online has great examples of using this function and why you should (cached execution plans).
-Chuck
May I add that you are using, in your example, a multi statement table value UDF which can be slower than a stored procedure.
You could instead use an inline table-value UDF which is simply a shortcut for reusable and more readable code that has no performance issues at all.
You can also speed things up even more by enabling the schema binding.
CREATE FUNCTION myFunction
( your parameters here… )
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT …
FROM ….
I strongly recommend using this kind of table-value UDFs as they are faster than the alternative you have shown and can also be a much faster alternative to views and nested queries in a huge number of cases.
They can me A LOT faster than views because data can be filtered through parameters (which you can’t do with views) and together with CROSS/OUTER APPLY, they can also speed things up a lot when used instead of nested sub queries, quite often.
I want get data from stored procedure and want to update my table. Please any body suggest me an syntax for doing this.
This is what i want to do,
Update Mytable
Set Column1 = exec MuProcedure ‘123′
some thing like above stuf.
Please help me.
@Sharan: you can’t select from the resultset of a stored proc directly.
You’d need to look at OpenQuery (http://technet.microsoft.com/en-us/library/ms188427.aspx) for this, although what you are trying to do sounds both inefficient and generally not optimal.
Hi Sharan,
What you need to do is.
You have to add one OUTPUT parameter to your Stored Procedure as:
CREATE PROC Myprocedure
@Value INT OUT
AS
SELECT @Value = 1.
Now you need to execute this SP from any query/SP as:
DECLARE @OutValue INT
EXEC Myprocedure @Value = @OutValue OUT
UPDATE Table
SET Column = @OutValue
This way you can update the table whatever is return from that SP
Tejas