0

Using SQL Server - 2014.

I have stored proc,

CREATE PROCEDURE getRequest(
        @Id INT
       ,@ApiNo NVARCHAR(100)
       ,@RContent VARCHAR(max) out)
AS
BEGIN
    IF((@ApiNo != ''))
    BEGIN
        DECLARE @name VARCHAR(100)
        DECLARE @class VARCHAR(100)
        SET @name = (SELECT name FROM Student WHERE id = @Id)
        SET @class = CASE
                WHEN (
                        @name LIKE '%First%'
                       )
                    THEN 'FR'
                WHEN (
                        @name LIKE '%Second%'
                       )
                    THEN '''FR'',''TR'''
                WHEN (
                        @name LIKE '%Fourthd%'
                       )
                    THEN '''FR'',''BM'',''TR'',''KK'''
                ELSE 'TM'
                END
            SELECT RContent FROM School where Class IN (@class) AND Id = @Id 
            print 'This is a test' + @class
    END
END;

In this I am setting @class using when case, and using IN to cheeking for multiple value. 

eg: SELECT RContent FROM School where Class IN (@class) AND Id = @Id

I have used print to see my @class variable set correctly. I am seeing following value as : This is a test'FR','BM','TR','KK' .

But Its not working. If I remove Class IN (@class) . It will work, while If I add this doesn't work as expected.

Somu
  • 59
  • 2
  • 10
  • No. Thats Not a case. Doesn't solve my problem. – Somu Apr 11 '16 at 15:05
  • The essential problem is not `CASE`, it's using `WHERE Class IN (@class)`, which is not an allowed construct. You can fill `@class` in any way you like, but you can't use it in an `IN`. – Jeroen Mostert Apr 11 '16 at 15:07
  • @JamesZ : I don't need to Parameterize. – Somu Apr 11 '16 at 15:07
  • your problem is that a varchar is not a list/array, so your IN only contains 1 value.. make @class a table variable and just insert the values you want.. then use the table var in your IN() – JamieD77 Apr 11 '16 at 15:07
  • @Somu That's what you're doing in your code. If you don't want to do that, just move your code into the where clause with proper and / or -parts instead of using a variable – James Z Apr 11 '16 at 15:09
  • @jamie : Thanks for response, I am new In DB. Kindly provide small example. If possible. I am sorry, if its too demanding. – Somu Apr 11 '16 at 15:10
  • @Somu It is the same problem. You can't pass a comma-delimited string as a multi-valued parameter to `IN`. It interprets the entire string as a single value, not a list of values like it would if the items were added to the `IN` clause literally. There's not a simple solution to what you are trying to do. The marked duplicate gives some options but they are not simple. – D Stanley Apr 11 '16 at 15:10
  • Just when I was about to post an answer.... anyway, D Stanley is correct - the IN() operator expects a comma separated list of arguments, but you have supplied it with a single argument that contains a comma separated list of values. – Zohar Peled Apr 11 '16 at 15:12
  • @Somu https://sqlstudies.com/2013/04/08/how-do-i-use-a-variable-in-an-in-clause/ – JamieD77 Apr 11 '16 at 15:26

0 Answers0