SQL Table Type Variable - Insert order [Resolved]

Posted by Mokkarasu under Sql Server on 2/16/2015 | Points: 10 | Views : 546 | Status : [Member] | Replies : 3
Hi friends,

In my MS SQL procedure am using table type variable as parameter. From front the data table is created and passed to the stored procedure as parameter.

Grid from UI:

Salary --- MonthOfPayment
600 ------ 3
300 ------ 1
800 ------ 2


I want the data to be inserted in the order in which the user has entered the salary data in the grid. But I noticed while inserting the data 300 always getting inserted first, then 600 and then 800.

In spite of the data table having first row as 600, second row 300 and third row 800 the data keeps inserting in ascending order of salary always.

How to retain the inserting order as passed in the data table? Please someone help me out.


Thanks in Advance,
Karthik




Responses

Posted by: Bandi on: 2/16/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

--Sample Table creation
CREATE TABLE TestTable ( ID int identity(1,1), salary int, MonthOfPayment int)
GO
-- Create table type to pass table variable as parameter to procedure
CREATE TYPE dbo.TableVariable AS TABLE
(
Salary INT, MonthOfPayment int
)
GO
--Create SP
CREATE PROC uspInsertData (@TableVar dbo.TableVariable READONLY)
AS
INSERT INTO TestTable SELECT Salary, MonthOfPayment FROM @TableVar
GO

-- Call procedure by passing input data
DECLARE @DataTable AS dbo.TableVariable
insert @DataTable
SELECT 600,3 union all
SELECT 300,1 union all
SELECT 800,2
--call SP
EXEC uspInsertData @DataTable
GO
-- Verify the data
SELECT * FROM TestTable


NOTE: the Insert Order doesn't change in this way until unless your data table do the default sorting

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mokkarasu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/16/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
refer
http://www.techrepublic.com/blog/the-enterprise-cloud/passing-table-valued-parameters-in-sql-server-2008/
http://www.aspdotnet-suresh.com/2012/09/pass-table-as-parameter-to-stored.html

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mokkarasu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/16/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
http://www.codeproject.com/Articles/412802/Sending-a-DataTable-to-a-Stored-Procedure

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Mokkarasu, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response