Categories: MSDN / DotNet / Java / Scripts / Linux / PHP Ask - La ask - La Answer

parameter in stored procedure with apostrophe to quotation marks

I'm trying to execute this stored procedure from a source code
CREATE PROCEDURE DB_RESTORE
@DBName varchar(60),
@BackName varchar(120),
@DataName varchar(60),
@DataFileName varchar(120),
@LogName varchar(60),
@LogFileName varchar(120)
AS
RESTORE DATABASE @DBName
FROM DISK = @BackName
WITH REPLACE, MOVE @DataName TO @DataFileName,
MOVE @LogName TO @LogFileName, STATS
GO

If I do it from query analyzer like this it works fine

The problem is when I try to execute it from the code source it takes Test1 between apostrophe like 'Test1' and that is not good.It works only if Test1 is between quotation marks.

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@DBName", OleDbType.VarChar, 60).Value = "Test1";
cmd.Parameters["@DBName"].Direction = ParameterDirection.Input;
cmd.ExecuteNonQuery();

if I trace it with SqlProfiler it looks like this during execution time:
exec[DB_RESTORE] 'Test1', 'F:\Users\Bianca\TEST.BAK', 'BD_Data', 'F:\USERS\DATABASE\BD_Data.MDF', 'BD_Log', 'F:\USERS\DATABASE\BD_Data.LDF'

How can I send Test1 to see it like "Test1" and not 'Test1'
[1282 byte] By [biap] at [2007-11-11 10:17:49]
# 1 Re: parameter in stored procedure with apostrophe to quotation marks
First of all, is Test1 a variable, or actual sample text?

Assuming it's actual text:
Replace each double quote " around Test1 with 3 double quotes """.

Instead of "Test1" You use """Test1""".

Surrounding the string with quotes simply indicates that is is a string. If you need the quotes inside the string, you need to escape each double quote with another double quote.

Another method that generally works is to include a single quote inside the quotes:

"'Test1'"

On the other hand, if Test1 is a variable name:

You need to surround the text value of the variable with quotes:
"'" & Test1 & "'"
// OR
"""" & Test1 & """"

Let me know if I misunderstood your question.

-Andrew
Andrew Cushen at 2007-11-11 23:43:24 >