Advanced passed of params with SQL 2008

Posted on 2012-09-17
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
    LVL 9

    Accepted Solution

    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)
          SET NOCOUNT ON

          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:
    LVL 12

    Expert Comment

    by:Saurabh Bhadauria
    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 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

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

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now