August 16, 2011 at 2:40 pm
I am trying to write a stored procedure that has two distinct query's and i want to insert the results into one temp table and return the temp table
But what i have so far just creates columns on separate rows. Can anyone explain to me how i am misunderstanding what i am doing.
USE [OpiAmber]
GO
/****** Object: StoredProcedure [dbo].[sproc_Report_OfficePatientDrugCount] Script Date: 08/16/2011 15:10:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sproc_Report_OfficePatientDrugCount] (
@BeginDateRange as Date,
@EndDateRange as Date,
@Offices as XML)
AS
BEGIN
CREATE TABLE #Test (
OfficeID INT,
PatientCount INT ,
PatientCountUnique INT,
ItemCount INT ,
ItemCountUnique INT)
INSERT INTO #Test (OfficeID, PatientCount, PatientCountUnique)
SELECT Orders.OfficeID, Count(Orders.OrderID) AS CountOfOrders, COUNT(DISTINCT orders.patientid) AS PatientCountUnique
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Offices ON Orders.OfficeID = Offices.OfficeID
WHERE Offices.OfficeID in
(SELECT T.c.value('@ID','integer') AS OfficeID FROM @Offices.nodes('/Root/Key') as T(c))
and (orders.OrderCreationTimestamp between @BeginDateRange and @EndDateRange) AND (OrderItems.OrderItemUnits > 0)
GROUP BY Orders.OfficeID
INSERT INTO #Test(ItemCount, ItemCountUnique)
SELECT COUNT(OrderItems.OrderItemID) as ItemCount,COUNT(DISTINCT OrderItems.OrderItemID)AS ItemCountUnique
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Offices ON Orders.OfficeID = Offices.OfficeID
WHERE Offices.OfficeID in
(SELECT T.c.value('@ID','integer') AS OfficeID FROM @Offices.nodes('/Root/Key') as T(c))
and (orders.OrderDeliveryTimestamp between @BeginDateRange and @EndDateRange) AND (OrderItems.OrderItemUnits > 0)
GROUP BY Offices.OfficeID
Select * From #Test
drop table #test
END
August 16, 2011 at 3:04 pm
Based on your description, the code does exactly what you're looking for: "two distinct query's and i want to insert the results into one temp table".
Separate INSERT statements will add individual rows.
The statement itself look identical except for the values returned. Based on the inner join predicate it shouldn't matter whether you group by Orders.OfficeID or Offices.OfficeID. So you basically could merge both statements into one.
But the question is: what is the output you're looking for? Maybe separate rows are exactly what is needed.
Please explain a little more detailed what the current result is and what you expect.
August 16, 2011 at 3:06 pm
You have two insert statements.
INSERT INTO #Test (OfficeID, PatientCount, PatientCountUnique)
and
INSERT INTO #Test(ItemCount, ItemCountUnique)
They don't insert into the same columns. You will get a table that has 5 columns. 3 columns with data from the first insert and the other 2 columns from the second insert. What I don't understand is why you are doing two insert statements. They have the exact same join conditions. Are you really wanting all 5 columns of information as the result of your stored proc? Did you try just adding the other two columns to your first insert?
INSERT INTO #Test (OfficeID, PatientCount, PatientCountUnique)
SELECT Orders.OfficeID, Count(Orders.OrderID) AS CountOfOrders, COUNT(DISTINCT orders.patientid) AS PatientCountUnique,
COUNT(OrderItems.OrderItemID) as ItemCount,COUNT(DISTINCT OrderItems.OrderItemID)AS ItemCountUnique
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Offices ON Orders.OfficeID = Offices.OfficeID
WHERE Offices.OfficeID in
(SELECT T.c.value('@ID','integer') AS OfficeID FROM @Offices.nodes('/Root/Key') as T(c))
and (orders.OrderCreationTimestamp between @BeginDateRange and @EndDateRange) AND (OrderItems.OrderItemUnits > 0)
GROUP BY Orders.OfficeID
Does that do what you want?
_______________________________________________________________
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/
August 16, 2011 at 3:08 pm
I am sorry let me go back to my specs before i waste anymore of anyone's time.
August 16, 2011 at 3:14 pm
ahhh missed that. so for one set you want only 3 pieces of information and for the the other set you want 2? Still not really sure what the output you are expecting.
_______________________________________________________________
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/
August 16, 2011 at 3:18 pm
@sean yeah its two different timestamps in the database that will in turn return 2 different results because patients may not necessarily have had orders create in the say same time frame. But the timestamps coming into the sproc are the same time frame to query the data. I am trying to just get everything back in to one temp table so i can return a datatable back to me .NET app.
August 16, 2011 at 3:22 pm
Looks like you did that. You inserted some data into a single table with two different insert statements. If you want to be able to get a hold of that data you will need your select as the last statement in your sproc or make an output parameter. Your temp table will be disposed when you dispose of your connection (which should be immediately).
_______________________________________________________________
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/
August 16, 2011 at 4:23 pm
I wasn't clear enough i am sorry. I need it all to go on one row. I guess i could use 3 temp tables, and the results of the 2 and populate the third accordingly and drop them all at the end. I was just wondering if i have two completely different select query's, for example.
Create table #Test
(
colA int,
colB int,
colC int,
colD int,
colE int
)
INSERT INTO #Test(colA, colB, colC)
Select A, B C
FROM Table a innerjoin Table b
WHERE (some logic)
INSERT INTO #Test (colD, colE)
Select D, E
FROM Table C innerjoin Table D
WHERE (some logic)
Select * from #Test
drop table #Test
I will get back
colA colB colC colD colE
val1 val2 val3 null null
null null null val4 val5
Which i understand this happens because i am inserting two rows but i really just want like a compounded single insert with two utterly separate queries, with no knowledge of one another.
This may not be my exact situation but was my initial response to solve my problem.
Would i create 3 temp tables for this situation or is there a way to combine them into one insert into statement.
Also i was wondering based upon the information what do you whink would be the most elegant way to accomplish this problem ?
Thanks Sean
August 17, 2011 at 7:08 am
It really sounds like you want two result sets? Maybe you should just create two stored procs? One for each resultset. Would certainly keep it simple. I would suggest that you do not try to make a single stored procedure return two completely different datasets like what you are doing. You code that consumes this data will be a lot simpler with two separate stored proc calls too.
_______________________________________________________________
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/
August 17, 2011 at 7:48 am
Since returning these to separate sets of data is required for one report i was really trying to keep it all contained in one stored procedure. Since the amount of stored procedures in the application are growing rapidly. I have to figure out how in SQL to be able to get back all this information, into one data set. Maybe i could create a view with one data set and join on it ?
It would just be nice if my one stored procedure had a way to go and get 2 separate data sets and merge them into one for the stored procedure to return. That would be more maintainable down the line for everything to be centralized like that.
August 17, 2011 at 8:06 am
You could do it in a single dataset but it is pretty craptastic. You would have to make your temp table allow for nulls in all columns. Include a rowNumber column. Then get a count from each result set and insert default values into your temp table for the larger of the two results. Then in your query use a rowcount and update your temp table joining on rowcount for each of your other two results.
While this would work it is god awful. Combining disconnected data is just plain nasty and frought with errors. You will spend WAY more time trying to figure out how to fit that square peg into the round hole than just using two datasets. Your replacement will likely want to hunt you down and punch in the face for making this so overly complicated and difficult to maintain. Even if you manage to squash them together you are going to add a bunch of extra logic to handle "empty rows" when the columns for the current section are empty and the other section has more rows. Don't over think this and make it really awful in the name of "it's cool". 😀
_______________________________________________________________
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/
August 17, 2011 at 8:07 am
And you can have a single stored proc return two result sets. Look into table valued parameters. You have to set it up as type first but then you can return more than 1 result set pretty easily.
_______________________________________________________________
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/
August 17, 2011 at 8:31 am
"Don't over think this" I think i have that problem every day, i guess it would be best to return both data sets in 2 sprocs and handle displaying that on the other side. As long as both sets are grouped and sorted by the same ID. It should work.
August 17, 2011 at 8:41 am
LOL We are all guilty of over thinking. It is part of what makes our type of people the way we are. If we didn't try to innovate we would still be using punch cards. 😛 Didn't mean to sound argumentative, I have been wrestling with rewriting a vendor application that is so overly complicated I want to shoot the guy who wrote it. Makes me want to take out my eyeballs and peel them to ease the pain.
Good luck with your report. Let us know if we can help with 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/
August 17, 2011 at 8:46 am
That is no problem good luck with rewriting your application. I am slowly gaining traction at this, some days in bounds some weeks are a crawl.
That is just how it goes 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply