Need help bulding a query in SQL server
-
I have a table “tblItems” . I also have 3 table “attrSFloat”,”attrRFloat” and “attrString” that hold many types of attributes for the items.
For instance:
----------
tblItems
(Id) (name) (qnty)
1 boxA 50
(itemID) (attrID) (value)
1 100 6.5
1 200 4
(itemID) (attrID) (value)
1 300 blue
----------
I have another table that holds up to 20 options for search criteria that is loaded from a webform.
For instance:
----------
tblSearchCr
(attrID1) (value1) (attrID2) (value2) (attrID3) (value3) …………
100 6.5 200 4 300 blue
----------
I need to build a stored procedure that reads the “tblSearchCr” and dynamicly builds the query string that returns all items that meet the specified criteria. I know the SELECT statement will not change but FROM will (add the right number of joins to each table) and the SELECT will. Do I need to use a cursor since I am only looking at one line from “tblSearchCr”? Also if the solution is a bunch of “IF/ELSE” statements then will using up to 20 different search criteria bring my query to a halt? If anyone has an example that I could expand on that would be greatly appreciated.
Thanks for any advice.
Larry
P.S. The value columns in the tblSearchCr are type sql_variant.
-
It should be possible to do such things with dynamic SQL, however, I don't understand your description well enough to be more specific... Is it so that certain field in the table tblSearchCr is always related with the same table? Or does the program have to find out somehow, in which table to look in this particular case?
-
Sorry if my previous post was confusing.The attributes table holds many types of attributes for each item. Example: the item AAA has 3 different attributes in the attributes table one each for height,length and weight. If the user chooses from the webform to search for only height and weight then it puts a search criterea in the tblSearchCr filling in the attributeID and value. then on another page i can access these search criteria via the sessionID and I need to use a sproc that will read this search criterea and build the required query. For example, if I am searching for two options the query would lool like this.
SELECT tblItems.itemName, tblItems.itemDescShort, tblItems.qntyOnHand
FROM tblItems LEFT OUTER JOIN
tblCTSFloat ON tblItems.itemId = tblCTSFloat.itemID LEFT OUTER JOIN
tblCTSFloat tblCTSFloat_1 ON tblItems.itemId = tblCTSFloat_1.itemID
WHERE (tblCTSFloat_1.itemID = @itemID) AND (tblCTSFloat_1.attrID = @attrID1) AND (tblCTSFloat_1.value1 = @value1) AND (tblCTSFloat.itemID = @itemID)
AND (tblCTSFloat.attrID = @attrID2) AND (tblCTSFloat.value1 = @value2)
but if I am searching for only one option the query would look like this.
SELECT tblItems.itemName, tblItems.itemDescShort, tblItems.qntyOnHand
FROM tblItems LEFT OUTER JOIN
tblCTSFloat tblCTSFloat_1 ON tblItems.itemId = tblCTSFloat_1.itemID
WHERE (tblCTSFloat_1.itemID = @itemID) AND (tblCTSFloat_1.attrID = @attrID1) AND (tblCTSFloat_1.value1 = @value1)
The user can select any combination of criterea to search for so that would mean 400 sprocs if I wrote all possible options. I am looking to return all items that have atleast the requested criterea.
-
My suggestion would be to create a temp table of the attributes (attrID and value) from the tblSearchCr table (ignoring non supplied entries). Count the number of rows in that table (for use see later). Left Join this table to each attrSFloat, attrRFloat and attrString table and assuming attrID can only exist in one of those tables, count the number of rows where at least one one of attrSFloat, attrRFloat and attrString is not null and match that count to the count of rows in the temp table.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks you for yor suggestion David, but I think I left out an important part. Yes each attrID can exist in only one of the three atribute tables, but i need to be able to search differently for each type of table. If it is in the attrSFloat(single float) it will be an = statemant, if it is in the attrRFloat(range float) it will be a BETWEEN statemant and if it is in the attrString(string) it will be a LIKE statemant. Is it still possible to build a temp table?
-
OK, lets see if we can define it further.
Assuming following ddl
attrSFloat itemID int, attrID int, value float
attrRFloat itemID int, attrID int, minvalue float, maxvalue float
attrString itemID int, attrID int, value varchar(20)
--temp table
create table #temp (itemID int, attrID int, value varchar(20))
insert into #temp values (1,100,'6.5')
insert into #temp values (1,200,'4')
insert into #temp values (1,300,'blue')
-- count rows in temp table
declare @cnt int
select @cnt = count(*) from #temp
-- now select itemid if each row in temp matches one of attr tables
select t.itemid
from #temp t
left outer join attrSFloat sf
on sf.itemID = t.itemID
and sf.attrID = t.attrID
and sf.value = cast(t.value as float)
left outer join attrRFloat rf
on rf.itemID = t.itemID
and rf.attrID = t.attrID
and cast(t.value as float) between rf.minvalue and rf.maxvalue
left outer join attrString as
on as.itemID = t.itemID
and as.attrID = t.attrID
and as.value like t.value
group by t.itemid
having sum(case
when sf.itemID IS NOT NULL THEN 1
when rf.itemID IS NOT NULL THEN 1
when as.itemID IS NOT NULL THEN 1
else 0 end) = @cnt
Far away is close at hand in the images of elsewhere.
Anon. -
Hmmm... so in the RFloat table there are always 2 records for each item/attribute combination, meaning maximum and minimum value? Or does this table have 2 columns for min and max value?
-
Wow you guys ROCK!! That worked like a champ and much simpler than the path I was heading down. Yes Vladen the RFloat table has a min and max value columns. I see that I did not show that in my first post either(another newbie mistake). I have one more question now on the #temp table. I assume the # is telling SQL to put the table somewhere special since I due not see it in either the user or system tables, do I need to remove this table or is it removed automaticlly after the sproc is finished?
-
temp tables (prefixed with #) are created in the tempdb database with the name you give it plus additional that is added by sql to make it unique. These temp tables are deleted by sql when the connection is dropped but as in all good programming you should do it yourself. So after the last command that uses the temp table do the following
drop table #temp
to delete it
p.s. these types of temp table are for use by the connection only, temp tables that are prefixed by ## (two hash chars) are know as global temp tables (created as you name them) and can be accessed by any process and are also deleted when the connection is dropped.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks David, you have been a big help. As far what Joe was saying I will give a little more information so we can decide if I am complete idiot or this project is worth saving.
First, the actual names of my tables are tblItems(Products), tblCTSFloat(cross table for float), tblCTRFloat(cross table for range float) and so on. I have always added the object type to the beginning of all names(string str, textbox txb, stored procedure csp). This helps me when I actually use the object to make sure I am doing something with the object that it is capable of. This may not be necessary in SQL but when you are using HTML,C#,javascript and SQL together it helps me.
Second, the attributes I am talking about are really features of the product in the items table. For example a custom power supply in the items table might have 20 different types of features(input volts,amps,#outputs,watts) that an engineer would need to be able to search for the product with. I do not want to include this in the items table because there are many types of power supply’s each with different set of features. I also do not want to create a separate table for the criteria for each type of power supply because I want to keep this dynamic.
Third, I believe I have put allot of thought into this data model and came up with the one that best suites my needs. I think that since the only way a user will have access to the data is through the business rules which then access the sproc I am ensuring the integrity of my database.
Fourth, I am happy you have had such a long and successful career. Since I am new to database programming I welcome any advice from the experienced members of the community. Could you explain what you mean by "fall apart in about a year"? Is there some testing that I could do to prove/disprove this? Also when you say "it will run like glue" do you mean right away or after a certain number of records are added? I only have about 5,000 products so that means 100,000 records spread over 3 tables.
-
David, I have been working with the script that you showed me and it was working great until I realized that I was supplying the itemID as part of the search criteria. I want to return all the itemID's that meet the criteria. Any thoughts?
-
Let's see if this helps... I will use David's SQL and only modify it to exclude the ItemID:
--temp table
create table #temp (attrID int, value varchar(20))
insert into #temp values (100,'6.5')
insert into #temp values (200,'4')
insert into #temp values (300,'blue')
-- count rows in temp table
declare @cnt int
select @cnt = count(*) from #temp
-- now select itemid if each row in temp matches one of attr tables
select COALESCE(sf.itemid, rf.itemid,ast.itemid),
sum(case
when sf.itemID IS NOT NULL THEN 1
when rf.itemID IS NOT NULL THEN 1
when ast.itemID IS NOT NULL THEN 1
else 0 end)
from #temp t
left outer join attrSFloat sf
on sf.attrID = t.attrID
and sf.value = cast(t.value as float)
left outer join attrRFloat rf
on rf.attrID = t.attrID
and cast(t.value as float) between rf.minvalue and rf.maxvalue
left outer join attrString ast
on ast.attrID = t.attrID
and ast.value like t.value
group by COALESCE(sf.itemid, rf.itemid,ast.itemid)
having sum(case
when sf.itemID IS NOT NULL THEN 1
when rf.itemID IS NOT NULL THEN 1
when ast.itemID IS NOT NULL THEN 1
else 0 end) = @cnt
What I did is that I replaced the ItemId from temptable with ItemId from the attributes tables; COALESCE finds first occurrence that is NOT NULL. Oh, and I have renamed the alias of attrString to "ast", since "AS" is a reserved keyword. Hope this helps.
BTW, I added the field with CASE to the SELECT, so that I can observe what results I'm getting. Of course, in the final sproc you won't need it - it's there just for testing.
-
There could be one serious flaw in this query... it depends on one assumption, that was not mentioned: No duplicities and no overlapping in range attribute values. That is, it is quite all right when item No. 1 has several records for attribute 300: blue, red, silver...
But, as soon as there are two identical records (i.e. both 'blue') for the same item and attribute, you get wrong results. Well, this particular example is something that should be avoided anyway, but the same will happen if one and the same item and attribute has two overlapping records in the attrRFloat table - e.g. range from 5 to 8 and from 7 to 10 (say, for two sizes of the same product). Then if someone searches for 8 as a value of that attribute,
you have a problem. What the query does, is counting the number of successful joins; it does not distinguish to what table and attribute. If two records with the same product satisfy the condition, then it will count 2 - meaning that even though one of the other criteria was not met, the sum is correct.
Maybe you have special item number for every variant and therefore no such problems, but maybe the same product in various colors, sizes etc. retains one ID - I don't know which is true.
In case you are sure this can never happen, everything is fine... but I have learned that nothing can be assumed automatically, and that certain people tend to be very creative. They tell you today "this will never happen", and introduce a new product in a few months, that will violate the rule - and nobody will tell you about it before it is too late.
-
Thanks for the help Vladen. I see the flaw now. I have went back to my original train of thought and came up with this sproc that works but seems there should be a better way to do it. This only gets the first 3 feild as I add the code for 17 more you can see it will become quite large. Is there any limitations on the size of a sproc? Here is what I have now.
------------------------------
declare @sessionID varchar(60),
@execStr varchar(2000),
@execStr_Select varchar(300),
@execStr_Where varchar(500),
@execStr_From varchar(500),
@type varchar(20),
@id1 int,
@value1 nvarchar(20),
@id2 int,
@value2 nvarchar(20),
@id3 int,
@value3 nvarchar(20),
@id4 int,
@value4 nvarchar(20),
@id5 int,
@value5 nvarchar(20),
@id6 int,
@value6 nvarchar(20),
@id7 int,
@value7 nvarchar(20),
@id8 int,
@value8 nvarchar(20),
@id9 int,
@value9 nvarchar(20),
@id10 int,
@value10 nvarchar(20)
SET @sessionID = '476f2abc-a499-44f9-b6c0-2a3e8843910d'
SELECT @id1=L201ID, @value1=L201V,@id2=L202ID,@value2=L202V
FROM dbo.tblSearchParams
WHERE sessionID = @sessionID
SET @execStr_Select = 'SELECT t.itemName,t.itemDescShort,t.qntyOnHand,t.unitPrice'
SET @execStr_From = 'FROM dbo.tblItems t'
SET @execStr_Where = 'WHERE'
IF @id1 IS NULL
BEGIN
GOTO execQuery
END
ELSE
BEGIN
Select @type = crossTable FROM tblAttrL2 WHERE attrID = @id1
SET @execStr_From =
CASE @type
WHEN 'tblCTSFloat'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTSFloat sfl' + CONVERT(nvarchar(50),@id1) + ' ON ' +
't.itemId = sfl' + CONVERT(nvarchar(50),@id1) + '.itemID'
WHEN 'tblCTRFloat'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTRFloat rfl' + CONVERT(nvarchar(50),@id1) + ' ON ' +
't.itemId = rfl' + CONVERT(nvarchar(50),@id1) + '.itemID'
WHEN 'tblCTString'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTString str' + CONVERT(nvarchar(50),@id1) + ' ON ' +
't.itemId = str' + CONVERT(nvarchar(50),@id1) + '.itemID'
END
SET @execStr_Where =
CASE @type
WHEN 'tblCTSFloat'
THEN @execStr_Where +
' (sfl' + CONVERT(nvarchar(50),@id1) + '.attrID= ' + CONVERT(nvarchar(50),@id1) +
') AND (sfl' + CONVERT(nvarchar(50),@id1) + '.value1= ' + @value1 + ')'
WHEN 'tblCTRFloat'
THEN @execStr_Where +
' (rfl' + CONVERT(nvarchar(50),@id1) + '.attrID= ' + CONVERT(nvarchar(50),@id1) +
') AND (cast(' + @value1 + ' as float) between rfl' + CONVERT(nvarchar(50),@id1) +
'.value1 and rfl' + CONVERT(nvarchar(50),@id1) + '.value2)'
WHEN 'tblCTString'
THEN @execStr_Where +
' (str' + CONVERT(nvarchar(50),@id1) + '.attrID= ' + CONVERT(nvarchar(50),@id1) +
') AND (str' + CONVERT(nvarchar(50),@id1) + '.value1 LIKE ' + @value1 + ')'
END
END
IF @id2 IS NULL
BEGIN
GOTO execQuery
END
ELSE
BEGIN
Select @type = crossTable FROM tblAttrL2 WHERE attrID = @id2
SET @execStr_From =
CASE @type
WHEN 'tblCTSFloat'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTSFloat sfl' + CONVERT(nvarchar(50),@id2) + ' ON ' +
't.itemId = sfl' + CONVERT(nvarchar(50),@id2) + '.itemID'
WHEN 'tblCTRFloat'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTRFloat rfl' + CONVERT(nvarchar(50),@id2) + ' ON ' +
't.itemId = rfl' + CONVERT(nvarchar(50),@id2) + '.itemID'
WHEN 'tblCTString'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTString str' + CONVERT(nvarchar(50),@id2) + ' ON ' +
't.itemId = str' + CONVERT(nvarchar(50),@id2) + '.itemID'
END
SET @execStr_Where =
CASE @type
WHEN 'tblCTSFloat'
THEN @execStr_Where +
' AND (sfl' + CONVERT(nvarchar(50),@id2) + '.attrID= ' + CONVERT(nvarchar(50),@id2) +
') AND (sfl' + CONVERT(nvarchar(50),@id2) + '.value1= ' + @value2 + ')'
WHEN 'tblCTRFloat'
THEN @execStr_Where +
' AND (rfl' + CONVERT(nvarchar(50),@id2) + '.attrID= ' + CONVERT(nvarchar(50),@id2) +
') AND (cast(' + @value2 + ' as float) between rfl' + CONVERT(nvarchar(50),@id2) +
'.value1 and rfl' + CONVERT(nvarchar(50),@id2) + '.value2)'
WHEN 'tblCTString'
THEN @execStr_Where +
' AND (str' + CONVERT(nvarchar(50),@id2) + '.attrID= ' + CONVERT(nvarchar(50),@id2) +
') AND (str' + CONVERT(nvarchar(50),@id2) + '.value1 LIKE ' + @value2 + ')'
END
END
IF @id3 IS NULL
BEGIN
GOTO execQuery
END
ELSE
BEGIN
Select @type = crossTable FROM tblAttrL2 WHERE attrID = @id3
SET @execStr_From =
CASE @type
WHEN 'tblCTSFloat'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTSFloat sfl' + CONVERT(nvarchar(50),@id3) + ' ON ' +
't.itemId = sfl' + CONVERT(nvarchar(50),@id3) + '.itemID'
WHEN 'tblCTRFloat'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTRFloat rfl' + CONVERT(nvarchar(50),@id3) + ' ON ' +
't.itemId = rfl' + CONVERT(nvarchar(50),@id3) + '.itemID'
WHEN 'tblCTString'
THEN @execStr_From +
' LEFT OUTER JOIN tblCTString str' + CONVERT(nvarchar(50),@id3) + ' ON ' +
't.itemId = str' + CONVERT(nvarchar(50),@id3) + '.itemID'
END
SET @execStr_Where =
CASE @type
WHEN 'tblCTSFloat'
THEN @execStr_Where +
' AND (sfl' + CONVERT(nvarchar(50),@id3) + '.attrID= ' + CONVERT(nvarchar(50),@id3) +
') AND (sfl' + CONVERT(nvarchar(50),@id3) + '.value1= ' + @value3 + ')'
WHEN 'tblCTRFloat'
THEN @execStr_Where +
' AND (rfl' + CONVERT(nvarchar(50),@id3) + '.attrID= ' + CONVERT(nvarchar(50),@id3) +
') AND (cast(' + @value3 + ' as float) between rfl' + CONVERT(nvarchar(50),@id3) +
'.value1 and rfl' + CONVERT(nvarchar(50),@id3) + '.value2)'
WHEN 'tblCTString'
THEN @execStr_Where +
' AND (str' + CONVERT(nvarchar(50),@id3) + '.attrID= ' + CONVERT(nvarchar(50),@id3) +
') AND (str' + CONVERT(nvarchar(50),@id3) + '.value1 LIKE ' + @value3 + ')'
END
END
execQuery:
SET @execStr = @execStr_Select + ' ' + @execStr_From + ' ' + @execStr_Where
print @execStr
EXEC (@execStr)
-------------------------------------------------
-
So after writing all that code, you still don't believe Joe Celko that the design is seriously flawed ?
The solution is to go back to the database design, not to copy & paste 20 near identical code blocks into a sproc from hell.
You asked Joe 2 things, why it would run like glue and why it would fall apart in a year or less.
The fact that you're having to build dynamic SQL and having to do so with so much unwieldy, difficult to maintain T-SQL code, answers your own questions. What are the chances that you (or a co-worker) can maintain that code a year from now ?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply