Dynamic sql stored procedure update query issue?

Question

I've written the below code to set filepath column in my table as 'F:\DataMigration\Wise\DELTA_20121008\Attachments\SR\SR_1.txt'

where SR_1 is file_name column
      .txt is file_ext column from my table.

but after executing following procedure, I'm getting filepath column in table as

'F:\DataMigration\Wise\DELTA_20121008\Attachments\file_name.file_ext'

means It's treating column names as string, how i can make it as column so it will use values in that column.

alter procedure [dbo].[WISEMissingAttachmentReportGenerator]
(
@tablename varchar(255), @pathonlocal nvarchar(255)
)
as
begin
--step 1
exec dbo.proc_alter_table @tablename
--step 2
EXEC ('update '+ @tablename +
' set filepath = '''+ @pathonlocal + ' file_name'+'.'+'file_ext''')
EXEC('Select * from '+@tablename)
end

exec [dbo].[WISEMissingAttachmentReportGenerator] [WISE.Non_VP_Service_Request_Attachments_File_Check_Analysis],
N'F:\DataMigration\Wise\DELTA_20121008\Attachments\SR\'

Indi · Accepted Answer · 2012-12-06 09:53:19Z

1

Try;

EXEC('UPDATE '+ @tablename +
' SET filepath = '''+ @pathonlocal + ''' + file_name + '''+'.'+''' + file_ext')

Equal as;

UPDATE [YourTable] SET filepath = 'YourPath' + file_name + '.' + file_ext

answered Dec 6, 2012 at 9:53

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vishwanath Dalvi Over a year ago

Thanks man, I always get confused while using single quotes with Dynamic sql, can you tell me why three continuous single quotes before @pathonlocal variable.

Indi Over a year ago

coz @pathonlocal is a string which has just the value. So you need it enclosed with 'your_string'. This is valid for any string type variable. Out of 3 ''' quotation marks, ' for ending 'SET filepath = ' and from other 2 one as **escape character** to the other.

Ivan Golović Over a year ago

Concatenation of a dot is not needed, it can just be included in a string, instead of '''+'.'+''' in file_name + '''+'.'+''' + file_ext it can just be file_name + ''.'' + file_ext, it will give a correct output, it's more simple this way and it saves you one concatenation operation.

Indi Over a year ago

@IvanG, Yes true, I left it for clarity.

Ivan Golović · Accepted Answer · 2012-12-06 09:50:34Z

1

Try changing your statement to this:

EXEC ('update '+ @tablename +
' set filepath = '''+ @pathonlocal + ''' + file_name + ''.'' + file_ext')

answered Dec 6, 2012 at 9:50

Ivan Golović

8,8223 gold badges27 silver badges31 bronze badges

1 Comment

Ivan Golović Over a year ago

To write single quotes inside literal string you have to double them, so you get subsequent 3 single quotes when the inner quotes are closed in front of the outer closing quote. Try this: SELECT 'What''s your name? My name is ''Joe''...', If there weren't for the ... on the end it would look as if there are 3 subsequent single quotes at the end - SELECT 'What''s your name? My name is ''Joe'''

Shaffiulla Khan · Accepted Answer · 2016-03-19 09:01:20Z

0

declare @tblnm varchar(20) = 'test'
declare @upda varchar(20) = 'update '
declare @set varchar(25) = ' set'
declare @id varchar(25) = ' id'
declare @cmd varchar(1000)

set @cmd = @upda + @tblnm + @set + @id + '=7'
exec(@cmd)

SAMPLE SQL UPDATE QUERY - FOR BUILDING TABLENAME DYNAMICALLY  
EXECUTED GUYS - THIS IS CALL JUGAAAAAAAAAD [NO NEED TO GET INTO ''' STUFF]

edited Mar 19, 2016 at 9:01

Shaffiulla Khan

10.4k16 gold badges58 silver badges54 bronze badges

answered Mar 19, 2016 at 8:54

JUGADU

1

Collectives™ on Stack Overflow

Dynamic sql stored procedure update query issue?

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related