Quotes problem in Query - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Quotes problem in Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-09 : 08:04:32

Asad Zaidi writes "I have a project in which the SQL Server communicates with the DB2 via a linked server. In the database of the project there is a query in a stored procedure (which fetches data from DB2)as follows:

********************start procedure ********************
CREATE procedure usp_ord_GetReferenceTypes
as
begin
declare @strsql varchar(200)

set @strsql = ' SELECT * FROM ' + dbo.VSCSRFL5() + ' WHERE CRUSGE = ''''O''''AND CRDFLG = ''''0'''' AND CRCLIE= ''''' +dbo.ordclientid() + ''''''

execute db2cexec @strsql,'VSCSRFL5_T',1000000
select crreft as reftype,crdesc as description from VSCSRFL5_T
end
GO
***************end procedure****************************

Now The query assigned to the variable at @strsql in the above procedure has a problem with the way the single quotes are added in the query.

Now if rewrite that query as follows then it works:

' SELECT * FROM ' + dbo.VSCSRFL5() + ' WHERE CRUSGE = ''''O''''AND CRDFLG = ''''0'''''

So above query works in the Stored PRocedure (1 single quote added at the end of query due to the change/modifiation in query)

However as soon as the remaining part of the query i.e.

AND CRCLIE= ''''' +dbo.ordclientid() + ''''''

is concatenated to the query it stops working and the variable @strSql gets a blank entry, so in the above procedure null or blank data gets passed to the db2cexec procedure called in the above stored procedure so nothing works at all.

Can someone please resolve this problem in constructing this query. The entire problem is the handling of the single quotes written OR is there another way to write this query."

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-09 : 08:47:39

You could use CHAR(39) - this returns a single quote - instead of the single quotes inside literals (and having to double AND re-double them)

Owais

Make it idiot proof and someone will make a better idiot

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-09 : 09:18:50

You're missing a + sign between your literals..

Plus you're doing dynamic sql...don't

unless you need to of course..

And

SELECT LEN('+CHAR(39)+'), LEN ('+''''+')

Well the second one is really 6....

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources