February 6, 2004 at 9:02 am
I have an insert like the following :
INSERT INTO Tbl_temp_stat VALUES (@county,460,50,0 ) which translates to
INSERT INTO TblJis_temp_stat VALUES (' St.Mary's County',460,50,0 ) which gives me an error
because of the quote . I am unable to use a function like REPLACE to substitute the single quote with 2 single quotes. Gives me an error again .
Any ideas will be appreciated.
February 6, 2004 at 9:17 am
declare @cmd varchar(255)
declare @county varchar(255)
select @county = "St.Mary's County"
select @cmd = 'INSERT INTO Tbl_temp_stat VALUES ("' + @county + '",460,50,0 )'
exec (@cmd)
February 6, 2004 at 9:21 am
It still gives me the following error :
Server: Msg 207, Level 16, State 3, Line 4
Invalid column name 'St.Mary's County'.
February 6, 2004 at 9:27 am
Add "set quoted_identifier off" as first line.
February 6, 2004 at 9:45 am
This works fine from Query Analyzer but in my Stored procedure it still gives the same error as before. I think the EXEC does not recognize the "set quoted_identifier off" from the previous line. Is there any other way I can do this ?
February 6, 2004 at 9:59 am
I don't know what's your problem,
This works for me :
Declare @data varchar(20)
set @DATA = 'St.Mary''s County'
insert into test VALUES (@data)
Are you using dynamic sql ? --> take a look at QUOTENAME
* Noel
February 6, 2004 at 10:34 am
I am using dynamic SQL, that is why I cannot change the variable as you have done for @DATA.
But QUOTENAME worked great ! Thanku.
February 6, 2004 at 1:12 pm
There is just one thing to watch out for when using quotename(). The input parameter is a nvarchar(129)
On SQL 2k you can use
CREATE FUNCTION quotestring(@str nvarchar(1998)) RETURNS nvarchar(4000) AS
BEGIN
DECLARE @ret nvarchar(4000),
@sq char(1)
SELECT @sq = ''''
SELECT @ret = replace(@str, @sq, @sq + @sq)
RETURN(@sq + @ret + @sq)
END
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 6, 2004 at 3:49 pm
When & where possible, I prefer to replace the pesky quote ASCII code CHAR(39),
with CHAR(146) or CHAR(145) and put an end to the madness of chasing and reacting
to the many different problems associated with it.
February 6, 2004 at 6:46 pm
Try something like this....
SET @var = REPLACE(@strWord,'''','''''')
Then build the rest of the string.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply