CODEWITHSUNDEEP
sqlsql-server-2005
selva
selva

Reputation: 809

Invalid column name when using sp_executesql

When I try to execute a dynamic query inside a stored procedure I'm getting an error.

My code is:

DECLARE @Query nvarchar(max)
DECLARE @AllowanceBadge nvarchar(20)
DECLARE @AllowFieldName nvarchar(50)
DECLARE @Amount Decimal
SET @AllowanceBadge  ='SIP0980'
SET @AllowFieldName ='xxxxx'
SET @Amount = 100

SET @Query = 'UPDATE tbl_PayrollTransaction SET '+ @AllowFieldName +' = '+convert(varchar,@Amount) + 'WHERE BadgeNumber = '+@AllowanceBadge
            EXEC SP_EXECUTESQL @Query

I'm getting following error

Msg 207, Level 16, State 1, Line 1
Invalid column name 'SIP0980'.

Tell me where I'm wrong.

Thanks

Upvotes: 2

Views: 2152

Answers (1)

Oleg Dok
Oleg Dok

Reputation: 21766

Need quotes around the SIP0890

DECLARE @Query nvarchar(max)
DECLARE @AllowanceBadge nvarchar(20)
DECLARE @AllowFieldName nvarchar(50)
DECLARE @Amount Decimal
SET @AllowanceBadge  ='SIP0980'
SET @AllowFieldName ='xxxxx'
SET @Amount = 100

SET @Query = 'UPDATE tbl_PayrollTransaction SET '+ @AllowFieldName +' = '+convert(varchar,@Amount) + 'WHERE BadgeNumber = '''+@AllowanceBadge+''''
            EXEC SP_EXECUTESQL @Query

Upvotes: 5

Related Questions