CODEWITHSUNDEEP
sql-server
user3860465
user3860465

Reputation: 55

Stored procedure giving me error

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

Answers (1)

Szymon
Szymon

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

Related Questions