Professional Web Applications Themes

SP with unknown number of arguments or one delimited argument - Microsoft SQL / MS SQL Server

Hi group, SQL 2000: Can anyone tell me how to create a stored procedure that will either accept an unknown number of arguments or that can accept a comma separated argument interpreted as just a single argument so that I can use an IN in my WHERE clause? Example: CREATE PROCEDURE sp_Something arg1 AS UPDATE [Something] SET [This]='that' WHERE [ID] IN (arg1) I'd like to have this procedure so that I can pass a value like this to it: EXEC sp_Something 1,2,3 I've tried passing (1,2,3) and '1,2,3', and neither of them works: The (1,2,3) creates a syntax error. The ...

  1. #1

    Default SP with unknown number of arguments or one delimited argument

    Hi group,

    SQL 2000: Can anyone tell me how to create a stored procedure that will
    either accept an unknown number of arguments or that can accept a comma
    separated argument interpreted as just a single argument so that I can use
    an IN in my WHERE clause? Example:

    CREATE PROCEDURE sp_Something arg1 AS
    UPDATE [Something] SET [This]='that' WHERE [ID] IN (arg1)

    I'd like to have this procedure so that I can pass a value like this to it:

    EXEC sp_Something 1,2,3

    I've tried passing (1,2,3) and '1,2,3', and neither of them works:

    The (1,2,3) creates a syntax error.
    The '1,2,3' treats the value as a text value, of course.

    I've tried delimiting my 1,2,3 with ", |, {}, [], and everything else I can
    think of.

    Thanks a lot,

    Ray at home



    Ray Guest

  2. #2

    Default Re: SP with unknown number of arguments or one delimited argument

    Ray
    Use dynamic SQL
    CREATE TABLE #W
    (
    COL INT NOT NULL,
    COL1 CHAR(1) NOT NULL
    )
    INSERT INTO #W VALUES (1,'A')
    INSERT INTO #W VALUES (2,'B')
    INSERT INTO #W VALUES (3,'C')
    DROP TABLE #W

    DECLARE VAR AS VARCHAR(10)
    DECLARE SQL AS VARCHAR(50)
    SET VAR ='1,2'
    SET SQL='UPDATE #W SET COL1=''D'' WHERE COL IN ('+VAR+')'
    EXEC(SQL)
    SELECT * FROM #W



    "Ray at <%=sLocation%>" <sefrhja7yasdf.com> wrote in
    message news:u#9$phx.gbl... 
    it: 
    can 


    Uri Guest

  3. #3

    Default Re: SP with unknown number of arguments or one delimited argument

    Thank you both.

    Ray at work

    "Ray at <%=sLocation%>" <sefrhja7yasdf.com> wrote in
    message news:u%239$phx.gbl... 


    Ray Guest

  4. #4

    Default Re: SP with unknown number of arguments or one delimited argument

    >> Can anyone tell me how to create a stored procedure that will either
    accept an unknown number of arguments or that can accept a comma
    separated argument interpreted as just a single argument so that I can
    use an IN in my WHERE clause? <<

    There are many kludges posted for this and you can search for them in
    this newsgroup.

    They are either (1) use dynamic SQL, thus admiting that your design is
    so bad you have to give control at run time to the user (2) p the
    CSV string with procedural code, admitting that you don't know a
    non-procedural way to do it (3) p the CSV string with procedural
    code, admitting that you know how to write complex, slow and painful to
    maintain non-procedural code.

    The right way is to put the list into a one column table and reference
    that table with the IN() predicate.
    Thus, given a host language with arrays, A[1:n] your code would look
    something like this in embedded SQL:

    INSERT INTO ParmList
    VALUES (:A[1]), (:A[2]), .. ,(:A[n]);

    The bad news is that SQL Server does not support the SQL-92 table
    constructor syntax, so this is painful.

    Pick one of the existing bad choices.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  5. #5

    Default Re: SP with unknown number of arguments or one delimited argument

    >> Can anyone tell me how to create a stored procedure that will either
    accept an unknown number of arguments or that can accept a comma
    separated argument interpreted as just a single argument so that I can
    use an IN in my WHERE clause? <<

    There are many kludges posted for this and you can search for them in
    this newsgroup.

    They are either (1) use dynamic SQL, thus admiting that your design is
    so bad you have to give control at run time to the user (2) p the
    CSV string with procedural code, admitting that you don't know a
    non-procedural way to do it (3) p the CSV string with procedural
    code, admitting that you know how to write complex, slow and painful to
    maintain non-procedural code.

    The right way is to put the list into a one column table and reference
    that table with the IN() predicate.
    Thus, given a host language with arrays, A[1:n] your code would look
    something like this in embedded SQL:

    INSERT INTO ParmList
    VALUES (:A[1]), (:A[2]), .. ,(:A[n]);

    The bad news is that SQL Server does not support the SQL-92 table
    constructor syntax, so this is painful.

    Pick one of the existing bad choices.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Replies: 2
    Last Post: February 16th, 11:39 PM
  2. Replies: 5
    Last Post: January 10th, 10:43 PM
  3. function with variable number of arguments
    By maguskrool in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: December 13th, 04:29 PM
  4. Replies: 0
    Last Post: August 20th, 08:25 AM
  5. Replies: 1
    Last Post: July 5th, 06:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139