Forum Replies Created

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

  • 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

  • RE: Updating Unique Sequential NUmber

    Lynn Pettis (7/11/2012)

    ScottPletcher (7/11/2012)

    gerard-593414 (7/11/2012)

    I have a table which holds the next Invoice Number. What SP code should I use to increment it (Cant be self incrementing)

    There are numerous...

    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 12:29 pm

    #1511729

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