How can I escape SINGLE QUOTE in insert statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I escape SINGLE QUOTE in insert statement

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-12-11 : 07:18:24

I have one table like this

CREATE TABLE #Delete_Master2
(Table_Name2 VARCHAR(100),
Condition varchar(1000) ,
Date_Col_Name2 VARCHAR(100),
LastRunDate DATETIME
)

Insert into #Delete_Master2 values('SAP_ORDER','SAP_ORDER_SYSTE_STATU_CHR = CLSD','KC_SAP_ORDER_CHANG_ON_DATE_DT',@LastRunDate )
In condition i want to insert the values
SAP_ORDER_SYSTE_STATU_CHR = 'CLSD'
when I tried above syntax in insert statement it gives me and error because SINGLE QUOTE (')finishes already ,How can I eliminate SINGLE QUOTE

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-12-11 : 07:41:26

You could use a parametrized query.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-11 : 08:22:46

another ' escapes the first


SAP_ORDER_SYSTE_STATU_CHR = ''CLSD''

But check out the QUOTENAME function rather.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-12-11 : 08:45:06

in below SP i m facing problem at INSERT statement
i want to insert 'CLSD' using start and end single quote

create Procedure [dbo].[DataPurge1](@LastRunDate as Varchar(100) )
AS
begin

-----------------------------------------------------------------------------------------
--For #Delete_Master2
-----------------------------------------------------------------------------------------
--Declare @LastRunDate as varchar(100)
--set @LastRunDate='25-may-2009'

IF OBJECT_ID(N'tempdb..#Delete_Master2', N'U') IS NOT NULL
DROP TABLE #Delete_Master2

CREATE TABLE #Delete_Master2
(Table_Name2 VARCHAR(100),
Condition varchar(1000) ,
Date_Col_Name2 VARCHAR(100),
LastRunDate DATETIME
)

--delete from #Delete_Master2
-----------------------------------------------------------------------------------------
Insert into #Delete_Master2 values('SAP_ORDER','SAP_ORDER_SYSTE_STATU_CHR = CLSD','SAP_ORDER_CHANG_ON_DATE_DT',@LastRunDate )
-----------------------------------------------------------------------------------------
-- Delete from KC_SAP_ORDER where KC_SAP_ORDER_CHANG_ON_DATE_DT<='2009-05-25'AND [KC_SAP_ORDER_SYSTE_STATU_CHR] = 'CLSD')
-----------------------------------------------------------------------------------------
select * from #Delete_Master2
-----------------------------------------------------------------------------------------

Declare @SQL_2 nVarChar(max)
Declare @RowCount2 int

-- declare the cursor
DECLARE C2 CURSOR FOR
SELECT Table_Name2,Condition,Date_Col_Name2
FROM #Delete_Master2

-- declare what to put the cursor variable into
DECLARE @Table_Name2 varchar(100)
DECLARE @Date_Col_Name2 varchar(100)
declare @Condition varchar(1000)

-- Open cursor
OPEN C2
-- Go to first cursor item (make sure fields same order)
FETCH NEXT FROM C2 INTO @Table_Name2 ,@Condition, @Date_Col_Name2

-- Loop through each item (loops between BEGIN and END)
WHILE @@Fetch_Status=0

BEGIN
-- do whatever code you want with the variables here
SELECT @SQL_2 =' Delete from ' + @Table_Name2 + ' where ' + @Condition + ' AND ' + @Date_Col_Name2 + '<='+ QUOTENAME(CONVERT(VARCHAR(20), @LastRunDate, 121), '''')

--exec sp_executesql @SQL
--exec @SQL

print @SQL_2

-- go to next cursor item
FETCH NEXT FROM C2 INTO @Table_Name2 ,@Condition,@Date_Col_Name2

END

-- clean up or if you run this again you'll get errors
Close C2
Deallocate C2

-----------------------------------------------------------------------------------------
end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 08:53:42

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources