Help with EXEC String Please 1

[shiggyshag]

HI Im using the below

ALTER PROCEDURE dbo.StoredProcedure1
(@SiteID int,
@PlotNumber int)
AS

Declare @tablename VarChar(50)
Declare @SQLCommand VarChar(200)

set @tablename = (SELECT TableName FROM dbo.tSite WHERE (SiteID = @SiteID))

SET @SQLCommand = 'UPDATE dbo.' + rtrim(@tablename) + ' SET Status = N''Hold'' WHERE (dbo.PlotNumber =' + @PlotNumber + ')'

EXEC(@SQLCommand)


and the result is

Syntax error converting the varchar value 'UPDATE dbo.tNewark SET Status = N'Hold' WHERE (dbo.PlotNumber =' to a column of data type int.


What is casuing this problem?

Any Help would be great

 

[PatrickIRL]

Just a little syntax error. Remove the single quotes around the @PlotNumber

SET @SQLCommand = 'UPDATE dbo.' + rtrim(@tablename) + ' SET Status = N''Hold'' WHERE (dbo.PlotNumber = + @PlotNumber + )'

Patrick

 

[shiggyshag]

Hi Thank you but now im getting

Must declare the variable '@PlotNumber'.

Cheers

 

[Katy44]

Try converting your @PlotNumber (in your original script) to a varchar using the convert function.
I've had similar problems and I think it's caused when you use the plus sign with an int - it doesn't know whether to concatenate or add!
Hope this helps

 

[shiggyshag]

Thank you it works great