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 with Table Valued Function.
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 result set that option 2, where I have converted stored procedure 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. The 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 (https://blog.sqlauthority.com)
46 Comments. Leave new
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 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
Hey,
This is a nice and useful article but the thing is we can retrieve the result of the sp as the DATASET and further retrieve the data for own use .Then why there is a need to create the table valued function than Sp?
@Archana,
Usually when you want to select data from a table, you would write something like this,
Select
Cola
,Colb
,Colc
From Table1
but lets say, there is a procedure which does the same thing,
CREATE PROC USP_SelectData
AS
Select
Cola
,Colb
,Colc
From Table1
Now when you want to retrieve data using stored procedure, then you need to execute procedure.
Execute USP_SelectData
But you cannot do a select * from USP_SelectData, this script will not run and will fail, similarly you cannot use stored procedure in your innerquiries.
So the suggested method is to execute procedure, put the output of procedure into a table and then use that table in your scripts.
~ IM
Sir, You are the best
Hi,
I want to update my field columns dynamically. For this i have written a code:
CREATE PROCEDURE [dbo].[UpdateSheet1]
(@Sheet1 varchar(50),
@ColumnName varchar(50)=[Feature Count],
@NewValue varchar(50))
AS
BEGIN
exec (‘Update ‘+ @Sheet1 + ‘ SET ‘+ @ColumnName +’=”’+ @NewValue+””)
END
Here Feature count is my column name. I want to update N number of column field. like Feature count, test cases count,…..)
What is the exact update procedure for this and my table name is Sheet1.
This is not the effecient method
Why do you want to do it dynamically?
thanks,
it really help me
I want to know when my procedure is altered last time. If is it possible please answer me
thank in advance.
If you use versions starting from 2005, query on sys.objects table. There is a column that shows when an object is last modified
Thanks.
hi
i want to select my stored procedure result
my stored procedure make a Sql Commad that return variable columns.
for example some times my stored procedure result has 5 columns some times more.
Create procedure [Fnc].[PrcArtyklAllTafsili]
As
begin
Declare @MaxTafLevel smallint, @Select varchar(max), @sql nvarchar(max), @i smallint, @tbnameindex smallint
Select @MaxTafLevel = MAX(Radif) from Fnc.TBArtTafsili
Set @Select = ‘Select A.PeriodID, A.CompanyID, A.SanadID, A.ID ArtID, A.KolID, A.MoinID’
Set @sql = ‘From Fnc.TBArtykl A’
set @tbnameindex = 98
Set @i = 1
While @i = 1
Exec sp_executesql @sql
end
i want to make a function for inserting the result to temp table and select that temp table as result table But in inline table value function i cannot use declare and come other commands
here is my Functio code:
CREATE FUNCTION FnTest
RETURNS Table
AS
return
(
Declare @MaxTafLevel smallint, @Select nvarchar(max), @i smallint, @tbnameindex smallint
Select @MaxTafLevel = MAX(Radif) from Fnc.TBArtTafsili
Set @Select = ‘If Object_ID(”TBtemp”) IS Not Null Drop Table TBTemp Create Table TBTemp(PeriodID smallint, CompanyID Smallint, SanadID Smallint, ArtID smallint, KolID smallint, MoinID smallint’
set @tbnameindex = 98
Set @i = 1
While @i <= @MaxTafLevel
begin
Set @Select = @Select + ', Taf'+CAST(@i as varchar(2))+' Int';
Set @i = @i+1;
Set @tbnameindex = @tbnameindex+1
End
Set @Select = @Select+', Bed numeric(18,0), Bes Numeric(18,0), Note NVarChar(1000))';
Exec sp_executesql @select
insert into TBtemp
Exec Fnc.PrcArtyklAllTafsili
select *
from TBtemp
)
please help
is it possible to insert 2 columns value into a temp table instead of inserting whole columns returns from sp
You can use openrowset function to get required columns from the output of a stored procedure. Refer method 2 of this post for more informations
Hello Pinal,
I am software engineer, and want the guideline to optimize my queries. i am developing an ERP system, and my database contains around 433 tables ,2681 sps, 128 table-valued and 81 scalar functions. i am facing a big problem of more execution time my some queries are taking.
Please let me know, what is the procedure to get the regular reply from you, if it will be paid, send me the details
sir i create one table ,in that table i insert values using Store Procedure,i Create that after that i execute that store Procedure in that time it will show this type of message,”There is already an object named ‘GetMariFamilyRefDat’ in the database”
Please Correct My Error My store Procedure is
CREATE PROCEDURE GetMariFamilyRefDat
AS
BEGIN
Insert into MariFamily values(‘Chell’,452,’BSc’)
end
GO
You need to just execute the procedure by
EXEC GetMariFamilyRefDat
again i will show error msg Like “the object Name is already exists
“
What does this return?
exec sp_help ‘your_procedure_name’
First reun sp_help GetMariFamilyRefDat
if you get the results this means there is object named GetMariFamilyRefDat in ur database already. first drop that object and re-execute your code.
Sir,
I want to execute my stored procedure and I couldn’t able to find how to run it by passing the values…
Can you please suggest some other ways to execute…?