locked
Execute a stored procedure RRS feed

  • Question

  • I have a SP   ( [dbo].[uspRpt_SalesAssignmentList] )  with two parameters (@businessunit, @Salesrep). I want to execute this with  multiple sales rep's below gives me error. How do i execute this with multiple Salesrep's. Thanks..

     

    exec uspRpt_SalesAssignmentList 'ctc','('SA00569','SA00003','SA00159')'

     

     


    svk
    Monday, December 19, 2011 9:30 PM

Answers

  • It will depend on how your stored procedure is handling this parameter. If you are using SS 2008 or greater, then you should use a table-valued parameter, if not, then you will need a varchar / nvarchar parameter to accept a delimited list or an xml doc. You will need to shred the list and return a row per each element in the list, and use JOIN / IN / EXISTS depend on your query.

    You will find a deep comparison of different methods to shred the list, in this great article.

    Arrays and Lists in SQL Server
    http://www.sommarskog.se/arrays-in-sql.html

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by czarvk Monday, December 19, 2011 9:39 PM
    Monday, December 19, 2011 9:35 PM

All replies

  • How the parameters for SP are defined?

    If the @SalesRep is supposed to be comma delimited list, then you can try

    exec uspRpt_SalesAssignmentList 'ctc','SA00569,SA00003,SA00159'

    assuming that the SP splits the values correctly. If the procedure expects a single value of Sales Rep, then you can run your SP in the loop for each different Sales Rep.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Kiranmayee Monday, December 19, 2011 9:33 PM
    Monday, December 19, 2011 9:32 PM
  • It will depend on how your stored procedure is handling this parameter. If you are using SS 2008 or greater, then you should use a table-valued parameter, if not, then you will need a varchar / nvarchar parameter to accept a delimited list or an xml doc. You will need to shred the list and return a row per each element in the list, and use JOIN / IN / EXISTS depend on your query.

    You will find a deep comparison of different methods to shred the list, in this great article.

    Arrays and Lists in SQL Server
    http://www.sommarskog.se/arrays-in-sql.html

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by czarvk Monday, December 19, 2011 9:39 PM
    Monday, December 19, 2011 9:35 PM
  • I use table valued and the answer is

     

    exec uspRpt_SalesAssignmentList 'ctc','SA00569,SA00003,SA00159'.

     

    Thanks for the help guys.


    svk
    Monday, December 19, 2011 9:40 PM
  • If you're using table valued parameter, then you can not pass values this way. 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, December 19, 2011 9:45 PM
  • How else can i pass the parameters.
    svk
    Tuesday, December 20, 2011 3:23 PM
  • The way you pass parameters depends on how the procedure is defined. If you want to use table valued parameter, then Pinal Dave has a very good tutorial on how it's done:

    SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Table-Valued Store Procedure Parameters – Day 25 of 35

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Tuesday, December 20, 2011 3:28 PM
    Tuesday, December 20, 2011 3:27 PM