SET Variable
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
Suthish NairPosted Feb 12, 2011, 1:18 AM
Ellen HuPosted Feb 11, 2011, 11:00 AM
ellen
Suthish NairPosted Feb 6, 2011, 5:45 AM
Ellen HuPosted Feb 5, 2011, 11:34 PM
Actually, I had similar try before, at that time it didn't work well, I thought this is not a proper approach. In fact, I had checked now it is something other error caused.
Thank you again for your help.
ellen
Manikavelu VelayuthamPosted Feb 4, 2011, 1:13 AM
declare @str nvarchar(max)
declare @oid bigint
set @oid = 1
set @str = 'select * from tablename where ID = '+ cast(@oid as nvarchar(max))
execute sp_executesql @str