Dynamic sql stored procedure update query issue?

how to write dynamic update query in sql server
dynamic query in sql server with parameters
how to pass dynamic parameters to stored procedure in sql server
stored procedure with dynamic sql and string concatenation
dynamic sql with variables
set variable = exec dynamic sql
execute dynamic query in function sql server
dynamic where clause in sql stored procedure

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\'

Try;

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

Equal as;

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

Dynamic sql stored procedure update query issue?, Try; EXEC('UPDATE '+ @tablename + ' SET filepath = '''+ @pathonlocal + ''' + file_name + '''+'.'+''' + file_ext'). Equal as; UPDATE [YourTable]  In the case of dynamic SQL inside stored procedures, syntax errors cannot be caught before the query execution. Furthermore, stored procedures are more like functions, they are defined once and then can be called anywhere in the script. Therefore, if you want to update a stored procedure, you only have to update it at one place.

Try changing your statement to this:

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

Dynamic SQL in SQL Server, Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input Using dynamic SQL inside stored procedures. UPDATE Stored Procedure in SQL Server Example. In this example, we will show how to use the UPDATE Statement inside the Stored procedure. Please refer to Introduction to Stored Procedure article.

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]

SQL Server Dynamic SQL, This tutorial shows you how to use the SQL Server dynamic SQL to construct general purpose and flexible SQL statements. Home / SQL Server Stored Procedures / SQL Server Dynamic SQL To execute a dynamic SQL statement, you call the stored procedure sp_executesql as shown in It will issue the following error:. Executing dynamic SQL using sp_executesql. sp_executesql is an extended stored procedure that can be used to execute dynamic SQL statements in SQL Server. we need to pass the SQL statement and definition of the parameters used in the SQL statement and finally set the values to the parameters used in the query.

Building Dynamic SQL In a Stored Procedure, The first variable @EmpID is used as a parameter to the SQL Query and second for order by clause and doing so causes a column-referencing problem. "​How to Build and Execute Dynamic SQL in stored procedures". Dynamic SQL is SQL that is created and executed at run-time. It sounds complicated, but it really isn’t. Instead of having the statements typed directly into the stored procedure, the SQL statements are first built and defined in variables. The code in these variables is then executed.

Execute Dynamic SQL commands in SQL Server, One issue is the potential for SQL Injection where malicious code is inserted into the Dynamic SQL by writing a query with parameters Look into using dynamic SQL in your stored procedures by employing one of the three  I have a stored procedure using dynamic SQL to execute some commands at runtime, and use INSERT INTO statement to temporarily keep the output of parameterized executesql in a temporary table. I needed to modify some contents of the temporary table and limit the content at some point.

Dynamic SQL vs Stored Procedure – {coding}Sight, Stored procedures beat dynamic SQL in terms of performance. is an issue with your database logic, only your stored procedure needs to be modified. In the case of dynamic SQL inside stored procedures, syntax errors  It allows you to create more general purpose and flexible SQL statement because the full text of the SQL statements may be unknown at compilation. For example, you can use the dynamic SQL to create a stored procedure that queries data against a table whose name is not known until runtime.

Comments
  • 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.
  • 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.
  • 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.
  • @IvanG, Yes true, I left it for clarity.
  • @ViswanathanIyer I'm glad to help :)
  • 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'''