July 8, 2014 at 3:45 pm
I have a couple of tables that look like this:
CREATE TABLE Colors
(
Id INT NOT NULL,
ColorName VARCHAR(25)
)
CREATE TABLE Cars
(
ID INT NOT NULL,
ColorId INT NOT NULL, --FK TO Colors
CarName VARCHAR(25)
)
CREATE TABLE CarSearch
(
ID INT NOT NULL,
SearchName VARCHAR(25)
)
CREATE TABLE CarSearchCriteria
(
ID INT NOT NULL,
CarSearchId INT NOT NULL, --FK to CarSearch
ColorId INT NOT NULL, --FK to Colors
CarId INT NULL --FK to Cars
)
CarSearch contains records for searches to be performed on the Cars and Colors tables, using CarSearchCriteria to contain the search criteria.
Rules -
* A search may have more than one set of search criteria, but the final result set must be the unioned set from all criteria.
* At the very least, ColorId must be provided for CarSearchCriteria.
* If CarId is not provided for CarSearchCriteria, it should be assumed to search for all cars with that associated ColorId.
(This is just a dumbed-down analog to my actual tables, so don't question the business sense of this, please)
July 8, 2014 at 4:52 pm
Are we supposed to do your work? Or do you have something done already?
July 8, 2014 at 4:58 pm
Hey, thanks for the smart-*** remark. I'm looking for alternatives to using a cursor here. If you have an idea, I'd like to hear it.
July 8, 2014 at 5:08 pm
It seems that you need joins or semi-joins (EXISTS).
I can't see the complexity and you didn't even provide sample data and expected results. You've been here for some time and you should know the good practices.
If you already have a solution using a cursor you could post it as well, that would give us an idea on what you're trying to do. The point is to show that you have tried something.
July 8, 2014 at 6:22 pm
Are we supposed to do your work? Or do you have something done already?
+1
Hey, thanks for the smart-*** remark. I'm looking for alternatives to using a cursor here. If you have an idea, I'd like to hear it.
First, just because you include the text, "... without cursors and/or dynamic SQL" in your post title does not all of a sudden make other people want to do your work. With all due respect, I think you missed Louis' point.
If you were to post something like this:
I have this requirement to blah blah...
Here's some sample DDL:
USE TempdbGO
IF OBJECT_ID('tempdb..HISTORY') IS NOT NULL DROP TABLE HISTORY;
CREATE TABLE dbo.yourtable<..ddl>);
INSERT INTO dbo.yourtable VALUES (<some values>);
... and this is what I need:
WITH what_I_need (CUSTOMER, PLANNBR, SUBPLAN, STARTDATE, ENDDATE)AS
(SELECT 9111111, 'H1111LAC','006', '2014-01-01', '2014-05-31' UNION ALL
SELECT 9111111, 'H1111OC','010', '2014-06-01', '2999-12-31' )
SELECT * FROM what_I_need ;
Here's what I've tried...
code...
... if you posted something like that you would have a solution already.
-- Itzik Ben-Gan 2001
July 8, 2014 at 8:44 pm
With out DDL (provided), sample data, expected results, and what you have already done and/or tried I wouldn't even know where to start. Remember you see what you have to work with, we don't.
You need to help us help you or all we can do pull stuff out of our you know what's and it may not be very helpful.
July 9, 2014 at 1:10 am
Luis Cazares (7/8/2014)
Are we supposed to do your work?
+1
Or do you have something done already?
Please share if you have already done something.
There are options like CTE, Self - joins, Do - While loop instead of Cursor usage.
Thanks
July 9, 2014 at 9:06 am
Hardy21 (7/9/2014)
Luis Cazares (7/8/2014)
Are we supposed to do your work?+1
Or do you have something done already?
Please share if you have already done something.
There are options like CTE, Self - joins, Do - While loop instead of Cursor usage.
While loops are certainly an option, just not a better option. A well written cursor can run faster than a while loop.
And there's nothing wrong on correctly coding dyanamic sql or cursors where they apply and with the proper cautions.
July 9, 2014 at 2:05 pm
One of the problems with "dumbed down" examples is that the solutions don't necessarily apply well to the full-scale real-world problem, often due to surprisingly simple differences that suddenly render the original solution totally worthless because the real-world scenario breaks one of the seemingly reasonable assumptions associated with the dumbed down version of the problem. Also, you didn't exactly state what kind of result you were looking for - just that you were looking for cursor alternatives. That's kind of like asking for alternatives to a perfectly good band-aid. A cursor is not necessarily evil, but given that we don't even know what kind of cursor code you're looking at, I don't see how anyone could possibly give you any useful advice. As we don't have either a statement of the problem or the alleged evil cursor to look at, we've nothing to go on. As many others have stated, help us help you...
TheGreenShepherd (7/8/2014)
I have a couple of tables that look like this:
CREATE TABLE Colors
(
Id INT NOT NULL,
ColorName VARCHAR(25)
)
CREATE TABLE Cars
(
ID INT NOT NULL,
ColorId INT NOT NULL, --FK TO Colors
CarName VARCHAR(25)
)
CREATE TABLE CarSearch
(
ID INT NOT NULL,
SearchName VARCHAR(25)
)
CREATE TABLE CarSearchCriteria
(
ID INT NOT NULL,
CarSearchId INT NOT NULL, --FK to CarSearch
ColorId INT NOT NULL, --FK to Colors
CarId INT NULL --FK to Cars
)
CarSearch contains records for searches to be performed on the Cars and Colors tables, using CarSearchCriteria to contain the search criteria.
Rules -
* A search may have more than one set of search criteria, but the final result set must be the unioned set from all criteria.
* At the very least, ColorId must be provided for CarSearchCriteria.
* If CarId is not provided for CarSearchCriteria, it should be assumed to search for all cars with that associated ColorId.
(This is just a dumbed-down analog to my actual tables, so don't question the business sense of this, please)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2014 at 3:25 pm
I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id or multiples, but this should handle either.
SELECT DISTINCT c.*
FROM Cars c
INNER JOIN CarSearchCriteria scs ON
scs.CarId = c.ID OR
(scs.CarId IS NULL AND C.ColorId = scs.ColorId)
WHERE
scs.CarSearchId IN (<list_of_search_id(s)_to_do>)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 10, 2014 at 3:51 pm
That has some nasty potential performance with the OR in the JOIN. As we don't know much about the actual desired result, I decided making a guess such as that might be counterproductive in the long run.
ScottPletcher (7/10/2014)
I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id or multiples, but this should handle either.
SELECT DISTINCT c.*
FROM Cars c
INNER JOIN CarSearchCriteria scs ON
scs.CarId = c.ID OR
(scs.CarId IS NULL AND C.ColorId = scs.ColorId)
WHERE
scs.CarSearchId IN (<list_of_search_id(s)_to_do>)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2014 at 4:09 pm
sgmunson (7/10/2014)
That has some nasty potential performance with the OR in the JOIN. As we don't know much about the actual desired result, I decided making a guess such as that might be counterproductive in the long run.ScottPletcher (7/10/2014)
I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id or multiples, but this should handle either.
SELECT DISTINCT c.*
FROM Cars c
INNER JOIN CarSearchCriteria scs ON
scs.CarId = c.ID OR
(scs.CarId IS NULL AND C.ColorId = scs.ColorId)
WHERE
scs.CarSearchId IN (<list_of_search_id(s)_to_do>)
"Nasty" is relative. I'd take any query plan I get from that vs. using a cursor :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 10, 2014 at 4:31 pm
Doesn't that depend on the cursor? They aren't always evil, ... they're just often not necessary... However, that said, I'm still waiting on the OP to come back with something more cogent about the desired results before I'll commit to code..
ScottPletcher (7/10/2014)
sgmunson (7/10/2014)
That has some nasty potential performance with the OR in the JOIN. As we don't know much about the actual desired result, I decided making a guess such as that might be counterproductive in the long run.ScottPletcher (7/10/2014)
I must be missing something, because with the limited search criteria so far it looks straightforward. I can't tell whether they will pass in a single search id or multiples, but this should handle either.
SELECT DISTINCT c.*
FROM Cars c
INNER JOIN CarSearchCriteria scs ON
scs.CarId = c.ID OR
(scs.CarId IS NULL AND C.ColorId = scs.ColorId)
WHERE
scs.CarSearchId IN (<list_of_search_id(s)_to_do>)
"Nasty" is relative. I'd take any query plan I get from that vs. using a cursor :-).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply