Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Get Away From Confusing Code Expand / Collapse
Author
Message
Posted Tuesday, November 15, 2005 10:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 12:03 PM
Points: 117, Visits: 163
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
Post #237262
Posted Tuesday, November 15, 2005 1:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 21, 2014 11:09 AM
Points: 20, Visits: 100
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.
Post #237313
Posted Tuesday, November 15, 2005 2:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 26, 2006 8:41 AM
Points: 3, Visits: 1

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

Post #237337
Posted Tuesday, November 15, 2005 3:42 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, May 22, 2011 6:15 PM
Points: 65, Visits: 80

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





Best Regards
Terry
Post #237349
Posted Wednesday, November 16, 2005 5:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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




Post #237477
Posted Saturday, November 19, 2005 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 19, 2005 2:36 AM
Points: 1, Visits: 1

Mr.Allen, you provided a very good another logic to get away from confusing code.  I understood how to use table data type practically and thanks for the author and Mr.Allen too for a nice example of using qutoname function.

Post #238295
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse