March 14, 2008 at 3:10 am
Hi All,
I need to pass a table variable in a stored procedure.
Is it possible or not?
Actually, My web Page contains 100 records in Data Grid.
and I have to make an insert event to to update data in Database.
Now I am calling an Insert Procedure 100 Times to Insert data in my Database. which is also checking if new data then insert and if existing one then it will update the data.
In my stored procedure I am handling the Transaction for Inserts
e.g Begin Tran and Commit, Rollback too.
My problem is when it making 100 times inserts, I found some transactions are open and few also going for sleep mode?
Is there any solution for this?
All what I need, I need a single Insert to the Database and all the data should be update at once in database as per condition.
I tried to pass a table variable to procedure but it is showing syntax error.
I am not preferring to pass the data as XML because of bigger sting to pass as an inputs. I think the data grid each record contains approx contains 1000 characters.
So please help me on this Issue?
Cheers!
Sandy.
--
March 14, 2008 at 3:33 am
Sandy (3/14/2008)
Hi All,I need to pass a table variable in a stored procedure.
Is it possible or not?
Actually, My web Page contains 100 records in Data Grid.
and I have to make an insert event to to update data in Database.
Now I am calling an Insert Procedure 100 Times to Insert data in my Database. which is also checking if new data then insert and if existing one then it will update the data.
In my stored procedure I am handling the Transaction for Inserts
e.g Begin Tran and Commit, Rollback too.
My problem is when it making 100 times inserts, I found some transactions are open and few also going for sleep mode?
Is there any solution for this?
All what I need, I need a single Insert to the Database and all the data should be update at once in database as per condition.
I tried to pass a table variable to procedure but it is showing syntax error.
I am not preferring to pass the data as XML because of bigger sting to pass as an inputs. I think the data grid each record contains approx contains 1000 characters.
So please help me on this Issue?
Cheers!
Sandy.
Unfortunately on 2005 you cannot pass table variables to stored procedures as parameters. You will be able to do this on SQL Server 2008, but that is not yet released. Alternatives are XML, but I agree with you that this is not a very nice way of doing this. Inserting into the table is also an option. You may also consider bulk inserting.
Concerning your transactions not committing or rolling back, your stored procedure may have some problems.
Regards,
Andras
March 14, 2008 at 3:34 am
Hi Mr,
First of All clear you xml thinking to pass large data it will not make any problem even on web.
XML is the easiest way to insert record for this
But any way here is the code to use table variable.
Declare @vtblTempTable(id int,valvarchar(50))
Insert into @vtblTemp (id,val) values (1,'Record1')
Insert into @vtblTemp (id,val) values (2,'Record2')
Insert into @vtblTemp (id,val) values (3,'Record3')
Insert into @vtblTemp (id,val) values (4,'Record4')
Insert into @vtblTemp (id,val) values (5,'Record5')
select * from @vtblTemp
Be prepare for Sql server 2008 because it can pass table variable as parameter
March 14, 2008 at 3:45 am
One thing i forgot to say that
temp table has some records restrictions so be careful of that
March 14, 2008 at 4:19 am
hey Andras,
------------------------------------------------------
Unfortunately on 2005 you cannot pass table variables to stored procedures as parameters. You will be able to do this on SQL Server 2008, but that is not yet released. Alternatives are XML, but I agree with you that this is not a very nice way of doing this. Inserting into the table is also an option. You may also consider bulk inserting.
Yes, I agree with you that SQL 2005 is not supporting Table variable as an Input parameter,
Can you please give me one small example of bulk inserting by using stored procedure.
It will help me a lot......
Concerning your transactions not committing or rolling back, your stored procedure may have some problems.
Andras, I am sure, I Handled properly the Transactions in my Stored procedure, I am using a common format of this.
hi shamas saeed,
----------------------------------------------------------------
First of All clear you xml thinking to pass large data it will not make any problem even on web. XML is the easiest way to insert record for this
Can you give me one small example of what you describe, it would be helpful for me.
Waiting Reply......
Cheers!
Sandy.
--
March 14, 2008 at 8:03 am
You really have two options for this currently. One is XML, the other is a delimited list.
On any significant number of records, XML will be better. Yes, puting together and sending XML takes more time than a recordset, but the time saved from the line-by-line insert will more than make up for it in most cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 14, 2008 at 8:34 am
Sandy (3/14/2008)
hey Andras,
------------------------------------------------------
Unfortunately on 2005 you cannot pass table variables to stored procedures as parameters. You will be able to do this on SQL Server 2008, but that is not yet released. Alternatives are XML, but I agree with you that this is not a very nice way of doing this. Inserting into the table is also an option. You may also consider bulk inserting.
Yes, I agree with you that SQL 2005 is not supporting Table variable as an Input parameter,
Can you please give me one small example of bulk inserting by using stored procedure.
It will help me a lot......
Cheers!
Sandy.
What you can do in your web application, is to use SqlBulkCopy. Assuming you are using C# it would look like:
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection, bcpOptions, null))
{
sqlBulkCopy.BulkCopyTimeout = 0;
sqlBulkCopy.DestinationTableName = ...;
sqlBulkCopy.ColumnMappings.Clear();
.. set up column mappings
sqlBulkCopy.WriteToServer( .. something like IDataReader, a DataRow array, ...);
}
But this does not use a stored procedure (it access the table directly).
Regards,
Andras
March 16, 2008 at 11:08 pm
Hi Andras,
Thanks for Idea,.
Cheers!
Sandy.
--
March 18, 2008 at 7:43 am
which is also checking if new data then insert and if existing one then it will update the data
You could also be using the new MERGE statement that will be available in SQL Server 2008 to perform the above logic.
If the data is an array for a single column you can get away with a delimited list. There are numerous articles in the forum on methods to split and pivot the data into a work table.
If the data is an array of records (i.e., more than one field per row), then XML is your only solution until SQL Server 2008.
Side note: Oracle has supported passing arrays of records to a stored procedure or function for over 10 years!
April 8, 2009 at 11:10 am
Hi,
I come to know that we cannot pass Table variable as a parameter to storeprocedure in
SQL SERVER 2005 . Please consider below senario ...
have datatable and wanted to insert some of the column values
not all in DB.
Can anyone have alternative to do the same using storeprocedure?
Regards
Priya ...
April 8, 2009 at 11:26 am
You can pass the data to the stored procedure as an XML document. You should use the XML data type for the parameter.
Then the stored procedure can perform a bulk insert using the values from the XML attributes and/or elements.
See Jacob Sebastian's Article Sales Order Workshop Part IV http://www.sqlservercentral.com/articles/Stored+Procedures/2912/
April 15, 2009 at 4:18 am
priya_jengathe (4/8/2009)
Hi,I come to know that we cannot pass Table variable as a parameter to storeprocedure in
SQL SERVER 2005 . Please consider below senario ...
have datatable and wanted to insert some of the column values
not all in DB.
Can anyone have alternative to do the same using storeprocedure?
Regards
Priya ...
Hi Priya,
there are many different techniques to pass more structured data to stored procedures. Erland Sommarskog has compared an impressive number of these. See his article on http://www.sommarskog.se/arrays-in-sql-2005.html.
Regards,
Andras
March 26, 2010 at 6:05 am
can u say how to pass a datatable to a stored procedure when using sqlserv2008?
March 26, 2010 at 6:52 am
Jobin Daniel (3/26/2010)
can u say how to pass a datatable to a stored procedure when using sqlserv2008?
Read the SQL Server 2008 documentation (Books Online):
Table-Valued Parameters (Database Engine)
http://technet.microsoft.com/en-us/library/bb510489.aspx
Also see MSDN site:
Table-Valued Parameters in SQL Server 2008 (ADO.NET)
http://msdn.microsoft.com/en-us/library/bb675163.aspx
November 22, 2010 at 10:12 pm
hi,
newbie here, I would like to ask if it is possible for a stored procedure to accept a table as an input? i want to create a stored procedure which count the rows of any table in a given database. Im assuming that the query for this problem would be somehow like the statement given below.
note: EXEC stored.procedure_name <TABLE_NAME>
btw, this is the code which i am trying to compile for my stored procedure but it wont compile. there is an error which says "Msg 1087, Level 15, State 2, Procedure spcountrows, Line 19 Must declare the table variable "@table_name".".
code:
USE [test]
GO
/****** Object: StoredProcedure [dbo].[spcountrows] Script Date: 11/23/2010 10:34:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spcountrows]
@table_name varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT count(*)
FROM @table_name
END
PLEASE HELP ME! i am using sql server 2005
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply