SQL Search with quotation marks

I have a sp I call from a VS C# program. All works fine until a get to a vendor who has a quotation mark in their ID. There is a vendor in the database whose vendorid is TOURNEDO'S. How do a change this script to find that vendor?

DECLARE @VENDORID CHAR(15)
SET @VENDORID = 'TOURNEDO''S'
SELECT PM.VADDCDPR,PM.VADCDTRO,PM.PYMTRMID,PA.TAXSCHID,PA.SHIPMTHD,
PM.TEN99TYPE,PM.TEN99BOXNUMBER,COALESCE(PT.DUEDTDS,0),COALESCE(PT.DISCDTDS,0),
COALESCE(PT.DSCPCTAM,0)
FROM PM00200 PM
INNER JOIN PM00300 PA ON PM.VENDORID=PA.VENDORID AND PM.VADCDTRO=PA.ADRSCODE
LEFT OUTER JOIN SY03300 PT ON PM.PYMTRMID = PT.PYMTRMID
WHERE PM.VENDORID = @VENDORID
rwheeler23Asked:
Who is Participating?
 
keyuCommented:
its nvarchar  or varchar ?

as you mentioned vchare and its taking as parameter nvarchar

also there is some space while passing it as parameter...

revove it using rtrim option


DEclare @tablsql as nvarchar(max)

set @tablsql='TOURNEDO''S     '

print @tablsql+'[[[['

print rtrim(@tablsql)+']]]]'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What error message is being generated?  Your code as written should work.

If you are trying to pass the string TOURNEDO'S as a SP parameter, you may have to convert the ' to '' in your C# front-end, then pass it as a parameter.
0
 
rwheeler23Author Commented:
I ran SQL profiler to see what was sent down. This is what was sent down.

exec rbsPMTransaction @INTERID=N'PEF',@BCHSOURC=N'PM_Trxent',@BACHNUMB=N'BOA-2012-09-001',@VCHNUMWK=N'0246254          ',@CNTRLTYP=0,@DOCNUMBR=N'TXN00022902',@DOCTYPE=4,@TRXDSCRN=N'',@DOCDATE='2012-05-25 00:00:00:000',@PSTGDATE='2012-05-25 00:00:00:000',@PORDNUMBR=N'',@VENDORID=N'TOURNEDO''S     ',@DOCAMNT=1082.0400

My SP insert records into a table which does not allow nulls. I then copied this into SQL and it returns no records. This is the Select statement is used to gather data from the vendor master record. It returns no records because variable @VENDORID contains 'TOURNEDO''S'. For other vendors it works fine. From what I can tell the right value is being passed down. It is simply the SQL statement that is failing.

SELECT @VADDCDPR=PM.VADDCDPR,@VADCDTRO=PM.VADCDTRO,@PYMTRMID=PM.PYMTRMID,@TAXSCHID=PA.TAXSCHID,@SHIPMTHD=PA.SHIPMTHD,
@TEN99TYPE=PM.TEN99TYPE,@TEN99BOXNUMBER=PM.TEN99BOXNUMBER,@DUEDTDS=COALESCE(PT.DUEDTDS,0),@DISCDTDS=COALESCE(PT.DISCDTDS,0),
@DSCPCTAM=COALESCE(PT.DSCPCTAM,0)
FROM PM00200 PM
INNER JOIN PM00300 PA ON PM.VENDORID=PA.VENDORID AND PM.VADCDTRO=PA.ADRSCODE
LEFT OUTER JOIN SY03300 PT ON PM.PYMTRMID = PT.PYMTRMID
WHERE PM.VENDORID = @VENDORID
0
 
rwheeler23Author Commented:
Thanks for your help with this. It turns out I was lied to on this one. I was told there would always be at least one record in the PM00300 table for every record in the PM00200 table. Well I just happened to land on one where that was not the case. So no records were being returned.

I has since upgraded my script to a left outer join and fill in missing valuies with blanks.
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.

All Courses

From novice to tech pro — start learning today.