• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

How to insert single quote values using dynamic sql during import process

Below is how i have my dynamic sql: I am inserting the values into the field through the import process. Out of 1000 records in a file, for 300 of them i had apostrophe. Below sql is working only if the value doesn't have apostrophe.

declare @SQL as nvarchar(1000)
set @SQL = N'exec USP_TABLE_ADD @ID=''' + CAST(@ID as nvarchar(100)) + ''', @VALUE=''' + @VAL + ''''
exec sp_executesql @SQL

Any help will be greatly appreciated!

Thanks,
Sam



0
Giftcard
Asked:
Giftcard
  • 2
1 Solution
 
dougaugCommented:
Try this:

SET QUOTED_IDENTIFIER OFF

declare @SQL as nvarchar(1000)
set @SQL = N'exec USP_TABLE_ADD @ID='"' + CAST(@ID as nvarchar(100)) + '"', @VALUE='"' + @VAL + '"''
exec sp_executesql @SQL

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You need to use REPLACE function like this
declare @SQL nvarchar(1000)
DECLARE @id INT;
DECLARE @val NVARCHAR(100);

SET @id = 10
SET @val = 'a''bc'
set @SQL = N'exec USP_TABLE_ADD @ID=''' + CAST(@ID as nvarchar(100)) + ''', CAST(@VALUE as nvarchar(100))=''' + REPLACE(@VAL, '''' , '''''') + ''''
PRINT @sql

exec sp_executesql @SQL

-- And your code would be

declare @SQL nvarchar(1000)
set @SQL = N'exec USP_TABLE_ADD @ID=''' + CAST(@ID as nvarchar(100)) + ''', CAST(@VALUE as nvarchar(100))=''' + REPLACE(@VAL, '''' , '''''') + ''''
exec sp_executesql @SQL

Open in new window

0
 
dougaugCommented:
Follow another version of my previous code:

SET QUOTED_IDENTIFIER OFF

declare @SQL as nvarchar(1000)
set @SQL = N'exec USP_TABLE_ADD @ID="' + CAST(@ID as nvarchar(100)) + '", @VALUE="' + @VAL + '"'
exec sp_executesql @SQL

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now