Forum Replies Created

Viewing 15 posts - 7,306 through 7,320 (of 7,614 total)

  • RE: Optional Parameters in Stored Procedures

    I really think you're going to drive people crazy trying to follow this because no one else does it.

    Almost universal is to use NULL to mean "no value was passed...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 17, 2012 at 10:48 am

    #1513717

  • RE: Optional Parameters in Stored Procedures

    takilroy (7/17/2012)

    Unfortunately, I can't use look up tables because a default value for a parameter MUST be a constant, meaning either a literal value or a @@ constant. So,...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 17, 2012 at 9:04 am

    #1513654

  • RE: Random space filled varchars

    Again, in theory yes.

    But in the real world that's often difficult to do and takes a lot of time. And often a change later to the code by someone...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 16, 2012 at 3:42 pm

    #1513350

  • RE: Optional Parameters in Stored Procedures

    You can use a lookup table to store default/control values.

    Or, less overhead but more obscure, you can store the default/control values in specific byte positions of CONTEXT_INFO.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 16, 2012 at 3:38 pm

    #1513347

  • RE: What is the equivalent of ORACLE “MONTH_between “ in SQL ?

    I'm not familiar with MONTHS_BETWEEN, but IF it does always use 31 days as a "month" in that function, then you can get the same result from SQL Server using:

    SELECT...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 16, 2012 at 3:32 pm

    #1513346

  • RE: Random space filled varchars

    Michael Valentine Jones (7/16/2012)

    ScottPletcher (7/16/2012)

    SQLKnowItAll (7/16/2012)

    ScottPletcher (7/16/2012)

    Donald Bustell (7/16/2012)

    Database: timetracking

    Column: project varchar(50)

    Questions:

    1. What in the world???

    2. How would I craft a trigger to just RTRIM everything on the way in?

    Thanks

    Donald

    CREATE...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 16, 2012 at 3:02 pm

    #1513335

  • RE: Random space filled varchars

    SQLKnowItAll (7/16/2012)

    ScottPletcher (7/16/2012)

    Donald Bustell (7/16/2012)

    Database: timetracking

    Column: project varchar(50)

    Questions:

    1. What in the world???

    2. How would I craft a trigger to just RTRIM everything on the way in?

    Thanks

    Donald

    CREATE TRIGGER dbo.project_trg_ins

    ON dbo.project

    AFTER INSERT

    AS

    UPDATE...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 16, 2012 at 2:18 pm

    #1513310

  • RE: Need T-SQL Help on Aggregate/Subquery w/my own "labels"

    Something like below should do it:

    SELECT

    CASE WHEN backup_month IS NULL THEN 'ALL'

    ELSE LEFT(DATENAME(MONTH, backup_month), 3) END AS...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 16, 2012 at 2:13 pm

    #1513306

  • RE: Random space filled varchars

    Donald Bustell (7/16/2012)

    Database: timetracking

    Column: project varchar(50)

    Questions:

    1. What in the world???

    2. How would I craft a trigger to just RTRIM everything on the way in?

    Thanks

    Donald

    CREATE TRIGGER dbo.project_trg_ins

    ON dbo.project

    AFTER INSERT

    AS

    UPDATE p

    SET

    ...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 16, 2012 at 1:56 pm

    #1513296

  • RE: FROM clause challenge

    JayWinter (7/12/2012)

    In this case SKU and UPC are the same and therefore its usage is correct. However my challenge is that there may be more than 1 record that meets...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 13, 2012 at 4:42 pm

    #1512740

  • RE: Minimum missing value - Query help

    Presumably the ContactID is indexed.

    If there can only be single-line gaps, you can do this:

    SELECT

    c1.ContactID - 1 AS Missing_Number

    FROM #Contact c1

    WHERE NOT EXISTS...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 13, 2012 at 4:11 pm

    #1512735

  • RE: Differences between LIKE and LEFT/RIGHT selects

    dan-572483

    I was surprised to see that the Estimated Execution Plan shows a slightly higher cost for LEFT(LastName,2) over LIKE. The Plan for LIKE recommended creating a nonclustered index...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 13, 2012 at 3:48 pm

    #1512729

  • RE: Differences between LIKE and LEFT/RIGHT selects

    Loner (7/13/2012)

    Please see

    http://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/

    LEFT() is a function it would result in an index scan where like '% %' would result in index seek which is more efficient.

    Not quite.

    LIKE 'xxx%' can...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 13, 2012 at 3:41 pm

    #1512728

  • RE: Need help on a Sql Query

    I don't think you really need indexing, since you can do everything in a single pass of the table, w/o JOINs, etc..

    SELECT

    ACTIONID,

    CASE...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 13, 2012 at 2:42 pm

    #1512712

  • RE: Updating Unique Sequential NUmber

    Lynn Pettis (7/11/2012)

    Matt Miller (#4) (7/11/2012)

    Lynn Pettis (7/11/2012)

    This works as well:

    CREATE TABLE [dbo].[TEST1](

    [ID] [VARCHAR](10) NULL,

    [VALUE] [int] NULL

    );

    GO

    INSERT INTO dbo.TEST1 (ID, VALUE) SELECT 1, 10;

    SELECT 'PRE' LABEL1, * FROM dbo.TEST1;

    DECLARE @SAVE1...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    July 11, 2012 at 1:50 pm

    #1511777

Viewing 15 posts - 7,306 through 7,320 (of 7,614 total)