sqlserver apostrophe problem.

[gerrybrennan]

Hi I have a stored procedure schemacreate.sql and below is a snippet. I am having problems with creating the below procedure. More specifically and TransType = "SI" Can any one help me with this. Thank you in advance. G

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER OFF

IF NOT EXISTS (select * from sysobjects where id = object_id(N'[dbo].[TransCheckifCurrentExists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[TransCheckifCurrentExists]

as
declare @SQLstr nvarchar(1000)
declare @value int;

set @SQLstr = ''select @value=count(*) from Trans where TransState = 1 and TransType = "SI" ''

exec sp_executesql @SQLstr, N''@value int out'', @value out
return

'
END

[Ashfield]

Double quotes are not valid to enclose literals, you need single quotes. I would suugest before executing your sql string you print it (at least for debug) as it soon shows any syntax errors.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[gerrybrennan]

Hi Bob, Thank you for your help. When I use single quotes i get an error Line 16: Incorrect syntax near 'SI'. G

[Niladri_Biswas]

Try and TransType =

''SI''

:)

Niladri Biswas

[Nilesh Kuvar]

Hello, Generally here, apostrophe is considered as escape sequence character You may execute as TransType = ''SI'' This may solve you problem :thumbsup:

[Blue_Boy]

Use CHAR(39) for single apostrophe.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com

[gerrybrennan]

Eventually got it ''''SI'''' Solved the problem. Many Thanks G.

[Blue_Boy]

Good,but when you have complex query and in which you use multiple times single apostrophe then you can become confused by writing much '''' in query. Regards.

---

I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com