Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: How to pass a string of interger to a SP with an in() clause

    I need to pass a string to my sp but dont know how the EXEC string is below as well as the sp.

    --EXEC [spGetUserCompanyUsers] '1,2,3'
    ALTER PROCEDURE [dbo].[spGetUserCompanyUsers]
    @CompanyIds varchar(50)
    AS
    BEGIN

    SELECT [User].UserId
    , [User].UserDesc
    , Companies.CompanyId
    FROM [User]
    INNER JOIN UserCompany ON [User].UserId = UserCompany.UserId
    INNER JOIN Companies ON UserCompany.CompanyId = Companies.CompanyId
    WHERE Companies.[Name] IN(@CompanyIds)

    END

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    well it looks ok, except your IN needs to have the string deconstructed and convert to int
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    How do i do that?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What version of SQL Server are you on?
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2003
    Posts
    233
    SQL2005 is the version

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    easiest ways are to use the Split function in a CLR function

    another is
    SQL Tip: HowTo Convert Delimited String to Table
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    actually that example is pretty hideous

    i'd probably go for something like

    Code:
    create function ConvertStringToList
    (
    @string varchar(1000),
    @delimiter char
    )
    returns @rtn table (VAL int)
    as
    begin
    
    	declare @index int
    	set @index = CHARINDEX(@delimiter, @string)
    
    
    	IF @index >0
    	BEGIN
    		set @rtn = ConvertStringToList(SUBSTRING(@string, @index + 1, LEN(@string) - @index),@delimiter,@partiallist)
    		insert into @rtn values(SUBSTRING(@string, 1, @index - 1)
    	END
    	ELSE
    		insert into @rtn values(@string)
        return
    end
    how ever that was written on the fly and hasn't been tested
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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