Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2010
    Posts
    28

    Unanswered: Stored Procedure with IN Statement

    Hi All

    I have the following Stored Procedure that asks for parameter @CaseID in my table that is type INT but how can I insert the values in the parameter when I MUST use string as the variable as the IN statement it only accepts a "," (comma) to sperate the values.

    for example

    Code:
    CREATEPROCEDURE [dbo].[spEvents]
    
    	@CaseID nvarchar(10)
    
    AS
    
    SELECT     tblEvents.CaseID
    FROM       tblEvents
    WHERE     (tblEvents.CaseID IN (@CaseID))
    so when I goto execute my stored procedure like

    Code:
    exec spEvents @CaseID = '21474,21475'
    It comes back with the following error

    Code:
    Msg 245, Level 16, State 1, Procedure spEvents, Line 7
    Conversion failed when converting the nvarchar value '21474,2147' to data type int.
    Any ideas on how I can overcome this problem?

    So if I use the statement as follows it works fine without the parameter

    Code:
    SELECT     tblEvents.CaseID
    FROM       tblEvents
    WHERE     (tblEvents.CaseID IN (21474,21475))

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Look up split functions in T-SQL. SQLTeam have a lot of good examples. Use these to split your values and insert in to a table. Inner join your table to tblEvents.

    If this is SQL Server 2008 you can (and should) use table valued parameters.

  3. #3
    Join Date
    Jun 2010
    Posts
    28
    Quote Originally Posted by pootle flump View Post
    Look up split functions in T-SQL. SQLTeam have a lot of good examples. Use these to split your values and insert in to a table. Inner join your table to tblEvents.

    If this is SQL Server 2008 you can (and should) use table valued parameters.
    Many Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You'll find some split functions here too: Common Solutions for T-SQL Problems - Home
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Does it not bother anyone else that the second CaseID is truncated?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley View Post
    Does it not bother anyone else that the second CaseID is truncated?
    Not a jot.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the code doesn't work, the parameters don't make much difference. Once the OP gets the code working, they'll quickly discover and fix problems with the parameters.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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