Advanced passed of params with SQL 2008

Posted on 2012-09-17
Medium Priority
Last Modified: 2012-09-19
Dear experts

I need to create a stored procedure which saves more than 1 records  /actually the records can be 1 or 200/. It will be faster not to call this SP 200 times, but to pass all these 200 params (bigint) to stored procedure. How can I do this? In C# a similar case is solved with pass a string value for example 3454,4456,456546,1265  then to split the string and to loop. Is such approach possible with SP for MS SQL Server 2008 ?
Question by:dvplayltd

Accepted Solution

keyu earned 2000 total points
ID: 38408379
You can do the similar in sql 2008...

just pass  your required value inside sp parameter and do looping and split inside sp.

see the below example

CREATE PROC dbo.GetOrderList2
      @OrderList varchar(500)

      CREATE TABLE #TempList
            OrderID int

      DECLARE @OrderID varchar(10), @Pos int

      SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
      SET @Pos = CHARINDEX(',', @OrderList, 1)

      IF REPLACE(@OrderList, ',', '') <> ''
            WHILE @Pos > 0
                  SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
                  IF @OrderID <> ''
                        INSERT INTO #TempList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
                  SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
                  SET @Pos = CHARINDEX(',', @OrderList, 1)


      SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
      FROM       dbo.Orders AS o
            #TempList t
            ON o.OrderID = t.OrderID


To execute it use..

EXEC dbo.GetOrderList2 '10248,10252,10256,10261,10262,10263,10264,10265,10300,10311'

Ref Link: http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38408425
Another better approach can be ...

Microsoft has introduced  user defined Table Type Variable in sql server 2008. You can defined a table type variable in your database and can use the same at ado.net side.

Actually through this mechanism you can pass whole table  as a parameter to your stored procedure and can use it as a table .

for more detail search User Defined Table Types and Table-Valued Parameters

or look into below link..

Author Closing Comment

ID: 38408464
10x. Perfect solution, i'll use it!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question