What is @Dummy ?

  • Hello all!

    I've been assigned to deconstruct and document a rather large Stored Procedure (SQL 2008). This procedure simply declared about a dozen of other procedures with I will need to anaylize and document. I'm coming into an environment where a handleful of other developers wrote, programmed, and left and not a damn thing was documented. So, I along with the help of someone else, are decontructing it all and documenting it.

    Anyway, the SP starts off like this:

    ALTER PROCEDURE [dbo].[The Name of this very SP]

    (@Dummy varchar(20))

    as

    Exec [SP #1] 1

    Exec [SP #2] 1

    ...and so on.

    I have two questions.

    1) What does @Dummy do?

    2) What do the "1" indicate after firing off each SP?

    Thanks in advance for your help!!

  • Can't tell for sure, since you only posted part of the code, but the @Dummy parameter does nothing in the code you posted. Perhaps it's used elsewhere.

    The 1 after the procedure calls is a parameter value, like

    exec sp_help 'Test'

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for helping. Here is is all cleaned up:

    USE [Database name]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[THIS SP NAME](

    @Dummy varchar(20)

    )

    as

    Exec [SP #1] 1

    Exec [SP #2]

    Exec [SP #3] 1

    Exec [SP #4] 1

    ...an so on...

    That's it.

    If I peak into SP #1, I see this:

    ALTER PROCEDURE [dbo].[SP #1](

    @test-2 int

    )

    AS

    Truncate table [Table Name]

    Thoughts?

  • RedBirdOBX (5/16/2013)


    Thoughts?

    The developers were smoking something.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This is just the tip too.....

    No idea?

  • GilaMonster (5/16/2013)


    RedBirdOBX (5/16/2013)


    Thoughts?

    The developers were smoking something.

    ... smoking something from Amsterdam and drinking strong clear thing from Eastern lands of Europe...

    :hehe:

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • RedBirdOBX (5/16/2013)


    No idea?

    The parameters are meaningless and never used.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • That's what I'm finding out.

    Thanks!

  • Total WAG...but I have seen things like this where the developers were informed (for whatever reason) that stored procedures require a parameter and therefore they built everything with a parameter whether or not it was used.

    I have also seen similar development methods used when the developers built a generic utility in code and that utility required all stored procedures to have a parameter - because if it didn't, their nice little generic utility would break.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams 3188 (5/16/2013)


    Total WAG...but I have seen things like this where the developers were informed (for whatever reason) that stored procedures require a parameter and therefore they built everything with a parameter whether or not it was used.

    I have also seen similar development methods used when the developers built a generic utility in code and that utility required all stored procedures to have a parameter - because if it didn't, their nice little generic utility would break.

    +1

     

  • Hey...I found the answer:

    Erland Sommarskog, SQL Server MVP. 2013-03-24.

    How to Share Data between Stored Procedures

    "Another solution, which requires SQL 2008, comes from Wayne Bloss. He creates a table type that holds the definition of the temp table. You can only use table types for declaring table variable and table parameters. But Wayne has a cure for this:

    DECLARE @dummy my_table_type

    SELECT * INTO #mytemp FROM @dummy

    From this point you work with #mytemp; the sole purpose of @dummy is to be able to create #mytemp from a known and shared definition. (If you are unacquainted with table types, we will take a closer look on them in the section on table-valued parameters.) A limitation with this method is that you can only centralise column definitions this way, but not constraints as they are not copied with SELECT INTO. You may think that constraints are odd things you rarely put in a temp table, but I have found that it is often fruitful to add constraints to my temp tables as assertions for my assumptions about the data. This does not the least apply for temp tables that are shared between stored procedures. Also, defining primary keys for your temp tables can avoid performance issues when you start to join them."

     

  • Steven Willis (8/27/2013)


    Hey...I found the answer:

    Erland Sommarskog, SQL Server MVP. 2013-03-24.

    How to Share Data between Stored Procedures

    "Another solution, which requires SQL 2008, comes from Wayne Bloss. He creates a table type that holds the definition of the temp table. You can only use table types for declaring table variable and table parameters. But Wayne has a cure for this:

    DECLARE @dummy my_table_type

    SELECT * INTO #mytemp FROM @dummy

    From this point you work with #mytemp; the sole purpose of @dummy is to be able to create #mytemp from a known and shared definition. (If you are unacquainted with table types, we will take a closer look on them in the section on table-valued parameters.) A limitation with this method is that you can only centralise column definitions this way, but not constraints as they are not copied with SELECT INTO. You may think that constraints are odd things you rarely put in a temp table, but I have found that it is often fruitful to add constraints to my temp tables as assertions for my assumptions about the data. This does not the least apply for temp tables that are shared between stored procedures. Also, defining primary keys for your temp tables can avoid performance issues when you start to join them."

     

    The two applications are different. Dummy in this example is used as a table valued parameter and not a varchar parameter.

    I see no use for the declaration of the Dummy parameter in case of the op - just some bad developer practice or like Gail said - somebody was smoking something.

    It could also be a holdover from an Oracle conversion where dummy tables are used rampantly but there isn't something that does the same thing by default in SQL so the developer built something to resemble the oracle process.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 12 posts - 1 through 11 (of 11 total)

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