SQL IN Issue

[k09]

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...

SELECT SetID FROM TestSets
WHERE (UserID IN ('user1','user2'))

Thanks.

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

[DaveMaxwell]

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.

[wwb_99]

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.