Reputation: 55
I have a stored procedure to change the caption of images at a time..my sp as follows:
ALTER Proc [dbo].[sp_RenameAll] @Id nvarchar(MAx), @Captionn nvarchar(20)
as
begin
DECLARE @List VARCHAR(MAX)
SELECT @List = @Id
EXEC(
'update
tbl_Images set Caption='+@Captionn+'
WHERE Serial IN (' + @List + ')'
)
end
But when i execute this stored procedure with values
EXEC @return_value = [dbo].[sp_RenameAll]
@Id = N'1,2,3',
@Captionn = N'Newcaption'
It is giving me rror as Invalid column 'Newcaption', If i change the input values as
EXEC @return_value = [dbo].[sp_RenameAll]
@Id = N'1,2,3',
@Captionn = N'''xxxx'''
It is working fine... what is wrong with my Stored procedure????
Upvotes: 0
Views: 41
Reputation: 43023
You need to change
EXEC(
'update
tbl_Images set Caption='+@Captionn+'
WHERE Serial IN (' + @List + ')'
)
to
EXEC(
'update
tbl_Images set Caption='''+@Captionn+'''
WHERE Serial IN (' + @List + ')'
)
to include quotes around your string value in the dynamic SQL.
Your current stored proc produced this dynamic query:
update
tbl_Images set Caption=Newcaption
WHERE Serial IN (1,2,3)
and hence SQL Server thinks Newcaption
is a column name. But you need
update
tbl_Images set Caption='Newcaption'
WHERE Serial IN (1,2,3)
Upvotes: 3