escaping - Escape Character in SQL Server

I want to use quotation with escape character. How can I do?

I have received error in SQL Server

Unclosed quotation mark after the
character string.

I am writing sql query in a varchar variable but I have received that error:

Unclosed quotation mark after the
character string.

I want to use quotation mark as escape char.

Thanks in advance

Answer

If you're concatenating SQL into a VARCHAR to execute (i.e. dynamic SQL), then I'd recommend parameterising the SQL. This has the benefit of helping guard against SQL injection plus means you don't have to worry about escaping quotes like this (which you do by doubling up the quotes).

e.g. instead of doing

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = ''AAA'''
EXECUTE(@SQL)

try this:

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = @Field1'
EXECUTE sp_executesql @SQL, N'@Field1 VARCHAR(10)', 'AAA'