December 8, 2014 at 2:55 pm
I have a stored proc that calls a few other stored procs. I've included an edited version of it at the bottom. I assumed that if I created a temp table in the top calling function, it would be available to all called functions. Something is not working.
When I run this test code, which is what the called function would do, I get table rows back from the temp table:
SELECT a.hic_nb, b.hcc_cd, a.svc_dt, b.mdl_ver_yr_nb, b.coefficient_qt
into #ICD_TO_HCC
from [Test_Trump_Data_1000] as a inner join
vwICD_TO_HCC_Pivot as b
on a.icd_diag_cd = b.icd_diag_cd and
year(a.svc_dt) = b.mdl_ver_yr_nb
select * from #ICD_TO_HCC
========================================================
When I run this code, to call that child proc, I get no rows back:
alter PROCEDURE [dbo].[spTrumping_Process_Testing]
-- Add the parameters for the stored procedure here
@tbl nvarchar(50),
@outtbl nvarchar(50),
@cut_off_date nvarchar(15)
AS
BEGIN
Create table #ICD_TO_HCC (
HIC_NB nvarchar(20),
HCC_CD nvarchar(10),
SVC_DT datetime,
MDL_VER_YR_NB nvarchar(4),
COEFFICIENT_QT numeric(6,3)
)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
exec spICD_TO_HCC_COEFF_Test @tbl -- fills #ICD_TO_HCC from input table.
select top 10 * from #ICD_TO_HCC
END
===========================================
can anyone tell me what I might be missing? this process works if I use normal tables to hold the results.
December 8, 2014 at 3:14 pm
DSNOSPAM (12/8/2014)
I have a stored proc that calls a few other stored procs. I've included an edited version of it at the bottom. I assumed that if I created a temp table in the top calling function, .....
You assumed wrong!
Try using ## instead of for the temp tables that are used in more than one procedure.
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]
December 8, 2014 at 3:20 pm
what if two people run the proc at the same time? Is the ## still within my login space, and can't be used by others?
Thanks for the response.
December 8, 2014 at 3:26 pm
DSNOSPAM (12/8/2014)
what if two people run the proc at the same time? Is the ## still within my login space, and can't be used by others?Thanks for the response.
I believe you are correct.
# creates a table that is only available to the connection that created it.
## creates a table that is only available to the login that created it. I may not be 100% correct, but you get the idea.
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]
December 8, 2014 at 3:39 pm
Hmmm.....by this post I found on StackOverflow, everyone can see global temp tables.
There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
December 8, 2014 at 3:47 pm
hmmm
That's news to me. A quick Google search found sources that agree with you and others that do not. Since one that agreed was a Microsoft Technet page, I guess you're right.
Given the above bad news, I'd consider using table variables instead.
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]
December 8, 2014 at 3:53 pm
I could have table results with hundreds of thousands of records. I THINK I read that that is not a good use for table variables. If I use just regular tables, they will be created only within the user's space. Maybe I need to stay with that.
December 9, 2014 at 1:23 am
You should be able to do what you are trying to do; I've done this myself before. Having created the local temp table in one stored procedure, it will be 'in scope' for others called from it. Can you post the full definitions of both procedures? There is probably some other problem that is causing the temp table to be empty.
December 9, 2014 at 1:30 am
DSNOSPAM (12/8/2014)
I could have table results with hundreds of thousands of records. I THINK I read that that is not a good use for table variables.
Correct
If I use just regular tables, they will be created only within the user's space. Maybe I need to stay with that.
Incorrect. Regular tables (non-temp tables) are visible to everyone and require permissions to create that apps shouldn't have.
Create the temp table in the outer-most procedure, then it will be visible to all procedures that it calls.
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
December 9, 2014 at 5:59 am
Alvin Ramard (12/8/2014)
hmmmThat's news to me. A quick Google search found sources that agree with you and others that do not. Since one that agreed was a Microsoft Technet page, I guess you're right.
Given the above bad news, I'd consider using table variables instead.
Nah, not table variables, not for large data sets involving filtering and joins. Just add a check for the existence of the global temp table. Or, do what Gail has suggested.
"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
December 9, 2014 at 7:15 am
Here is where I am going to disagree with Gail and Grant. I have worked on a system that had many procs nested like this and it was a serious PITA to maintain. What you end up with is a bunch of stored procedures that can't work on their own because they require temp tables created in previous procedures. You end up with a tightly coupled group of procedures. It seems clever during development but in the long run it is painful. Debugging procedures like that (especially when you were not the author) is like the spaghetti code of old. I would recommend rethinking the process and roll the logic into a single stored procedure to save yourself hours and hours of regret down the road.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 9, 2014 at 7:34 am
Sean Lange (12/9/2014)
Here is where I am going to disagree with Gail and Grant. I have worked on a system that had many procs nested like this and it was a serious PITA to maintain. What you end up with is a bunch of stored procedures that can't work on their own because they require temp tables created in previous procedures. You end up with a tightly coupled group of procedures. It seems clever during development but in the long run it is painful. Debugging procedures like that (especially when you were not the author) is like the spaghetti code of old. I would recommend rethinking the process and roll the logic into a single stored procedure to save yourself hours and hours of regret down the road.
Not being argumentative, because I think you make a very valid point, you do need to balance clarity with behavior. While I absolutely abhor nesting procedures, especially beyond a single layer of nesting, it does solve some common issues. For example, if you're dealing with bad parameter sniffing, one approach is to have a wrapper procedure that calls two other procedures based on the parameter value passed, so that each proc gets it's own plan.
Again, not arguing with your fundamental approach because I think it's valid. Just that these things do have to be thought through from both sides.
"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
December 9, 2014 at 7:41 am
Grant Fritchey (12/9/2014)
Sean Lange (12/9/2014)
Here is where I am going to disagree with Gail and Grant. I have worked on a system that had many procs nested like this and it was a serious PITA to maintain. What you end up with is a bunch of stored procedures that can't work on their own because they require temp tables created in previous procedures. You end up with a tightly coupled group of procedures. It seems clever during development but in the long run it is painful. Debugging procedures like that (especially when you were not the author) is like the spaghetti code of old. I would recommend rethinking the process and roll the logic into a single stored procedure to save yourself hours and hours of regret down the road.Not being argumentative, because I think you make a very valid point, you do need to balance clarity with behavior. While I absolutely abhor nesting procedures, especially beyond a single layer of nesting, it does solve some common issues. For example, if you're dealing with bad parameter sniffing, one approach is to have a wrapper procedure that calls two other procedures based on the parameter value passed, so that each proc gets it's own plan.
Again, not arguing with your fundamental approach because I think it's valid. Just that these things do have to be thought through from both sides.
I absolutely agree that nesting procedures has their place, doesn't everything in sql server? 😉 Wrapper procedures is a great example of where they make lots of sense. I am probably jumping to conclusions quicker than I should and end up sounding like the ultimate pessimist.
--JOKE--
Do you know the difference between a pessimist and an optimist?
Experience. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 9, 2014 at 7:51 am
doesn't everything in sql server? 😉
Still trying to figure out what the use-case for multi-statement table-valued user-defined functions is. But, other than that, yes!
"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
December 9, 2014 at 8:04 am
Grant Fritchey (12/9/2014)
doesn't everything in sql server? 😉
Still trying to figure out what the use-case for multi-statement table-valued user-defined functions is. But, other than that, yes!
Their one use is for people to demonstrate why they're bad.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply