multivalue para in store proc

  • I want to use a multi value parameter in RS

    so I create my proc as below

    CREATE PROCEDURE PR_ActClientWithMulti

    (@HACCProj varchar(10)= NULL,

     @Branch varchar(10)= NULL,

     @DivCode varchar(10) = NULL,

     @PrimZone varchar(10)= NULL)

    AS

    DECLARE @Multipara VARCHAR(8000)

    BEGIN

     

    SET @Multipara = 'SELECT CNo,CName,[Search Name],CFName,CMidName,CSurname,Title,

     

    CASE DOB

          WHEN '01/01/1753' THEN 'Null'

    ELSE DOB

    END AS DOB1,       

     

    CASE CConsent

          WHEN '0' THEN 'NO'

          WHEN '1' THEN 'Yes'

    END AS CConsent1,

     

     

    FROM VW_ActClient1

     

    WHERE

    (@HACCProj IS NULL OR HACCProj IN('+ @HACCProj + ')

    AND (@branch IS NULL OR Branch IN('+ @branch + ')

    AND (@DivCode IS NULL OR DivCode IN('+ @DivCode + ')

    AND (@PrimZone IS NULL OR PrimZone IN('+ @PrimZone + ')'

     

    execute (@Multipara)

     

    END

     

    Error message comes up that  I have “ Incorrect syntax near '01'.”

    I guess because I use CASE .

    How do I correct my proc?

     

    Thanks,

    Susan

     

  • Nope, it's the dynamic SQL. When you use dynamic SQL you have to turn all single-quotes within the string that you want evaluated into double-single-quotes (not double quotes). So your query should be:

    SET @Multipara = 'SELECT CNo,CName,[Search Name],CFName,CMidName,CSurname,Title,

    CASE DOB

    WHEN ''01/01/1753'' THEN ''Null''

    ELSE DOB

    END AS DOB1,

    CASE CConsent

    WHEN ''0'' THEN ''NO''

    WHEN ''1'' THEN ''Yes''

    END AS CConsent1,

    FROM VW_ActClient1

    WHERE

    (@HACCProj IS NULL OR HACCProj IN(''+ @HACCProj + '')

    AND (@branch IS NULL OR Branch IN(''+ @branch + '')

    AND (@DivCode IS NULL OR DivCode IN(''+ @DivCode + '')

    AND (@PrimZone IS NULL OR PrimZone IN(''+ @PrimZone + '')'

    Try that.

  • Thanks Aaron.

    however when I run it

    exec PR_ActClientWithMulti '80,81',null,null,null

     

    it doesn't give me any data only say the commands is completed succesfully.

    Do you think my Proc is wrong?

  • Yes, the SPROC is not quite right. The problem is with this part of the dynamic SQL :

    AND (@branch IS NULL OR Branch IN('+ @branch + ')

    AND (@DivCode IS NULL OR DivCode IN('+ @DivCode + ')

    AND (@PrimZone IS NULL OR PrimZone IN('+ @PrimZone + ')'

     

    As written, if any one of those variables is NULL, then the whole dynamic sql string becomes NULL (   'ABC' + null ==> null  ).

     

    Essentially, you are executing EXEC (null).

     

    Anytime you use dynamic SQL, always store the command to a variable as you have done, but PRINT it before EXEC. Once you see the command is correct, remove the PRINT and add the EXEC.

     

  • Here's an example:

    DECLARE @HACCProj varchar(8000), @branch varchar(8000), @DivCode varchar(8000), @PrimZone varchar(8000)

    SET @HACCProj = '80,81'

    DECLARE @Multipara varchar(8000)

    SET @Multipara = 'SELECT CNo,CName,[Search Name],CFName,CMidName,CSurname,Title,

    CASE DOB

          WHEN ''01/01/1753'' THEN ''Null''

    ELSE DOB

    END AS DOB1,       

    CASE CConsent

          WHEN ''0'' THEN ''NO''

          WHEN ''1'' THEN ''Yes''

    END AS CConsent1,

    FROM VW_ActClient1'

    IF Coalesce(@HACCProj, @branch, @DivCode, @PrimZone) IS NOT NULL

    BEGIN

      SET @Multipara = @Multipara + ' WHERE '

      IF @HACCProj IS NOT NULL

        SET @Multipara = @Multipara + 'IN(''+ @HACCProj + '')'

      IF @branch IS NOT NULL

        SET @Multipara = @Multipara + 'IN(''+ @branch + '')'

      IF @DivCode IS NOT NULL

        SET @Multipara = @Multipara + 'IN(''+ @DivCode + '')'

      IF @PrimZone IS NOT NULL

        SET @Multipara = @Multipara + 'IN(''+ @PrimZone + '')'

    END

    PRINT @Multipara

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply