June 28, 2006 at 7:26 pm
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
June 28, 2006 at 11:38 pm
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.
June 28, 2006 at 11:55 pm
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?
June 30, 2006 at 10:11 am
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.
June 30, 2006 at 10:20 am
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