CODEWITHSUNDEEP
sql-server-2008
sk7730
sk7730

Reputation: 736

SQl Stuff not returning results

I have stored procedure which has below Sql query.

    SELECT  @WorkGrpIDforUser = STUFF((SELECT ',' +  convert (nvarchar(20),Work_grp_id )
      FROM WORK_GRP_USER 
     where emp_id in(@WorkTypeIdentity)
       FOR XML PATH(''), TYPE
       ).value('.', 'NVARCHAR(MAX)') 
      ,1,1,'')

I am passing (@WorkTypeIdentity) values as 'ABC','HI' from my application. This query not returning any results. But If i put value 'ABC','HI' in this query, it is giving results.

I am not getting what i am missing.Can anyone please suggest.

Upvotes: 0

Views: 276

Answers (1)

GarethD
GarethD

Reputation: 69819

The problem is nothing to do with STUFF. You cannot parameterise the IN clause in this way. What you end up running is something like:

emp_id in(''ABC', 'HI'')

i.e. you still are only checking emp_id for a single value. You have a number of options, the first (and preferable) is to use table-valued parameters.

The first step in this is to create your type:

CREATE TYPE dbo.ListOfString AS TABLE (Value VARCHAR(MAX));

Then you can pass this type as a parameter:

DECLARE @Values dbo.ListOfString;
INSERT @Values VALUES ('HI'), ('ABC');
...
WHERE emp_id IN (SELECT Value FROM @Values)

Or pass to your procedure:

CREATE PROCEDURE dbo.SomeProcedure @Strings dbo.ListOfString READONLY
AS
BEGIN    
    ....
END

The next option is to use LIKE:

WHERE   ',' + @WorkTypeIdentity + ',' LIKE '%,' + emp_id + ',%';

Finally, you can use some other string splitting method to split your parameter before comparison. For further reading on this I suggest you read:

To summarise these articles, either use a CLR string splitting function, or table valued parameters, should you create a string splitting function, your SQL Will be something like:

WHERE emp_id IN (SELECT Value FROM dbo.Split(@WorkTypeIdentity))

Upvotes: 1

Related Questions