How do i insert or update a record based on data whicha has a apostohphe in a dynamic sql?-CodePudding

Here the @Data has a value with apostophe(')s . how do i update or insert a data based on the data value which is having apostophe in a dynamic sql

suppose @data has one value abc and another value abc's it throwing error for the second one

SET @SQL = ' Update ' @ProcessCode '_abc SET IS_IGNORING = 1 where Column_Name = ''' @Column_Name ''' and [DATA] = ''' @Data ''' and Table_name = ''' @Table_Name ''''

Generally what i found is a manual process of adding one more apostophe but i am not really sure how to use that in a dynamic sql where not all data in the table is same, few of the data records has got this type of apostophe(')

CodePudding user response：

Parameterized your query using sp_executesql

Example:

SET @SQL = 'Update '   @ProcessCode   '_abc ' 
           'SET   IS_IGNORING = 1 ' 
           'where Column_Name = @Column_Name ' 
           'and   [DATA]      = @Data ' 
           'and   Table_name  = @Table_Name ' 

EXEC sp_executesql @SQL, 
                   N'@Column_Name varchar(100), @Data varchar(100), @Table_Name varchar(100)',
                   @Column_Name, @Data, @Table_Name

Do read up more on dynamic query and SQL Injection

CodePudding user response：

You might find convenient to use parameterized queries, so you can replace static values with placeholders and then bind values to those placeholders before executing the query. It has certain advantages like better performance and helps to avoid SQL-injection attacks.

More info here: https://techcommunity.microsoft.com/t5/sql-server-blog/how-and-why-to-use-parameterized-queries/ba-p/383483