May 16, 2013 at 7:49 am
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!!
May 16, 2013 at 7:55 am
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
May 16, 2013 at 8:02 am
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?
May 16, 2013 at 8:05 am
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
May 16, 2013 at 8:11 am
This is just the tip too.....
No idea?
May 16, 2013 at 8:45 am
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:
May 16, 2013 at 9:01 am
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
May 16, 2013 at 9:02 am
That's what I'm finding out.
Thanks!
May 16, 2013 at 11:34 am
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
May 17, 2013 at 7:58 am
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
August 27, 2013 at 8:28 pm
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."
August 27, 2013 at 8:41 pm
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