SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: SQL IN Issue

  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL IN Issue

    Hi,

    Why does the below stored procedure not return any values??

    Yet this same query which I use in the stored proc does return values...
    Code:
    SELECT SetID FROM TestSets
    WHERE (UserID IN ('user1','user2'))
    Thanks.

    Code:
    ALTER PROCEDURE [dbo].[TestStoredProc]
    	-- Add the parameters for the stored procedure here
    	@UserID nvarchar(max)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @SetID varchar(50)
    
        -- Insert statements for procedure here
    	SET @SetID = (
    	SELECT SetID FROM TestSets
    	WHERE (UserID IN (@UserID)))
    
    
    	SELECT @SetID 
    
    END
    GO

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    I believe it's because you're comparing apples to oranges there. You're setting a varchar with a results set.

    Why can't you just have the select statement as the stored proc? Or to that extent, if that's all the stored proc does, why have it as a stored proc at all? There's not a decisive performance gain running it through a stored proc versus a straight select.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,653
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Protip: T-SQL variables are not just shorthand string concatenation.

    There are alot of solutions for this problem, best one I have seen takes XML as the argument since that is easily an array and then joins to a temp table. The worst ones use sp_exec to execute a sql string created in T-SQL.


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
  •