April 27, 2012 at 3:08 pm
OK that is what I thought. This shouldn't be a big deal. I would stay but I have to go pick up the kiddos.
_______________________________________________________________
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/
April 27, 2012 at 4:59 pm
Something like this?
with cteRates as (
select o.emp_id,
row_number() over (partition by o.emp_id order by x.r.value('@date_changed','DATETIME') desc) as ROWNUM,
count(*) over (partition by o.emp_id) as MAX_ROWNUM,
x.r.value('@date_changed','DATETIME') AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS RATE
from #EMPLOYEE_OTHER2 o
cross apply o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT rLatest.ROWNUM,
e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
rLatest.DATE_CHANGED,
rOldest.RATE AS FROM_PAY_RATE,
rLatest.RATE AS TO_PAY_RATE
FROM #EMPLOYEE2 e
left outer join cteRates rLatest on (rLatest.EMP_ID = e.EMP_ID and rLatest.ROWNUM = 1)
left outer join cteRates rOldest on (rOldest.EMP_ID = e.EMP_ID and rOldest.ROWNUM = rOldest.MAX_ROWNUM)
May 2, 2012 at 9:13 am
R.P.Rozema (4/27/2012)
Something like this?
with cteRates as (select o.emp_id,
row_number() over (partition by o.emp_id order by x.r.value('@date_changed','DATETIME') desc) as ROWNUM,
count(*) over (partition by o.emp_id) as MAX_ROWNUM,
x.r.value('@date_changed','DATETIME') AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS RATE
from #EMPLOYEE_OTHER2 o
cross apply o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT rLatest.ROWNUM,
e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
rLatest.DATE_CHANGED,
rOldest.RATE AS FROM_PAY_RATE,
rLatest.RATE AS TO_PAY_RATE
FROM #EMPLOYEE2 e
left outer join cteRates rLatest on (rLatest.EMP_ID = e.EMP_ID and rLatest.ROWNUM = 1)
left outer join cteRates rOldest on (rOldest.EMP_ID = e.EMP_ID and rOldest.ROWNUM = rOldest.MAX_ROWNUM)
Here is my code, but I am not getting any data. Just a blank table.
alter PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@EMP_ID INT
)
AS BEGIN
DECLARE @x XML;
with cteRates as (
select o.emp_id,
row_number() over (partition by o.emp_id order by x.r.value('@date_changed','DATETIME') desc) as ROWNUM,
count(*) over (partition by o.emp_id) as MAX_ROWNUM,
x.r.value('@date_changed','DATETIME') AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS RATE
from #EMPLOYEE_OTHER2 o
cross apply o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT rLatest.ROWNUM,
e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
rLatest.DATE_CHANGED,
rOldest.RATE AS FROM_PAY_RATE,
rLatest.RATE AS TO_PAY_RATE
FROM #EMPLOYEE2 e
left outer join cteRates rLatest on (rLatest.EMP_ID = e.EMP_ID and rLatest.ROWNUM = 1)
left outer join cteRates rOldest on (rOldest.EMP_ID = e.EMP_ID and rOldest.ROWNUM = rOldest.MAX_ROWNUM)
END
May 2, 2012 at 9:18 am
That can't possibly be the entire code for your proc. It is selecting data from temp tables that are not declared inside the proc.
_______________________________________________________________
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/
May 2, 2012 at 9:22 am
Sean Lange (5/2/2012)
That can't possibly be the entire code for your proc. It is selecting data from temp tables that are not declared inside the proc.
Let's go from this code:
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS
BEGIN
DECLARE @x XML;
CREATE TABLE #RATE_HISTORY
(
EMP_NAME VARCHAR(255),
EMP_ORGANIZATION VARCHAR(255),
EMP_DEPARTMENT VARCHAR(255),
EMP_DT_TERMINATION DATETIME,
DATE_CHANGED DATETIME,
FROM_PAY_RATE NVARCHAR(MAX),
TO_PAY_RATE NVARCHAR(MAX)
)
INSERT INTO #RATE_HISTORY
(
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
EMP_DT_TERMINATION,
DATE_CHANGED,
FROM_PAY_RATE,
TO_PAY_RATE
)
SELECT
e.emp_name,
e.emp_organization,
e.emp_department,
e.emp_dt_termination,
DATE_CHANGED = (select convert(varchar(10), max(x.r.value('@date_changed','DATETIME')), 101) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),
FROM_PAY_RATE = (select x.r.value('@rate','DECIMAL(10,5)') from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)), --where x.r.value('@date_changed','DATETIME') = 'min(x.r.value('@date_changed','DATETIME'))',
TO_PAY_RATE = (select x.r.value('@rate','DECIMAL(10,5)') from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)) --where x.r.value('@date_changed','DATETIME') = 'max(x.r.value('@date_changed','DATETIME'))'
FROM employee e
JOIN employee_other eo ON e.emp_id = eo.emp_id
where e.emp_id = @emp_id and (emp_dt_termination is NULL) and e.emp_organization_id = @ORG_LEVEL_ID
select * from #RATE_HISTORY
END
May 2, 2012 at 9:29 am
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.
NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
OK rant about that over, perhaps it is more frustrating to me because I worked on a huge system once where this type of thing was all over the place. It is horrible to maintain. It took weeks to document how all that stuff fit together.
Now if I take your sample data and ddl the code you posted works fine.
First here is your proc code exactly as you posted it.
alter PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@EMP_ID INT
)
AS BEGIN
DECLARE @x XML;
with cteRates as (
select o.emp_id,
row_number() over (partition by o.emp_id order by x.r.value('@date_changed','DATETIME') desc) as ROWNUM,
count(*) over (partition by o.emp_id) as MAX_ROWNUM,
x.r.value('@date_changed','DATETIME') AS DATE_CHANGED,
x.r.value('@rate','DECIMAL(10,5)') AS RATE
from #EMPLOYEE_OTHER2 o
cross apply o.rate_history.nodes('/Rates/Rate') AS x(r)
)
SELECT rLatest.ROWNUM,
e.EMP_ID,
e.EMP_NAME,
e.EMP_ORGANIZATION,
e.EMP_DEPARTMENT,
e.EMP_DT_TERMINATION,
rLatest.DATE_CHANGED,
rOldest.RATE AS FROM_PAY_RATE,
rLatest.RATE AS TO_PAY_RATE
FROM #EMPLOYEE2 e
left outer join cteRates rLatest on (rLatest.EMP_ID = e.EMP_ID and rLatest.ROWNUM = 1)
left outer join cteRates rOldest on (rOldest.EMP_ID = e.EMP_ID and rOldest.ROWNUM = rOldest.MAX_ROWNUM)
END
Then your sample data exactly as you posted it.
CREATE TABLE #EMPLOYEE2
(
EMP_ID INT,
EMP_NAME VARCHAR(MAX),
EMP_ORGANIZATION VARCHAR(MAX),
EMP_DEPARTMENT VARCHAR(MAX),
EMP_DT_TERMINATION DATETIME
)
CREATE TABLE #EMPLOYEE_OTHER2
(
EMP_ID INT,
RATE_HISTORY XML
)
INSERT INTO #EMPLOYEE2
(
EMP_ID,
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
EMP_DT_TERMINATION
)
(SELECT '3' , 'Whitehead, Molly ', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '5' , 'Gentry, Clinton A ', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '6' , 'Crosby, Sherman', 'Silver Manor', 'Administrative', NULL UNION ALL
SELECT '7' , 'Riddle, Nichole', 'Silver Manor', 'Maintenance', NULL
)
INSERT INTO #EMPLOYEE_OTHER2
(
EMP_ID,
RATE_HISTORY
)
(SELECT '3' , '<Rates>
<Rate date_changed="Mar 6 2011 12:00AM" not_active_since="03/06/2011" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Jan 25 2009 12:00AM" not_active_since="01/25/2009" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="3461.54" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="Y" />
<Rate date_changed="Sep 21 2008 12:00AM" not_active_since="09/21/2008" rate="1730.77" emp_pay_type="1" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '5' ,
'<Rates>
<Rate date_changed="Apr 5 2009 12:00AM" not_active_since="04/05/2009" rate="11.73" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
<Rate date_changed="Nov 2 2008 12:00AM" not_active_since="11/02/2008" rate="11.44" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '6' ,
'<Rates>
<Rate date_changed="Feb 22 2009 12:00AM" not_active_since="02/22/2009" rate="17.43" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
UNION ALL
SELECT '7' ,
'<Rates>
<Rate date_changed="Feb 8 2009 12:00AM" not_active_since="02/08/2009" rate="14.00" emp_pay_type="0" emp_work_hours="40.00" rate2="0.00" rate3="0.00" benefit_hours="0.00" exempt_ind="N" />
</Rates>'
)
Now finally we just need to execute your proc.
exec WEB_HR_GetRateHistory @EMP_ID = 3
This returns the same 4 rows as R.P.Rozema's example above.
Not sure what is different but it is doing close to what you want. I assume what you really want to end up with the data for only a single EMP_ID. To accomplish this all you need to do is add
where e.EMP_ID = @EMP_ID
as the last line in your proc.
After adding that it returned only the 1 row for that person.
_______________________________________________________________
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/
May 2, 2012 at 9:36 am
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
OK rant about that over, perhaps it is more frustrating to me because I worked on a huge system once where this type of thing was all over the place. It is horrible to maintain. It took weeks to document how all that stuff fit together.
Sean, I will take exception to this but with an exception (if that makes sense). I have written stored procedures that used temporary tables created outside the procedure. These procedures were called within the context of a larger procedure to complete specific tasks.
One thing I did do in those procedures though was to check if the temporary table existed, if it didn't it would actually create it and use it. I had to do this as we sometimes used these same procedures stand alone. It all worked quite well.
Just so you know, I usually say never say never. You just have to plan and design appropriately.
May 2, 2012 at 9:43 am
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
OK rant about that over, perhaps it is more frustrating to me because I worked on a huge system once where this type of thing was all over the place. It is horrible to maintain. It took weeks to document how all that stuff fit together.
Sean, I will take exception to this but with an exception (if that makes sense). I have written stored procedures that used temporary tables created outside the procedure. These procedures were called within the context of a larger procedure to complete specific tasks.
One thing I did do in those procedures though was to check if the temporary table existed, if it didn't it would actually create it and use it. I had to do this as we sometimes used these same procedures stand alone. It all worked quite well.
Just so you know, I usually say never say never. You just have to plan and design appropriately.
I also don't say never, with exceptions of course. I was brought in as a consultant once to handle a scenario similar to what you described. In my case there were procs nested about 6-7 deep by the time it was all done. There was not a single line of documentation (either inside the code or outside) and no kind of checking to see if these temp tables existed. Oh yeah, the first proc in this line of pasta was over 10k lines long with multiple nested cursors.
I knew somebody would come along and tell me that my absolute is not an absolute. It is an absolute for me because of the potential uber ugliness it can create.
_______________________________________________________________
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/
May 2, 2012 at 9:46 am
So, in my post to go off from this code:
ALTER PROCEDURE [dbo].[WEB_HR_GetRateHistory]
(
@ORG_LEVEL_ID INT,
@EMP_ID INT
)
AS
BEGIN
DECLARE @x XML;
CREATE TABLE #RATE_HISTORY
(
EMP_NAME VARCHAR(255),
EMP_ORGANIZATION VARCHAR(255),
EMP_DEPARTMENT VARCHAR(255),
EMP_DT_TERMINATION DATETIME,
DATE_CHANGED DATETIME,
FROM_PAY_RATE NVARCHAR(MAX),
TO_PAY_RATE NVARCHAR(MAX)
)
INSERT INTO #RATE_HISTORY
(
EMP_NAME,
EMP_ORGANIZATION,
EMP_DEPARTMENT,
EMP_DT_TERMINATION,
DATE_CHANGED,
FROM_PAY_RATE,
TO_PAY_RATE
)
SELECT
e.emp_name,
e.emp_organization,
e.emp_department,
e.emp_dt_termination,
DATE_CHANGED = (select convert(varchar(10), max(x.r.value('@date_changed','DATETIME')), 101) from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)),
FROM_PAY_RATE = (select x.r.value('@rate','DECIMAL(10,5)') from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)), --where x.r.value('@date_changed','DATETIME') = 'min(x.r.value('@date_changed','DATETIME'))',
TO_PAY_RATE = (select x.r.value('@rate','DECIMAL(10,5)') from employee_other CROSS APPLY rate_history.nodes('/Rates/Rate') AS x(r)) --where x.r.value('@date_changed','DATETIME') = 'max(x.r.value('@date_changed','DATETIME'))'
FROM employee e
JOIN employee_other eo ON e.emp_id = eo.emp_id
where e.emp_id = @emp_id and (emp_dt_termination is NULL) and e.emp_organization_id = @ORG_LEVEL_ID
select * from #RATE_HISTORY
END
What would you suggest so I can get just one row for the Data from not only the DDL and Sample Data I provided, but for any employee's rate history, but the return is in one row.
May 2, 2012 at 9:50 am
Sean Lange (5/2/2012)
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
OK rant about that over, perhaps it is more frustrating to me because I worked on a huge system once where this type of thing was all over the place. It is horrible to maintain. It took weeks to document how all that stuff fit together.
Sean, I will take exception to this but with an exception (if that makes sense). I have written stored procedures that used temporary tables created outside the procedure. These procedures were called within the context of a larger procedure to complete specific tasks.
One thing I did do in those procedures though was to check if the temporary table existed, if it didn't it would actually create it and use it. I had to do this as we sometimes used these same procedures stand alone. It all worked quite well.
Just so you know, I usually say never say never. You just have to plan and design appropriately.
I also don't say never, with exceptions of course. I was brought in as a consultant once to handle a scenario similar to what you described. In my case there were procs nested about 6-7 deep by the time it was all done. There was not a single line of documentation (either inside the code or outside) and no kind of checking to see if these temp tables existed. Oh yeah, the first proc in this line of pasta was over 10k lines long with multiple nested cursors.
I knew somebody would come along and tell me that my absolute is not an absolute. It is an absolute for me because of the potential uber ugliness it can create.
I can tell you that mine did NOT go that deep, in fact it only went one level if I remember correctly (it has been over a year since I left the school district). And hopefully I documented it so people following me would know what was going on, again it has been a while.
My only absolute is that there are no absolutes. Been bitten too many times by them. Good design and documentation are really needed.
One of the reasons I did what I did was actually to reduce the complexity of the main procedure. I moved things into logical subprocedures to make the main procedure more manageable. It was originaly designed by she-who-could-walk-on-water-because-she-knew-where-the-rocks-were. Poorly documented and did a heck of a lot of extra processing that was easily combined. Think of it as set-based rbar.
May 2, 2012 at 10:03 am
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
OK rant about that over, perhaps it is more frustrating to me because I worked on a huge system once where this type of thing was all over the place. It is horrible to maintain. It took weeks to document how all that stuff fit together.
Sean, I will take exception to this but with an exception (if that makes sense). I have written stored procedures that used temporary tables created outside the procedure. These procedures were called within the context of a larger procedure to complete specific tasks.
One thing I did do in those procedures though was to check if the temporary table existed, if it didn't it would actually create it and use it. I had to do this as we sometimes used these same procedures stand alone. It all worked quite well.
Just so you know, I usually say never say never. You just have to plan and design appropriately.
I also don't say never, with exceptions of course. I was brought in as a consultant once to handle a scenario similar to what you described. In my case there were procs nested about 6-7 deep by the time it was all done. There was not a single line of documentation (either inside the code or outside) and no kind of checking to see if these temp tables existed. Oh yeah, the first proc in this line of pasta was over 10k lines long with multiple nested cursors.
I knew somebody would come along and tell me that my absolute is not an absolute. It is an absolute for me because of the potential uber ugliness it can create.
I can tell you that mine did NOT go that deep, in fact it only went one level if I remember correctly (it has been over a year since I left the school district). And hopefully I documented it so people following me would know what was going on, again it has been a while.
My only absolute is that there are no absolutes. Been bitten too many times by them. Good design and documentation are really needed.
One of the reasons I did what I did was actually to reduce the complexity of the main procedure. I moved things into logical subprocedures to make the main procedure more manageable. It was originaly designed by she-who-could-walk-on-water-because-she-knew-where-the-rocks-were. Poorly documented and did a heck of a lot of extra processing that was easily combined. Think of it as set-based rbar.
hehe I assumed you didn't come up with anything quite so dreadful. The funny ending of the story is the development team took it out of that massive proc structure and rolled into their .NET code. They said they were doing that because it would help performance. The process went from the usual 30-40 minutes to over 8 hours. 😀 They were satisfied that they did the right thing. I quickly fired them as a client after that. This was a financial system where they also decided to add (nolock) to every single procedure because they were facing locking issues. I am not kidding, they added that hint to somewhere around 2,000 procs. This was a project for a number of their DBA's that lasted a few weeks but it was mandated that every single query use that hint to prevent locking and boost performance. :w00t:
_______________________________________________________________
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/
May 2, 2012 at 10:06 am
Sean Lange (5/2/2012)
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
OK rant about that over, perhaps it is more frustrating to me because I worked on a huge system once where this type of thing was all over the place. It is horrible to maintain. It took weeks to document how all that stuff fit together.
Sean, I will take exception to this but with an exception (if that makes sense). I have written stored procedures that used temporary tables created outside the procedure. These procedures were called within the context of a larger procedure to complete specific tasks.
One thing I did do in those procedures though was to check if the temporary table existed, if it didn't it would actually create it and use it. I had to do this as we sometimes used these same procedures stand alone. It all worked quite well.
Just so you know, I usually say never say never. You just have to plan and design appropriately.
I also don't say never, with exceptions of course. I was brought in as a consultant once to handle a scenario similar to what you described. In my case there were procs nested about 6-7 deep by the time it was all done. There was not a single line of documentation (either inside the code or outside) and no kind of checking to see if these temp tables existed. Oh yeah, the first proc in this line of pasta was over 10k lines long with multiple nested cursors.
I knew somebody would come along and tell me that my absolute is not an absolute. It is an absolute for me because of the potential uber ugliness it can create.
I can tell you that mine did NOT go that deep, in fact it only went one level if I remember correctly (it has been over a year since I left the school district). And hopefully I documented it so people following me would know what was going on, again it has been a while.
My only absolute is that there are no absolutes. Been bitten too many times by them. Good design and documentation are really needed.
One of the reasons I did what I did was actually to reduce the complexity of the main procedure. I moved things into logical subprocedures to make the main procedure more manageable. It was originaly designed by she-who-could-walk-on-water-because-she-knew-where-the-rocks-were. Poorly documented and did a heck of a lot of extra processing that was easily combined. Think of it as set-based rbar.
hehe I assumed you didn't come up with anything quite so dreadful. The funny ending of the story is the development team took it out of that massive proc structure and rolled into their .NET code. They said they were doing that because it would help performance. The process went from the usual 30-40 minutes to over 8 hours. 😀 They were satisfied that they did the right thing. I quickly fired them as a client after that. This was a financial system where they also decided to add (nolock) to every single procedure because they were facing locking issues. I am not kidding, they added that hint to somewhere around 2,000 procs. This was a project for a number of their DBA's that lasted a few weeks but it was mandated that every single query use that hint to prevent locking and boost performance. :w00t:
Is it safe to assume you don't do business with this company on a personal level as well?
May 2, 2012 at 10:09 am
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Lynn Pettis (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
OK rant about that over, perhaps it is more frustrating to me because I worked on a huge system once where this type of thing was all over the place. It is horrible to maintain. It took weeks to document how all that stuff fit together.
Sean, I will take exception to this but with an exception (if that makes sense). I have written stored procedures that used temporary tables created outside the procedure. These procedures were called within the context of a larger procedure to complete specific tasks.
One thing I did do in those procedures though was to check if the temporary table existed, if it didn't it would actually create it and use it. I had to do this as we sometimes used these same procedures stand alone. It all worked quite well.
Just so you know, I usually say never say never. You just have to plan and design appropriately.
I also don't say never, with exceptions of course. I was brought in as a consultant once to handle a scenario similar to what you described. In my case there were procs nested about 6-7 deep by the time it was all done. There was not a single line of documentation (either inside the code or outside) and no kind of checking to see if these temp tables existed. Oh yeah, the first proc in this line of pasta was over 10k lines long with multiple nested cursors.
I knew somebody would come along and tell me that my absolute is not an absolute. It is an absolute for me because of the potential uber ugliness it can create.
I can tell you that mine did NOT go that deep, in fact it only went one level if I remember correctly (it has been over a year since I left the school district). And hopefully I documented it so people following me would know what was going on, again it has been a while.
My only absolute is that there are no absolutes. Been bitten too many times by them. Good design and documentation are really needed.
One of the reasons I did what I did was actually to reduce the complexity of the main procedure. I moved things into logical subprocedures to make the main procedure more manageable. It was originaly designed by she-who-could-walk-on-water-because-she-knew-where-the-rocks-were. Poorly documented and did a heck of a lot of extra processing that was easily combined. Think of it as set-based rbar.
hehe I assumed you didn't come up with anything quite so dreadful. The funny ending of the story is the development team took it out of that massive proc structure and rolled into their .NET code. They said they were doing that because it would help performance. The process went from the usual 30-40 minutes to over 8 hours. 😀 They were satisfied that they did the right thing. I quickly fired them as a client after that. This was a financial system where they also decided to add (nolock) to every single procedure because they were facing locking issues. I am not kidding, they added that hint to somewhere around 2,000 procs. This was a project for a number of their DBA's that lasted a few weeks but it was mandated that every single query use that hint to prevent locking and boost performance. :w00t:
Is it safe to assume you don't do business with this company on a personal level as well?
LOL. Nope I certainly don't.
_______________________________________________________________
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/
May 2, 2012 at 10:26 am
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
This seems like something you are fairly certain about. Is there a reason that this isn't a good idea?
May 2, 2012 at 10:28 am
patrickmcginnis59 (5/2/2012)
Sean Lange (5/2/2012)
Well unless you are doing on the most god awful things I have seen some sql people do...which is to create a temp table and then call a stored proc which depends on it being run within the same connection. That will work but it is an absolutely hideous idea. This is one of the rare times I am totally confident in using an absolute in sql.NEVER NEVER NEVER use temp tables inside of a proc that were declared elsewhere.
This seems like something you are fairly certain about. Is there a reason that this isn't a good idea?
Keep reading the follow up. It is more from personal experience of this kind of thing running haywire than anything else.
_______________________________________________________________
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/
Viewing 15 posts - 151 through 165 (of 194 total)
You must be logged in to reply to this topic. Login to reply