September 2, 2015 at 2:27 pm
I need to use the same SQL source code in more than one stored proc.
Is there a way to -include mysql.sql or something like that ?
How does everyone handle this ? Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
September 2, 2015 at 2:36 pm
I'd use the InsertSnippet option. That's precisely the functionality that snippets have.
You just need to create a new template.
September 2, 2015 at 2:47 pm
I suspect many just use COPY - PASTE to handle this.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 2, 2015 at 3:18 pm
Alvin Ramard (9/2/2015)
I suspect many just use COPY - PASTE to handle this.
That's no good....what happens when that code needs to be changed ??
If SSMS had a source code -include feature, all you need to do is change the code in ONE place and recompile the procecures that reference that -include.
Where do we post enhancement requests ?
September 2, 2015 at 3:24 pm
mar.ko (9/2/2015)
Alvin Ramard (9/2/2015)
I suspect many just use COPY - PASTE to handle this.That's no good....what happens when that code needs to be changed ??
If SSMS had a source code -include feature, all you need to do is change the code in ONE place and recompile the procecures that reference that -include.
Where do we post enhancement requests ?
I'm not saying it's good; just saying how it probably is.
I agree that a better way would be great.
I wonder if there's any chance that the new Visual Studio database projects might have something along this line.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 2, 2015 at 4:20 pm
If you are using the exact same code in multiple procedures I'd look at seeing if I could change the procedures to call a common procedure where needed rather than maintaining the identical code in multiple locations.
Also, not too familiar with using source control with database objects, but it may be possible to control changes in multiple procedures this way so that you only maintain the code in one place.
September 2, 2015 at 10:50 pm
mar.ko (9/2/2015)
I need to use the same SQL source code in more than one stored proc.Is there a way to -include mysql.sql or something like that ?
How does everyone handle this ? Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
Quick thought, you could turn this logic into a iTVF and call that function from the stored procedures.
😎
Pseudo iTVF function
CREATE FUNCTION [SCHEMA_NAME].[FUNCTION_NAME]
(
@PARAM_ONE [DATA_TYPE]
,@PARAM_TWO [DATA_TYPE]
,@PARAM_THREE [DATA_TYPE]
,@PARAM_FOUR [DATA_TYPE]
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
CASE WHEN @PARAM_ONE = 'Core' AND LEFT(@PARAM_TWO,3) = '00Q' THEN @PARAM_THREE ELSE 0 END AS CoreTotal
,CASE WHEN @PARAM_ONE = 'Core' AND LEFT(@PARAM_TWO,3) = '00Q' AND LEFT(@PARAM_FOUR,5)!='Upsel' THEN @PARAM_THREE ELSE 0 END AS CoreSales
,CASE WHEN @PARAM_ONE = 'Core' AND LEFT(@PARAM_TWO,3) = '00Q' AND LEFT(@PARAM_FOUR,5)='Upsel' THEN @PARAM_THREE ELSE 0 END AS CoreUpsell
,CASE WHEN @PARAM_ONE != 'Core' AND LEFT(@PARAM_TWO,3) = '00Q' THEN @PARAM_THREE ELSE 0 END AS NonCoreTotal
;
September 3, 2015 at 6:56 am
There currently is no good way to handle this within SQL Server. Copy & paste is how most people do it. Now, you can make this work better, especially if you have to later modify the structure that you're copying and pasting. The way to do that is to get your database code under source control. Then, when you identify a pattern that you're going to have to fix across a wide variety of procedures, you can fix it programmatically and then deploy it programmatically. Because T-SQL absolutely doesn't lend itself to code re-use, that's how I'd do it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2015 at 9:40 am
mar.ko (9/2/2015)
I need to use the same SQL source code in more than one stored proc.Is there a way to -include mysql.sql or something like that ?
How does everyone handle this ? Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
It's a nice idea, but have you considered ALL the ramifications such a feature would entail?
1.) Where do you store the .SQL files that the feature would make use of when implemented in a production environment?
2.) Who might need access to that location?
3.) Who might "finger check" a file in that location, causing a massive problem if the right critical code gets whacked?
4.) Who pays for the costs of all the human errors that can creep into such a process, along with the cost of fixing any data problems that result?
5.) How many data problems from such a finger check might go entirely un-noticed because the nature of the problem would make it entirely impossible to determine just what data got changed?
To be honest, given what I've seen over the years, human beings just aren't good enough to be given that level of convenience, save in a rare few instances, and in most of those scenarios, they knew better. Any decent manager these days SHOULD be tossing this idea in the dustbin of history and then incinerating it before anyone sees it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 4, 2015 at 6:40 pm
sgmunson (9/4/2015)
mar.ko (9/2/2015)
I need to use the same SQL source code in more than one stored proc.Is there a way to -include mysql.sql or something like that ?
How does everyone handle this ? Is InsertSnippet the only way to do this ?
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS CoreTotal
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)!='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreSales
,CASE WHEN P.PROD_CTG = 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' AND LEFT(S.OPPS_RECORD_TP_NM,5)='Upsel' THEN S.MSL_QUANTITY ELSE 0 END AS CoreUpsell
,CASE WHEN P.PROD_CTG != 'Core' AND LEFT(C.SALESFORCE_LEAD_ID,3) = '00Q' THEN S.MSL_QUANTITY ELSE 0 END AS NonCoreTotal
It's a nice idea, but have you considered ALL the ramifications such a feature would entail?
1.) Where do you store the .SQL files that the feature would make use of when implemented in a production environment?
2.) Who might need access to that location?
3.) Who might "finger check" a file in that location, causing a massive problem if the right critical code gets whacked?
4.) Who pays for the costs of all the human errors that can creep into such a process, along with the cost of fixing any data problems that result?
5.) How many data problems from such a finger check might go entirely un-noticed because the nature of the problem would make it entirely impossible to determine just what data got changed?
To be honest, given what I've seen over the years, human beings just aren't good enough to be given that level of convenience, save in a rare few instances, and in most of those scenarios, they knew better. Any decent manager these days SHOULD be tossing this idea in the dustbin of history and then incinerating it before anyone sees it.
Heh... they already have all of this. It's called a "front end development environment". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply