Complex Stored Procedure with multiple conditionals

[lithuin]

Hello,

I've got a MS SQL procdure to write that i sa bit above my head. Any help would be greatly appreciated. I am working on this for a web site I'm building, and the DB guy at the company I'm working for works mostly in RPG, so it's up to me.

I'm really only working with one table in this query.
I am passing in four parameters. The first three are Group, Category, and Class. An item cannot have a Category with out a Group, nor a class without a category. This is already verified, so doesn't need any error checking.

The first three are pretty straighforward. This is how I was building the SQL in c# for testing. This should explain what I want to do better than I can:

string sqlStatement = "Select OrderCode From itemMaster";
if (productGroup != "")
{
    sqlStatement = sqlStatement + " WHERE [Group]='" + productGroup + "'";
    if (productCategory != "")
    {
        sqlStatement = sqlStatement + " AND [Category]='" + productCategory + "'";
        if (productClass != "")
        {
            sqlStatement = sqlStatement + " AND [Class]='" + productClass + "'";
        }
    }
}

That part of it is not too bad. The real problem is the fourth parameter. There is a finish field in the itemMaster that contains a single digit int, which stands for a particular finish. The fourth parameter is the concatination of finishes that should be included in the search.

For example, if the parameter was "24039", that would indicate that the finishes 2, 4, 3, and 9 should be included in the search. A zero would be ignored. I understand the substring function in SQL, but I don't understand how conditionals in SQL work well enough to do this.

Any pointer in the right direction would be greatly appreciated!

 

[lithuin]

Just to clarify, all I really need to know how to do is the following, but my syntax is just all screwy.

Select itemNumber from itemMaster where
if @parmGroup <> "N"            (N = none specified ie all)
  begin
  itemMaster.Group = @parmGroup
  if @parmCategory <> "N"
    begin
    AND itemMaster.Category = @parmCategory
    if @parmClass <> "N"
      begin
      AND itemMaster.Class = @parmClass
    end
  end
end
if @parmFinish <> 0
  begin
  ----From here on just pseudo-code
  AND (
  for int i = 0, @parmFinish.Length, i++
    if @parmFinish[i] <> 0
      itemMaster.Finish = @parmFinish[i]
    
etc.

 

[bborissov]

Select itemNumber
       from itemMaster
       where (@parmGroup = "N" OR itemMaster.Group = @parmGroup) AND
             (@parmCategory = "N" OR itemMaster.Category = @parmCategory) AND
             (@parmClass <> "N" OR itemMaster.Class = @parmClass)

That part with @parmFinish I didin't understand. SQL Server doesn't have arrays. So what you have in @parmFinish and how it supposed to match some fields?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[lithuin]

Thanks, that first part is perfect. Exactly what I was looking for.

My goal is really to pass @parmFinish in as a string, and then iterate through it, pulling out each character and using it as its own parameter. I would pass them in as individual variables, but there could be three finishes passed in or a million.

Well, not really a million, but maybe 7 or 8. I want to be able to handle growth without having to change the procedure everytime we add a finish.

Thanks!

 

[rac2]

I assume the fourth parameter is CHAR or VARCHAR. Because '04039' must be a valid value. And that position is irrelevant. Meaning '24039' is the same as '24390', at least in the desired results of a search. And that the integer value is between 0 and 9, that is it cannot be two digits.

So you need to use LIKE and you need to convert the INT value to a CHAR value.

SELECT ...
WHERE ...
  AND productFinishes LIKE '%' + CAST([Finish] AS CHAR(1)) + '%'

For a particular row the integer value might be 7. The condition evaluates to

  AND productFinishes LIKE '%7%'

Is '24039' LIKE '%7%' ? No, so the row is not selected.

 

[SQLDenis]

declare @finish varchar(50)
select @finish ='1,2,3,4,5,6'


select @finish = '''' + replace(@finish,',',''',''') + ''''

select @finish

then you would do
'AND SomeValue IN (' + @finish + ')'

--test
PRINT 'AND SomeValue IN (' + @finish + ')'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lithuin]

I think that's what I need, I'd just like to ask a couple questions to understand this a bit better.

Even if productfinish is declared as a char(1), "2345 LIKE %5%" would evaluate to true (ignoring the cast for simplicity)? Likewise, "2345 LIKE %3%" would also be true?

For some dumb reason, I'd only thought of using a wildcard on the value I'm comparing with, not the value I'm comparing to.

I need to spend some more time learning SQL. It seems to be much more elegant than I had thought.

Also, when I try to enter the code from your first reply, I get "Invalid column name 'N'." Am I missing some syntax? I've entered:

CREATE PROCEDURE [sp_Search]
(
@parmGroup varchar(1),
@parmCategory int,
@parmClass int,
@parmFinish int
)
AS
SELECT itemMaster.OrderCode, itemMaster.Collection, itemMaster.Class, itemMaster.FinishSpecific FROM itemMaster
     where (@parmGroup = "N" OR [itemMaster.Group] = @parmGroup) AND
                (@parmCategory = 0 OR [itemMaster.Category] = @parmCategory) AND
                (@parmClass = 0 OR [itemMaster.Class] = @parmClass)
;

Thanks yet again!

 

[lithuin]

Oops, ignore the part about your "first reply."
I just realized you are multiple people.

 

[SQLDenis]

Likewise, "2345 LIKE %3%" would also be true?


No this just means the last character has to be 3, the same as right(Value,1) ='3'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[rac2]

Possibly the quote marks are getting in the way of understanding. I posted SQL code only, not C code. I leave the building of the string to you.

Denis is great, not really a menace. But I must quibble.

right(Value,1) = '3' may mean the string must end in '3', but LIKE '%3%' matches a 3 in any position. LIKE '%3' matches a 3 in the final position.

SQL is elegant, no doubt.

 

[SQLDenis]

true somewhow I missed the first %

right(Value,1) = '3' is equal to LIKE '%3'
left(Value,1) = '3' is equal to LIKE '3%'






Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lithuin]

I just wanted to thank all three of you again for your help.
With a little tweaking, your solutions worked like a charm.

Thanks again!