Get Away From Confusing Code

Question

Post reply

Get Away From Confusing Code

KenpoDBA

SSCrazy Eights

Points: 9811
More actions
October 12, 2005 at 4:37 pm

#165647

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sMcCown/getawayfromconfusingcode.asp
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant
Minion Maintenance is FREE:

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

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

Alexander Yuryshev Old Hand Points: 390 More actions · Answer 1

Thanx.

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

Lee Dilkes SSC Enthusiast Points: 164 More actions · Answer 2

Lee Dilkes

SSC Enthusiast

Points: 164

November 15, 2005 at 3:36 am

#603847

Nice simple idea that make a lot of sense - thanks

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

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 well...got 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 !!!**

Craig A. Silvis Ten Centuries Points: 1038 More actions · Answer 4

Great approach and very easy to implement - good job!

SW21_Bob SSCommitted Points: 1826 More actions · Answer 5

Such a simple idea. Gotta love it.

Bob
SuccessWare Software

Revi Nadesan SSC-Addicted Points: 471 More actions · Answer 6

This is a webby site with ASCII information.

http://www.lookuptables.com/

Thanks and have a great day.

RN

David Markle SSC Journeyman Points: 86 More actions · Answer 7

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...)

Bob Monahon Ten Centuries Points: 1282 More actions · Answer 8

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.

Regards,

Bob Monahon

Dennis D. Allen Say Hey Kid Points: 699 More actions · Answer 9

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 )

Dennis D. Allen Say Hey Kid Points: 699 More actions · Answer 10

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(

'

SELECT

col1 AS "Hello Kitty",

col2 AS "Today is Today",

col3 AS "War is hell"

FROM

dbo.table1

WHERE

col4 = "' + @pram1 + '"'

, '"', '''')

PRINT @sql

Dennis Klemenz Right there with Babe Points: 721 More actions · Answer 11

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.

Erik Foisy SSC Veteran Points: 297 More actions · Answer 12

i wrote a function a while back for this same problem

CREATE FUNCTION dbo.Quote()

RETURNS CHAR(1)

AS

BEGIN

RETURN ''''

END

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

ejf

TeeGee Say Hey Kid Points: 697 More actions · Answer 13

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

Best Regards
Terry

Ian Yates SSCoach Points: 19738 More actions · Answer 14

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

nvarchar(258)

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...