Quotation Marks and Dynamic SQL Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Quotation Marks and Dynamic SQL Statement

Author

Topic

DNB
Starting Member

7 Posts

Posted - 2002-03-22 : 14:13:31

I am creating a dynamic insert statement within a stored procedure. It looks like this (with 'plus' signs after @SQL, which I don't why this doesn't show them):

				
-- create dynamic insert
Select @SQL = 'Insert '
Select @SQL = @SQL + @DestinationDatabase 
Select @SQL = @SQL + '.t_ItemCategory Values ("'
Select @SQL = @SQL + RTrim(LTrim(@CurCategoryCode)) + '", "' + RTrim 
  (LTrim(@CurItemCategoryDesc)) + '", "' + RTrim(LTrim
  (@CurItemOther)) + '")'
--Print (@SQL)
Exec ( @SQL)

However, the problem I am having is that some of the passed in parameters have double quotation marks within them:

Insert RPMA1Archive.dbo.t_ItemCategory Values ("240001", "Patterned concrete mix 3.5"", "")

Thus I get the error message:

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'Patterned concrete mix 3.5", ")'.

Does anyone know how to handle this within T-SQL?

Thanks!

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-22 : 14:22:04

look at quotename()

setBasedIsTheTruepath
<O>

DNB
Starting Member

7 Posts

Posted - 2002-03-22 : 14:27:34

Thanks! Let's pretend I didn't ask that one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources