Professional Web Applications Themes

SP with an multiple values as input - Microsoft SQL / MS SQL Server

I currently have a stored procedure like this: CREATE PROCEDURE dbo.GetCategories begin smalldatetime, end smalldatetime, USID smallint AS SELECT CA_ID FROM Categories where (t.TS_Begin between begin and end) and (t.TS_USID = USID)) GO USID is the user ID. Now I was wondering if it's possible to change the user to a list of multiple users like this: 1,5,7 I tried it like this: CREATE PROCEDURE dbo.GetCategories begin smalldatetime, end smalldatetime, USID varchar(100) AS SELECT CA_ID FROM Categories where (t.TS_Begin between begin and end) and (t.TS_USID IN (USID)) GO But when I give 1,3 as input I receive: Syntax error converting ...

  1. #1

    Default SP with an multiple values as input

    I currently have a stored procedure like this:

    CREATE PROCEDURE dbo.GetCategories
    begin smalldatetime, end smalldatetime, USID smallint
    AS
    SELECT CA_ID FROM Categories
    where (t.TS_Begin between begin and end) and (t.TS_USID = USID))
    GO

    USID is the user ID. Now I was wondering if it's possible to change the
    user to a list of multiple users like this: 1,5,7

    I tried it like this:

    CREATE PROCEDURE dbo.GetCategories
    begin smalldatetime, end smalldatetime, USID varchar(100)
    AS
    SELECT CA_ID FROM Categories
    where (t.TS_Begin between begin and end) and (t.TS_USID IN (USID))
    GO

    But when I give 1,3 as input I receive:

    Syntax error converting the varchar value '1, 3' to a column of data type
    smallint.

    I found a msg on google telling to use temporary tables but I have no clue
    how to input the list 1, 5, 7 in a temp table (in the SP).

    Anyone know how to do this?


    Stijn Verrept.


    Stijn Guest

  2. #2

    Default Re: SP with an multiple values as input

    You will have to use dynamic sql inside the stored proc to handle this
    situation.

    You would have to pass the UIDs as '1,2,3,6,7,8'

    So here is how your proc call would look: GetCategories '12/12/03',
    '12/30/03', '1,2,3,4,5,7,8,9'

    Inside the proc construct the string: str = SELECT CA_ID FROM
    Categories where (t.TS_Begin between begin and end) and (t.TS_USID IN
    (USID))
    then execute the string: EXEC(str)


    --
    -Ram Thiru
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Use of included script samples are subject to the terms specified at
    http://www.microsoft.com/info/cpyright.htm


    "Stijn Verrept" <vub.ac.be> wrote in message
    news:3f4a8bc5$0$1853$news.skynet.be... 


    Ram Guest

  3. #3

    Default Re: SP with an multiple values as input

    There are some methods suggested here:

    http://www.algonet.se/~sommar/dynamic_sql.html

    --

    Andrew J. Kelly
    SQL Server MVP


    "Stijn Verrept" <vub.ac.be> wrote in message
    news:3f4a8bc5$0$1853$news.skynet.be... 


    Andrew Guest

  4. #4

    Default Re: SP with an multiple values as input

    Great thanks, looks like a good informative site, I'll check it out.

    "Andrew J. Kelly" <com> wrote in message
    news:phx.gbl... 


    Stijn Guest

Similar Threads

  1. Replies: 9
    Last Post: April 14th, 05:07 PM
  2. Subtraction on input tag values
    By akcreative in forum Macromedia ColdFusion
    Replies: 3
    Last Post: September 19th, 02:49 PM
  3. Get input parameter values
    By ANCH in forum ASP.NET Web Services
    Replies: 1
    Last Post: May 13th, 03:02 PM
  4. [PHP] Input Validation of $_SESSION values
    By Pablo in forum PHP Development
    Replies: 8
    Last Post: November 6th, 03:06 PM
  5. Input Validation of $_SESSION values
    By Pablo in forum PHP Development
    Replies: 2
    Last Post: November 6th, 05:19 AM

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