Hi,
I have problem on setting variable with integer values,
SET @InitSqlStatement = 'INSERT INTO #SearchResults
SELECT P.zVolunteerProjectID
FROM tblVolunteerProject P
INNER JOIN tblVolunteerProjectZipCode Z
ON P.zVolunteerProjectID = Z.zVolunteerProjectID AND P.zActive = 1 AND Z.zActive = 1
WHERE P.zVolunteerProjectID IN ( SELECT zProjectID
FROM tblvolunteerprojectpromote
WHERE zActive = 1 )
AND P. zVolunteerProviderID = 1
AND P.zVolunteerParticipationTypeID = 2
AND P.zAffliateID =' + CAST(@AffiliateID AS NCHAR(4))
if I use "EXECUTE sp_executesql @InitSqlStatement ", also use "select @InitSqlStatement" to check what is really going to be excuted, get the following:
INSERT INTO #SearchResults
SELECT P.zVolunteerProjectID
FROM tblVolunteerProject P
INNER JOIN tblVolunteerProjectZipCode Z
ON P.zVolunteerProjectID = Z.zVolunteerProjectID AND P.zActive = 1 AND Z.zActive = 1
WHERE P.zVolunteerProjectID IN ( SELECT zProjectID
FROM tblvolunteerprojectpromote WHERE zActive = 1 )
AND P. zVolunteerProviderID = 1 AND P.zVolunteerParticipationTypeID = 2
There is no P.zAffliateID = 'someValue' at all
If I change the last line to AND P.zAffliateID =' + @AffiliateID , will get following error during the excution
Msg 245, Level 16, State 1, Procedure spVolunteerProjects_Search_CorpSponsered, Line 57
Conversion failed when converting the varchar value 'INSERT INTO #SearchResults
SELECT P.zVolunteerProjectID
FROM tblVolunteerProject P
INNER JOIN tblVolunteerProjectZipCode Z
ON P.zVolunteerProjectID = Z.zVolunteerProjectID AND P.zActive = 1 AND Z.zActive = 1
WHERE P.zVolunteerProjectID IN ( SELECT zProjectID
FROM tblvolunteerprojectpromote
WHERE zActive = 1 )
AND P. zVolunteerProviderID = 1
AND P.zVolunteerParticipationTypeID = 2
AND P.zAffliateID =' to data type int.
Of cause I can put the P.zAffliateID condition at somewhre else, but I am thinking there must a way to set variable with integer values. If you happen to know, would you please share with me?
Thank you for your time,
ellen