Is it possible to embed a parameter in the name of stored procedure that's called from within another sproc?

Question

Is it possible to embed a parameter in the name of stored procedure that's called from within another sproc?

pharmkittie

SSCertifiable

Points: 5775
More actions
January 29, 2015 at 8:36 am

#295038

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. Is it possible? If so, what is the syntax? Thanks.
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
round(cast([02-Total DM Patients with LDL] as float)/[01-Total Patients with DM]* 100,2) as PercentLDLComplete
, round(cume_dist() over ( order by round(cast([02-Total DM Patients with LDL]as float)/[01-Total Patients with DM]* 100,2) )*100,2) as CDist
,YYYYQQ
from dbo.DMMeasures
where [01-Total Patients with DM] > 0
and YYYYQQ = @YYYYQQ
order by round(cast([02-Total DM Patients with LDL]as float)/[01-Total Patients with DM]* 100,2) desc , CDist desc
end
exec DM@QQ_YYYYMT1
parameter value examples: @YYYYQQ = '2014Q3' and @QQ_YYYY = Q3_2014
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

pharmkittie (1/29/2015)
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. Is it possible? If so, what is the syntax? Thanks.
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
round(cast([02-Total DM Patients with LDL] as float)/[01-Total Patients with DM]* 100,2) as PercentLDLComplete
, round(cume_dist() over ( order by round(cast([02-Total DM Patients with LDL]as float)/[01-Total Patients with DM]* 100,2) )*100,2) as CDist
,YYYYQQ
from dbo.DMMeasures
where [01-Total Patients with DM] > 0
and YYYYQQ = @YYYYQQ
order by round(cast([02-Total DM Patients with LDL]as float)/[01-Total Patients with DM]* 100,2) desc , CDist desc
end
exec DM@QQ_YYYYMT1
parameter value examples: @YYYYQQ = '2014Q3' and @QQ_YYYY = Q3_2014

First of all, in the batch above you need to have a GO directive between END and EXEC.

To pass in parameters, I prefer to do so by name:

exec DM@QQ_YYYYMT1 @YYYYQQ = '2014Q3', @QQ_YYYY = Q3_2014

You can also pass them positionally but I've never felt that was a particularly maintainable option.

Not sure if this answers your question but let me know.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

mister.magoo SSC-Forever Points: 47068 More actions · Answer 2

You just need to use a new variable for the second procedure name, like this:

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]

@QQ_YYYY char(7),

@YYYYQQ char(8)

AS

SET NOCOUNT ON;

select

[provider group]

, provider

, npi

, [01-total patients with dm]

, [02-total dm patients with ldl]

, round(cast([02-total dm patients with ldl] as float) / [01-total patients with dm] * 100, 2) as percentldlcomplete

, round(cume_dist() over (order by round(cast([02-total dm patients with ldl] as float) / [01-total patients with dm] * 100, 2)) * 100, 2) as cdist

, yyyyqq

from dbo.dmmeasures

where [01-total patients with dm] > 0

and yyyyqq = @YYYYQQ

order by round(cast([02-total dm patients with ldl] as float) / [01-total patients with dm] * 100, 2) desc, cdist desc;

--== build a variable with the name of the SP to call

declare @name sysname = quotename('DM'+@QQ_YYYY+'MT1');

exec @name;

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Are you seriously creating the same stored procedures for each quarter? I don't want to imagine what would happen if you need a daily report.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pharmkittie SSCertifiable Points: 5775 More actions · Answer 4

Luis Cazares: Hey, we all have to start somewhere. What would you suggest as the better way? Thanks.

dwain.c and mister magoo: Thank you! I will try your solutions tomorrow and report back/mark as answer(s).

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

dwain.c (1/29/2015)
First of all, in the batch above you need to have a GO directive between END and EXEC.

You can ignore this part of what I said. I realize that you're trying to EXEC the second SP from within the first, in which case the EXEC should be within the BEGIN/END block.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

pharmkittie (1/29/2015)
Luis Cazares: Hey, we all have to start somewhere. What would you suggest as the better way? Thanks.
dwain.c and mister magoo: Thank you! I will try your solutions tomorrow and report back/mark as answer(s).

I'm sorry if it sounded rude, but I was stunned by this and no one said a thing.

Stored procedures are parametrizable so you don't have to generate new code each time. If you create your procedure like this:

CREATE PROCEDURE [dbo].[runDMLDLComplete]

@YYYYQQ char(6)

AS

SET NOCOUNT ON;

select [provider group]

,provider

,NPI

,[01-Total Patients with DM]

,[02-Total DM Patients with LDL]

,round(cast([02-Total DM Patients with LDL] as float)/[01-Total Patients with DM]* 100,2) as PercentLDLComplete

,round(cume_dist() over ( order by round(cast([02-Total DM Patients with LDL]as float)/[01-Total Patients with DM]* 100,2) )*100,2) as CDist

,YYYYQQ

from dbo.DMMeasures

where [01-Total Patients with DM] > 0

and YYYYQQ = @YYYYQQ

order by PercentLDLComplete desc

,CDist desc

And run it like this:

DECLARE @YYYYQQ char(6) = (YEAR(GETDATE()) * 100) + DATEPART(QQ, GETDATE())

PRINT @YYYYQQ --Check the value

EXEC [dbo].[runDMLDLComplete] @YYYYQQ

That's the same procedure that will work for any date you want. There's no need to create new procedures for each quarter.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pharmkittie SSCertifiable Points: 5775 More actions · Answer 7

Thank you everyone. All your answers helped me with my question. The reason I was not able to just parameterize the date and quarter to solve the entire problem is because the column names often vary each quarter, even when most of them mean the same thing as the last quarter. I have asked a few times if the people who pull the raw data could use a consistent naming scheme but they won't. They always come back with: The measures may change from quarter to quarter, to which I reply: Any new measures are fine; I can add or subtract measures. But I'm hoping that the measures that stay the same from quarter to quarter can have the same names. Answer is no.

So, for example, we have a measure that was called [Long measure name with lots of spaces and chopped off names ] one quarter and the same measure will be called [ Long measure name with lots of spaces and chopped off nam ] the next quarter. Note too the extra spaces in one. And there are many, many files, measures, etc. It's a nightmare. My coworkers are not database clueful but believe they are because they don't know what they don't know. I asked the smartest one how he would define a data warehouse versus a "regular" database. His reply: A DW is just a really, really big database. I said the way data is stored varies, for one thing. And this person is very intelligent but the rest are not so much. Majority rule where I work.

I suppose I could look into parameterizing the column names too....

Thanks again everyone!

pharmkittie SSCertifiable Points: 5775 More actions · Answer 8

I'm honored that Joe Celko gave me advice! I have a couple of your books. I'm guilty of some of the things you mentioned but I have zero control over the column names they give me. You called it: The columns are named by COBOL programmers. Same story with the spaces. They insist that I use their naming schemes. Frustrates me no end. I tell that that we can relabel for a report or user interface but they want it stored this way in the database. Trust me when I say that the majority rules where I work. No one wants to listen to a newcomer. I am guilty of adding the computations though. I want to get percentiles so I use cume_dist.

I didn't know that I should show DDL but I'll include it in the future.

I will study your advice and I'm appreciative that I have been flamed by the famous Joe Celko. I've heard that you are tough but I don't mind if I can learn from it.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

CELKO (2/2/2015)
These report periods can overlap or have gaps. I like the MySqL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. In your case, I would suggest 'yyyyQq' instead. [font="Arial Black"]The advantages are that it will sort [/font]with the ISO-8601 data format [font="Arial Black"]required by Standard SQL [/font]and it is language independent.
...{snip}...
You even used an ORDER BY in a query; [font="Arial Black"]today, we pass the raw query data to a report server and do all the sorting there instead[/font]. Wasting time and resources on the database server for a job best done by other tools. You migth not have this hardware, but be aware of this.

I could be wrong but that seems like a direct contradiction.

You error is that you do not know that SQL is a database language; it does not do statistics or [font="Arial Black"]computations [/font]worth a damn.

True, true. That means that about a quarter of the articles found [font="Arial Black"]here[/font][/url] were apparently written by someone else that doesn't know that SQL is a database language. In fact, that all makes [font="Arial Black"]this[/font] book just about totally useless in the world of databases. 😉

Heh... C'mon, Joe. You've been spouting the same hypocritical stuff for more than a decade. Give it up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 10

CELKO (2/2/2015)
You even used an ORDER BY in a query; today, we pass the raw query data to a report server and do all the sorting there instead. Wasting time and resources on the database server for a job best done by other tools. You migth not have this hardware, but be aware of this.

It might depend on the reporting tool, but my experience is that sorting in the database (thus in the query) is faster than sorting the data in the tool (I'm specifically talking about SSRS).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

CELKO (2/3/2015)

I will study your advice and I'm appreciative that I have been flamed by the famous Joe Celko. I've heard that you are tough but I don't mind if I can learn from it.

It is Rite of Passage in the SQL world 😉 I did a column on the COBOL & mag tape mindset years ago; programmers who never used the language are trapped by it and do not know it.

https://www.simple-talk.com/sql/t-sql-programming/mimicking-magnetic-tape-in-sql/%5B/quote%5D

Too bad you can't even use the IFCode tags to the left to make it easier for others to follow your links. Here, I'll do it for you today.

https://www.simple-talk.com/sql/t-sql-programming/mimicking-magnetic-tape-in-sql/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

CELKO (2/3/2015)

I will study your advice and I'm appreciative that I have been flamed by the famous Joe Celko. I've heard that you are tough but I don't mind if I can learn from it.

It is Rite of Passage in the SQL world 😉 I did a column on the COBOL & mag tape mindset years ago; programmers who never used the language are trapped by it and do not know it.

https://www.simple-talk.com/sql/t-sql-programming/mimicking-magnetic-tape-in-sql/%5B/quote%5D

BWAA-HAAA. In some countries, so is genital mutilation but that doesn't mean it's a good thing. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 13

pharmkittie (2/1/2015)
Thank you everyone. All your answers helped me with my question. The reason I was not able to just parameterize the date and quarter to solve the entire problem is because the column names often vary each quarter, even when most of them mean the same thing as the last quarter. I have asked a few times if the people who pull the raw data could use a consistent naming scheme but they won't. They always come back with: The measures may change from quarter to quarter, to which I reply: Any new measures are fine; I can add or subtract measures. But I'm hoping that the measures that stay the same from quarter to quarter can have the same names. Answer is no.

I've actually also seen instances of yearly specific stuff, this time from a commercial software vendor, and the tables would have the year in the name and have different columns from the tables from previous years. The reasoning is that when adding and removing columns, historical data wouldn't have to be involved in these changes.

Right? Wrong? Who knows, but its their product and decision so thats that.