January 10, 2009 at 3:42 am
hi,
how to use "apostrophe" in procedures
I have created below procedure
create procedure pro_name(@name varchar(50))
as
Select * from emp where empname=@name my procedure like..
execute pro_name ' giri's '
It shows error ...how can i access the my data from data table
Thanks
Dastagiri.D
January 10, 2009 at 4:11 am
Double up the apostrophes (character stuffing)
execute pro_name ' giri''s '
January 11, 2009 at 10:21 am
For easier maintenance & management, we replace ' with ´ when we store names in the DB.
Ex:
DECLARE @name varchar(100)
SET @name='GIRI''S'
SELECT @name,replace(@name,'''','´')
Therefore, we don't need to encode them when we fetch them to be displayed in a winform, webpage, javascript alert,...
January 11, 2009 at 11:26 am
There are some more special characters you need to double up when using them in T-sql or in a dynamic sql string.
If you use variables from you calling application (vb.net,..) there should be no problem at all.
e.g.
Dim objCon As ADODB.Connection
Dim objCom As ADODB.Command
Dim objParam As ADODB.Parameter
Dim objRS As ADODB.Recordset
Set objCon = New ADODB.Connection
Set objCom = New ADODB.Command
objCon.ConnectionString = "PROVIDER=SQLOLEDB.1;...."
'Opening the connection
objCon.Open objCon.ConnectionString
'assigning the command object parameters
With objCom
.CommandText = "dbo.pro_name" 'Name of the stored procedure
.CommandType = adCmdStoredProc 'Type : stored procedure
.ActiveConnection = objCon.ConnectionString
End With
'Create parameters
Set objParam = objCom.CreateParameter("@name", adVarChar, adParamInput, 50,"jones")
'Append the output parameters to command object
objCom.Parameters.Append objParam
'Store the result in a recordset
Set objRS = objCom.Execute
'Open the recordset
Do While Not objRS.EOF
For k = 0 To objRS.Fields.Count - 1
Debug.Print objRS(k).Name & ": " & objRS(k).Value
Next
Debug.Print "_____"
objRS.MoveNext
Loop
'Close the recordset
objRS.Close
'close connection
objCon.Close
'cleaning up
Set objCom = Nothing
Set objCon = Nothing
Set objParam = Nothing
Set objRS = Nothing
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 11, 2009 at 11:45 am
jmguazzo (1/11/2009)
For easier maintenance & management, we replace ' with ´ when we store names in the DB.
FYI, this is really NOT recommended for literals in the procs and triggers themselves. The closure distinct single quote, along with several other "alternate" quoting characters can throw the Scripter and SSMS query windows for a tailspin in certain situations. In fact, I have a customer that has about 20,000 lines of application vendor supplied SQL procs and triggers that cannot be automatically scripted because of this (requires manual intervention to fix every time).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 11, 2009 at 1:12 pm
I didn't mean that.
When we store the data in the DB, we convert ' to ´ in the C# code.
We almost never store any litteral in SP, Triggers,...
So when the value is transferred in triggers or else, the ´ is considered a character like any other.
We only work with Nvarchar because we must handle other than ascii and it has never been a problem for several years now.
JM.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply