Inserting a single quote using stored procedures

Question

Inserting a single quote using stored procedures

jaybmehta

SSChasing Mays

Points: 637
More actions
November 7, 2002 at 12:07 am

#79254

Hi All.
I have a parameter as varchar coming into the procedure. This parameter can have single quotes to be inserted into the column of the table. I can do this by SET QUOTED_IDENTIFIER ON from the page and insert into the table, but i am not able to figure out how do i do it with a paramter which comes to the stored proc.
Please i need some nice help from u gurus.
Bye
Jay

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

paulhumphris Hall of Fame Points: 3496 More actions · Answer 1

You should be able to insert a varchar containing a single quote into a table field without any problems. However, if you are concatenating the varchar param into a dynamic SQL statement you need to escape the single quote character.

Try using the replace command as follows:

set @param = replace(@param, char(39), char(39) + char(39))

Antares686 SSC Guru Points: 125444 More actions · Answer 2

If coming from a client app then have the app do a replace of the ' with '' (two single quotes).

VB

REPLACE(variablename,"'", "''")

C++

cStringVar.Replace("'","''")

Another for SQL

set @param = replace(@param, '''', '''''')

(Note there you have to double the single in the string area then double the doubled version for it to replace right but I am assuming it is a client app since you mentioned using SET QUOTED_IDENTIFIER ON.)

or what ever is specific to your programming language and your variable types

jaybmehta SSChasing Mays Points: 637 More actions · Answer 3

quote:
If coming from a client app then have the app do a replace of the ' with '' (two single quotes).
VB
REPLACE(variablename,"'", "''")
C++
cStringVar.Replace("'","''")
Another for SQL
set @param = replace(@param, '''', '''''')
(Note there you have to double the single in the string area then double the doubled version for it to replace right but I am assuming it is a client app since you mentioned using SET QUOTED_IDENTIFIER ON.)
or what ever is specific to your programming language and your variable types

Thnks , i think thios should help, but then it will req changes in many places, in the code of my developers team.

I will let u know if this works out propperly

Thnks

Jay

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 4

If you are using parameters correctly from your client, i.e parameters of the command object.

Then no replacing is needed as the text is handled correctly. it is only if you are using adhoc sql i.e connction.execute "insert into ..." then you will need to so the replacing.

You quite often find that people replace when there is no need. this results in '' being stored or returned to the client.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Is is the preferred method to use Command Object of adCmdStoredProc instead of a .Execute on the Connection object, the .Open of a Recordset Object or as adCmdText in a Command Object. This a much cleaner interface and Simon is right you will not have to perform the replace. The only issue with it though is that it returns the recordset as adOpenStatic and acts as a Forward-Only recordset, so you cannot move previous. See KB Article here http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306388 and here http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306385

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 6

You should never, in my experience, need to use server side cursors. This is reinforced with .net where everything is client side. With client side recordsets you can do what you want with them, forward, back, left, right, upside down

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

sjcsystems Ten Centuries Points: 1347 More actions · Answer 7

OK Simon, then how do you code things like loops of rows in a cursor where you need an action to occur on each row, rather than an aggregated result: for example, calling a COM object to send the row to an external system, or just simple things like coping with multi-row updates in update triggers. Perhaps I'm missing a point, and I know what the purists say, but there is a difference between writing totally correct SQL and writing code that other people are going to understand. I hope you can enlighten me on some of these gaps in my knowledge? In the case of .net, isn't that expecting to have a middleware layer to do this data manipulation, rather than downloading masses of data to each client?

Antares686 SSC Guru Points: 125444 More actions · Answer 8

And forceing a lot of rows down to the client can have side effects on memory resources. Not saying Client Side cursor are bad and again they are preferred, but preferred is not always practical. My other statment was a headsup.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 9

When I refer to server side cursor I mean a client side piece of code that is using a server side cursor an looping though the data. i.e. a recordset in server side mode.

This requires the setting up of a cursor on the server, requires a call to the server for each chunk of data, by default each row not many people change this.

A server side cursor is only of benefit if you are not going to process each row in the resultset. However in that situation change your code to only return the rows you want and use a client side cursor.

As for returning lots of data to the client, yeh I agree you, have to decide whether this is needed, whether you can do the work on the server. weigh up the pros and cons. This will get expenentionally harder to assess with Yukon

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 10

Then you are talkign abou doing the code lik so

rs.CursorLocation = adUseServer

but when using adCmdStoredProcedure as the CommandType for the command object there is a know bug that the cursor will only act as a forward only. Back to the two links I posted.

It was mentioned by another person to cloan the recordset into one with a CursorLocation of adUseClient, but again you push all the data down, it may not fit in memory.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 11

I read those articles and the say the problem is when you are using server side cursors but not that it forces server side cursors.

My app only uses SPs and we have no server side cursors.

My point is if you need the data on the middle tier then, use a client side cursor.

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 12

So I know we are are the same page (or heck, even the same book at this point). Simon will you post an example of what you are doing so I can be sure that is not what I am talking about?