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
I am new to this site sir, i am very happy to see this site.it’s really helpful sir,
i have to add another another button of adding details using add burtton
i want stored procedure for inserting values and the values should dispaly in clicking the required button
Hi sir
I want how to create procedure dynamically for insert table values
means
creaet proc tbl(@tblname varchar(100))as
begin
declare @name nvarchar(1000)
select @name=’select * from’ +char(39)+ @tblname +char(39)
print @name
end
so finally we got out put like this
select * from ’emp’
then we coppy and past upper and then execute the query
similarly i want insert procedure dynamically when i will give a table name
in procedure at that time
please help me
regards
nareshreddy
hi,
My procedure returns three tables as it has three select statement inside.
now I want to Insert result in different three tables.
How to do that?
eg. proc
create proc test
as
begin
select * from table1 —result to be inserted into R_table1
select * from table2 —result to be inserted into R_table2
select * from table3 —result to be inserted into R_table3
end
Hi Sir,
If I am creating a SP and in that I have to insert data in a table and I want input another sp retuned value in the column of that table so how can I insert the value of sp to a column of a table.
Plase help me out.
Thanks ®ard
Dewesh Pushkar
I have 3 tables (AccountNum, Details, and Main). I need to upload a purchase into these 3 tables. The main is the main, and AccountNum and Details are tables that many have multiple entries per main entry. I would like to use a Stored Procedure to insert values into these tables. I am using VWD 2010. Any help would be helpfule.
Mike Hinkle
I create a store procedure to check the validity of ID numbers.
store proc gets the Id number as an argument.
Id_numbers are stored in a table. i want to pass the Id_number column as an argument and update validity on another column in same table . any help would be appreciated.
Why we use stored procedure?why we cannot directly create table?why we first declared a table and in that we create a stored procedure?
Why we use stored procedure?
why we cannot directly create table?
why we first declared a table and in that we create a stored procedure?
bcoz it doesn’t create multiple problems in coding.. coding looks very simple.
hi…..sir this best for me
i am sql user
One of the variables that my stored procedure is returning is a timestamp. I call this Stored Procedure from a different Stored Procedure and inserts into a @table. But it gives an error:
Msg 273, Level 16, State 1, Procedure , Line 193
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
Nilesh – that’s an expected behavior. If datatype of a column is timestamp, you can’t insert values. What exactly you are tying to achieve?
It is resolved now:
I had declared as the @tmpTable (in which i am saving the values returned by my Stored Procedure call) to receive value of timestamp which is why I was getting the error:
DECLARE @SP_OUTPUT TABLE (
,
.,
.,
[COLUMN_TIMESTAMP] timestamp NULL
)
But I wanted to track the output that the stored procedure was returning. So I made the following change:
DECLARE @SP_OUTPUT TABLE (
,
.,
.,
[COLUMN_TIMESTAMP] BIGINT NULL
)
Changing the datatype to BIGINT resolves all the issues with timestamp.
Good. I am glad that my tip helped you.
You can do it without creating a table type by calling the store procedure with dummy value to create table type and then use that table to insert the value
SELECT *
INTO #tmpTable
FROM OPENQUERY(DATABASESERVERNAME,’ YOURPROCEDURECALL ”-1”’ )
insert into #tmpTable
exec(YOURPROCEDURECALL @ACTUALVALUE )
try this
create procedure spTS_BatchEntry
@stage varchar(10),
@BatchNo int,
@BatchRefNo int,
@BatchDate datetime,
@Duration int,
@InputQty float,
@OutputQty float
as
begin
insert into [dbo].[TS_Sys_BatchEntry] (
[Stage],[BatchNo],[BatchRefNo],[BatchDate],[Duration],[InputQty],[OutputQty]
)values( @stage,@BatchNo,@BatchRefNo,@BatchDate,@Duration,@InputQty,@OutputQty)
end
I want to call above stored procedure in wcf service, can anyone tell me how i can do it