3 tables, one query?
-
My db has 3 types of Contacts, ea with its own tbl.
Customer table: ContactID, CustomerID
Media table: ContactID, MediaID
Group table: ContactID, GroupID
There is a Contact tbl that has an Identity column, ID, plus many more fields. A one to many relationship exists (one Customer can have several contacts, etc).
I am looking for ideas on SPs that return a recordset of Contacts for one of the 3 types (Customer, Media, Group). I can write 3 SPs like:
CREATE PROC CustomerContacts(@ID)
SELECT Last, First, ...
FROM Contact
JOIN Customer ON ContactID = Contact.ID
WHERE CustomerID = @ID
ORDER BY Last, First, ...
However, I would prefer the logic for Contact SELECT/ORDERBY be in one and only one SP.
One idea (but I don't know how to code it):
Create a sp that accepts a table or table name as a param, then dynamically calc the table and column to use in the JOIN. Then make 3 small SPs that simply call the 1st SP with the needed param(s)
I am looking for other ideas, with pros/cons (easy to maintain, execution speed, etc). I am looking for sample code on anything advanced or tricky.
Greg
-
I'd advise you to use separate sp's. It is perfectly fine to use your application to determin the sp to use. But if you must... this is how you might do it. Use CASE statements if you want to use "type" specific columns. (CASE @type WHEN 1 THEN custName WHEN 2 THEN mediaName WHEN 3 THEN groupName END AS typeName). You may also use CASE/IF
BEGIN-END blocks for 3 different queries.
CREATE PROCEDURE uspContactSearch
@type tinyint, -- 1 cust, 2 media, 3 group
@typeID int
AS
SELECT Last, First, ...
FROM Contact
LEFT JOIN Customer ON Customer .ContactID = Contact.ID
LEFT JOIN Media ON Media.ContactID = Contact.ID
LEFT JOIN [Group] ON [Group].ContactID = Contact.ID
WHERE @type = 1 AND customerID = @typeID
OR @type = 2 AND mediaID = @typeID
OR @type = 3 AND groupID = @typeID
ORDER BY Last, First, ...
Last recomendation is not to use SQL key-words for your user-defined objects. Group can easily be changed to Groups and read more english-like. SELECT * FROM Groups
-
Some good ideas.
I failed to mention that CustomerID is a Customer.ID identity, that MediaID is a Media.ID identity, and that GroupID is a Group.ID identity. Thus the proposed logic wont work (@type = 1 AND customerID = @typeID OR @type = 2 AND mediaID = @typeID OR @type = 3 AND groupID = @typeID).
How about your CASE idea. Will soemthing like this work?
SELECT Last, First, ...
FROM Contact
CASE type
WHEN 1 THEN JOIN Customer ON Customer .ContactID = Contact.ID
WHEN 2 THEN JOIN Media ON Media.ContactID = Contact.ID
WHEN 3 THEN JOIN [Group] ON [Group].ContactID = Contact.ID
ORDER BY Last, First, ...
Or how about passing the tbl and col to join on into the sp?
CREATE PROCEDURE uspContactSearch
@tbl Table, -- CustomerContact, MediaContact, GroupContact
@typeID Column, -- CustomerID, MediaID, GroupID
@ID int
AS
SELECT Last, First, ...
FROM Contact
JOIN tbl ON tbl.ContactID = Contact.ID
WHERE tbl.typeID = ID
ORDER BY Last, First, ...
-
First, I would recommend that you look at this article here:
http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
What you are doing seems to me to be an example of what he talks about. However, back to your question.
The easiest to maintain method would be to create a dynamic SQL statement, and execute that statement. Using your original example, we would have something like:
CREATE PROC ContactsByIDAndType
@ID int,
@Type tinyint
AS
BEGIN
Declare @SQL Varchar(8000)
Declare @tblName varchar(16)
If @Type = 1 Set @TblName = 'Customer'
If @Type = 2 Set @TblName = 'Media'
If @Type = 3 Set @TblName = 'Group'
Set @SQL = 'SELECT Last, First, ...
FROM Contact INNER JOIN [' + @TblName + '] ON ContactID = Contact.ID
WHERE ' + @TblName + 'ID = @ID
ORDER BY Last, First, ...'
EXEC (@SQL)
The alternate form would be to include all of the SQL statements that you would have in three separate stored procs (one for each table) in one stored proc, with IF @Type = 1 BEGIN END around each of the SELECT statements.
One additional idea - have three stored procs, and one central stored proc that conditionally executes one of the three individual procs based on a parameter you pass, e.g:
IF @Type = 1 EXEC usp_GetContactsForCustomer @ID
IF @Type = 2 EXEC usp_GetContactsForMedia @ID
IF @Type = 3 EXEC usp_GetContactsForGroup @ID
Hopefully, some of this will help.
-
Thanks for the ideas.
I looked at that link and see he is refering to something different. My db has one-to-many relationships (3 of them) to address, vs. a lookup (ie., one to one) table(s). Mainly, a given customer can have many contacts, a given Media can have many contacts, and a given Group can have many contacts. Thus the tables CustomerContacts, MediaContacts, and GroupContacts, respectively.
The idea of building the whole query as as string would work. Cons: (a) execution speed (can't be pre-compiled), (b) error detection (none at design/pre-compile/save time, only avail. at run-time), (c) IDE syntax highlighting. But otherwise seems to meet the requriements.
Regarding the "have three stored procs" stmt, the application will always know what type of table is being queried (Customer, Media or Group), and can thus call the specific sp. The only reason why 1 sp to handle all 3 tables came up was because it was a proposed method to reuse the logic needed to select/order the Contact columns.
To the point regarding "The alternate form", how can this be coded? It is the direction I was leaning towards and asked about in my last post ("How about your CASE idea", and "Or how about passing the tbl and col"). If this is possibly in SQL, then I'd consider it.
-
The idea of building the whole query as as string would work. Cons: (a) execution speed (can't be pre-compiled), (b) error detection (none at design/pre-compile/save time, only avail. at run-time), (c) IDE syntax highlighting. But otherwise seems to meet the requriements.
And the other con would be security.
--------------------
Colt 45 - the original point and click interface
-
First off, I know this link gets thrown around a lot, but it is good info:
http://www.sommarskog.se/dynamic_sql.html
For many of his reasons I'd recommend using exec sp_executesql @sql instead of exec(@sql) in the dynamic example... it's far more likely that the query plans will be reused. For your case option, let's see if this will work for you. I'm not sure how your application will provide what type of contact info you're requesting to the SP, so I'm assuming something like this:
CREATE PROC CustomerContacts @ID int, @TypeFlag varchar(3) As
IF @TypeFlag = 'CUS'
BEGIN
<code>
END
ELSE IF @typeflag='MED'
BEGIN
<code>
END
ELSE --implied typeflag='GRP'
BEGIN
<code>
END
This would let you have all of your code in one SP, although I'd be sure that it gets run with several instances of each type during testing to make sure it doesn't skew the execution plan towards one value.
Brian
-
That link has some excellent tips on dyn sql. Thanks.
Your IF/ELSE blocks make sense to me but I don't think they solve the problem. The problem is that the sql to sort/order/group the contacts is complex. I want to write that logic once, then use it 3 times (Customer, Media, Group). The reuse could be located in one general sp or in 3 separate SPs, I'm okay with either.
Below is the functionality that I want, shown with 3 separate SPs.
CREATE PROC QueryContact(@t Table, @C Column, @ID int) AS --Can I pass tables/columns objects into an sp?
SELECT Firstname,Lastname,...
FROM Contact
WHERE ContactID IN(SELECT(ContactID FROM @t WHERE @C=@ID))
ORDER BY Firstname,Lastname,...
CREATE PROC QueryCustomerContact(@ID int) AS
QueryContact(CustomerContact, CustomerContact.CustomerID, @ID)
CREATE PROC QueryMediaContact(@ID int) AS
QueryContact(MediaContact, MediaContact.MediaID, @ID)
CREATE PROC QueryGroupContact(@ID int) AS
QueryContact(GroupContact, GroupContact.GroupID, @ID)
Another idea -- Can I pass a record/result set into a SP? The above could be re-crafted as shown below. I don't get quite as much reuse because each sp of the 3 has a SELECT that is the basically the same, with diff table /column tbl names substituted. But it does allow the complext aspect of the query (sort/ordering/group the Contact resultset) to be reused.
CREATE PROC QueryContact(@r RecordSet) AS
SELECT Firstname,Lastname,...
FROM Contact
WHERE ContactID IN(r)
ORDER BY Firstname,Lastname,...
CREATE PROC QueryCustomerContact(@ID int) AS
QueryContact(SELECT (ContactID FROM CustomerContact WHERE CustomerID=ID)))
CREATE PROC QueryMediaContact(@ID int) AS
QueryContact(SELECT ContactID FROM MediaContact WHERE MediaID=ID))
CREATE PROC QueryGroupContact(@ID int) AS
QueryContact(SELECT ContactID FROM GroupContact WHERE GroupID=ID))
-
You've got a few options here, all of which will work. First, you can create dynamic SQL, which should be your last resort. Second, you can create separate SELECT statements, one for each table, and put them in their own stored procedure. Last, you can create separate SELECT statements, one for each table, and use the IF/Then/Else blocks (as described by others in this thread) to allow them to exist in one stored procedure (you would then determine which block to run based off of an input parameter).
-
I agree. I don't think the problem has been solved in this thread (w/o using Dynamic SQL), how about you?
Being a newbie to SQL, I don't know how to code a solution that meets the requirements. Any code that _I_ submitted to the thread was psudo code. The code that others contributed to the thread either doesn't meet the requriements or wouldn't work.
Take this idea. It does not meet the requirements because the Contact SELECT (which non-trivial in real life) is repeated 3 times.
CREATE PROC QueryCustomerContact(@ID Int) AS
SELECT Last, First, ...
FROM Contact
JOIN Customer ON ContactID = Contact.ID
WHERE CustomerID = @ID
ORDER BY Last, First, ...
CREATE PROC QueryMediaContact(@ID Int) AS
SELECT Last, First, ...
FROM Contact
JOIN Media ON ContactID = Contact.ID
WHERE MediaID = @ID
ORDER BY Last, First, ...
CREATE PROC QueryGroupContact(@ID Int) AS
SELECT Last, First, ...
FROM Contact
JOIN Group ON ContactID = Contact.ID
WHERE GroupID = @ID
ORDER BY Last, First, ...
Take this idea. It too does not meet the requirements because the Contact SELECT is repeated 3 times.
CREATE PROC QueryContacts(@type Int, @ID Tnt) AS
IF type=1 BEGIN
SELECT Last, First, ...
FROM Contact
JOIN Customer ON ContactID = Contact.ID
WHERE CustomerID = @ID
ORDER BY Last, First, ...
END
IF type=2 BEGIN
SELECT Last, First, ...
FROM Contact
JOIN Media ON ContactID = Contact.ID
WHERE MediaID = @ID
ORDER BY Last, First, ...
END
IF type=3 BEGIN
SELECT Last, First, ...
FROM Contact
JOIN Group ON ContactID = Contact.ID
WHERE GroupID = @ID
ORDER BY Last, First, ...
END
So, ... I'm still looking for ideas on how the Contact SELECT logic doesn't have to be repeated. If the ideas that I suggested can be written in SQL then how so?
-
All of the solutions suggested will work based off of what you've posted as your requirements. Why do you think you need to have it all in one statement? You said in one of your posts 'The reuse could be located in one general sp or in 3 separate SPs, I'm okay with either.'
If you are worried about adding overhead by having the SELECT contacts statement repeated, you don't have to be. If you have your select statements in an IF/THEN/ELSE block, SQL Server will not execute all 3 SELECTS. It will only execute the 1 SELECT that meets the criteria for the IF block. Using the conditional logic allows you to consolidate the three similar SELECTS from your contacts table into one stored procedure for easy maintenance. This way, if the requirements for this SELECT change, you only need to change one stored procedure.
If we are not talking apples to apples here, you may need to post the DDL for all of the tables involved, plus an example of how you want the data to appear.
-
I think we are not talking apples & apples, but it doesn't appear to be a DDL issue. Yes, the posted solutions will return the correct results. And many good ideas have been contributed. I am not concerned about execution speed/overhead at this pt. I am trying to learn how to not repeat (or at least minimize) SQL logic. I'm new to SQL, but as developers we try to not repeat logic because it makes future maintenance difficult.
Let us assume that the Contact SELECT is not only non-trivial, as mentioned previously, but is, oh, say, a ridiculous 200 lines long. Aside from this being an indication that the design is critically flawed, from a maintenance standpoint it sure would be nice if I could write, tweak, adjust and maintain that SELECT stmt once vs. 3 times, no?
Ah, but how can I do this in SQL? If we were writing in BASIC or something we would make a Function and pass in the variables, then call the Function 3 times (Customer, Media, Group). But we are talking SQL here. Is there a way to 'call' one Contact SELECT stmt for 3 types of contacts?
-
The only way to make this work with one statement would be to use dynamic SQL; however, this does not mean, that from a maintenance perspective, that it would be easily maintained. One of the problems with dynamic SQL is that it is difficult to debug and troubleshoot. Think about how you would go about debugging a 'ridiculous' 200 line long SELECT statement via dynamic SQL. That would be a nightmare.
I understand where you are coming from as far as code reuse and maintenance as I was once a developer also. If there is one thing that I've learned in transition from a developer to a DBA is that you have to change the way you think about things. For example, as a developer, you would be concerned about code reuse where as a DBA, you need to be worried about execution plan reuse. While it would be nice to have more code reuse, execution plan reuse has a much larger overall impact on how your application will perform.
Let's unpack this a little more. Say you were to create a stored procedure that would accept a table name and use that table name to dynamically build and execute a join between a static table and the parameterized table. The very first time this SP would execute, SQL Server would compile it and store its execution plan in cache (this is something that you could watch using Profiler). You could then run the same SP, but pass in the second table name and you would see, through Profiler, that SQL Server finds and re-uses the same execution plan. This will result in a full table scan of the parameterized table as the execution plan was created for a different join. This means that the dynamic SP will only run efficiently for the table/join combination that was used when the execution plan was created.
If you watch the execution plan reuse on a stored procedure using the if/else blocks, you will see the that the execution plan is again compiled once during the first use, but subsequent cache hits result in index scans/seeks rather than table scans. While you could get around the behavior of the dynamic SP by forcing recompiles, that defeats the purpose of caching execution plans for reuse.
Stored procedures should be specific in the tasks that they accomplish. With this said, I would recommend creating 3 distinct stored procedures for your original scenario regardless of how many lines of code are present. While it is sometimes nice to be able to group similar tasks into a stored procedure for reuse in the end it is easier to maintain your stored procedures when they perform specific tasks.
Back to the maintaining of stored procedures and the possible hassle redundant code may cause. This, to me, is a documentation and/or specification issue. If you have 3 stored procedures that perform SELECT statements that join one common table with 3 other tables and changing the way one SELECT works requires that you change the other similar SELECTS, you need to have that documented somewhere. But then again, if you ask 10 DBA’s this question, you would probably get 10 different answers. This one is just my 2cents……
-
Very well said. I get it, Thanks! Closed.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply