Get Away From Confusing Code

  • Comments posted to this topic are about the content posted at

    Watch my free SQL Server Tutorials at:
    Blog Author of:
    DBA Rant –

    Minion Maintenance is FREE:

  • Thanx.

    I like this approach. Very simple, but I didn't even think in this way.

  • Nice simple idea that make a lot of sense - thanks

  • Great "thinking outside the box" Sean - I know that I personally hate parantheses as much as I hate the single/double quotes - especially in really lengthy expressions...going to try that out with ascii as to think of a really meaningful variable name though - I knew that 'SQ' was single quote but I'm so used to looking at phrases with SQL in it that I had to keep reminding myself that this was not 'truncated SQL'!!

    **ASCII stupid question, get a stupid ANSI !!!**

  • Great approach and very easy to implement - good job!


  • Such a simple idea. Gotta love it.

    SuccessWare Software

  • This is a webby site with ASCII information.

    Thanks and have a great day.


  • Good article, but it makes me yearn all the more for database-global constants (they don't have them in 2000, I wonder if they're in 2005...)


  • I like the idea of using variables instead of the quotes.  But not the part about the ASCII characters.  The solution below works best for me:

    Declare @q char(1)

    Set @q = ''''

    The value in the Set statement is four (4) characters, all single quotes.



    Bob Monahon

  • You could also look at QuoteName()

    DECLARE @quote Char(1)

    SET @quote = ''''

    PRINT 'WHERE LastName = ' + QuoteName( @LName, @quote )

    PRINT 'WHERE TDate = ' + QuoteName( GetDate(), @quote )

    PRINT 'WHERE LDate = ' + QuoteName( GetDate() - 100, @quote )

  • Or you could use a substatute char in the string. There are obvious disadvantages with this, but there is a lot less string concantination and that is a good thing...

    DECLARE @sql nvarchar(2000), @pram1 nvarchar(100)

    SET @pram1 = 'West'

    SET @sql = Replace(



        col1 AS "Hello Kitty",

        col2 AS "Today is Today",

        col3 AS "War is hell"




        col4 = "' + @pram1 + '"'

    , '"', '''')

    PRINT @sql

  • You can write a UDF that receives a string and returns the string enclosed in single quotes. In your SQL code, you have a re-usable function that you can use throughout your database.

  • i wrote a function a while back for this same problem

    CREATE  FUNCTION dbo.Quote()





     RETURN ''''


    beauty of the function is it's always available (without the DECLARE and SET statements), and it can be used in views (inline)


  • One of those - "Doh, Why didn't I think of that?"  Simple and smart - well done.

    Best Regards

  • Yes quotename is a nice way of doing things, but bear in mind it has a limit of


    so you probably are better off using a UDF so you don't get caught out working with a really really big string   

    Nice method using the variable though, although perhaps a little @q is less intrusive?  Using a substitute char also makes good sense, like a ~ character or something not often used...

Viewing 15 posts - 1 through 15 (of 15 total)

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