Store Procedure with unknow condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Store Procedure with unknow condition

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-02 : 21:00:27

I have a table ICSELECT that have and IDSELECT field with the information of the inventory clases major and minor that the Department of Inventory with count.

For example the IDSELECT-> IDTEST have
MAJ_CLAS MIN_CLAS
112 43
98 30
102
129 57

So I have to go to the ITEMMAST ( Item Master table) and make a quey like this

Select Item, description
from ITEMMAST
where (MAJ_CLAS=112 AND MIN_CLAS=43) OR (MAJ_CLAS=98 AND MIN_CLAS=30)
OR (MAJ_CLAS=102) OR (MAJ_CLAS=129 AND MIN_CLAS=57)

That is to print the sheets that we give to the people that go and count the item in the stores.
The problem is that the table ICSELECT have 20 major and minor clase fiels and some time they only will fill one per IDSELECT or they can have 20 too. So the number of conditions is not a thing that I can have to make my store procedure.
For example the last sentences will be something like

Select Item, description
from ITEMMAST
where (MAJ_CLAS=maj_class_01 AND MIN_CLAS=min_class_01) OR (MAJ_CLAS=maj_class_02 AND MIN_CLAS=min_class_02)
OR (MAJ_CLAS=maj_class_03)
OR (MAJ_CLAS=maj_class_04 AND MIN_CLAS=min_class_04)

Where I have to send the maj_clas_## and min_class_## has parameters ( I suppose), and other thing the user can have just a major classes in some of the case, so the condition can be:
(MAJ_CLAS=maj_class_03)
OR
(MAJ_CLAS=maj_class_04 AND MIN_CLAS=min_class_04)
Thanks in advance for take a time to read this.
I hope you undestand my english.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2003-04-02 : 21:59:43

If I understand correctly, you're trying to create a query that has a variable number of conditions.
In this case, you should create the query as a string, then execute it with the sp_ExecuteSQL system stored procedure. You can find out more about this in the SQL Server books online.

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-03 : 18:18:37

Thanks for your help I am using

DECLARE @InsertString varchar(5000)

SET @InsertString = ('SELECT dbo.ITEMLOC.COMPANY, dbo.ITEMLOC.LOCATION
FROM dbo.ITEMLOC ')
exec (@InsertString)

ans if works just fine, but now that I am trying with this stament I got the error:
Line 16: Incorrect syntax near 'TJU01'.

SET @InsertString = ('SELECT dbo.ITEMLOC.COMPANY, dbo.ITEMLOC.LOCATION
FROM dbo.ITEMLOC ')

SET @InsertString =('SELECT TOP 100 PERCENT dbo.ITEMLOC.COMPANY, dbo.ITEMLOC.LOCATION, dbo.ICLOCATION.NAME, dbo.ITEMMAST.ITEM, dbo.ITEMMAST.DESCRIPTION,
dbo.ITEMMAST.SALES_MAJCL, dbo.MAJORCL.DESCRIPTION AS EXPR1, dbo.ITEMMAST.SALES_MINCL, dbo.MINORCL.DESCRIPTION AS EXPR2
FROM dbo.MINORCL INNER JOIN
dbo.MAJORCL ON dbo.MINORCL.MAJOR_CLASS = dbo.MAJORCL.MAJOR_CLASS AND dbo.MINORCL.ITEM_GROUP = dbo.MAJORCL.ITEM_GROUP AND
dbo.MINORCL.CLASS_TYPE = dbo.MAJORCL.CLASS_TYPE INNER JOIN
dbo.ITEMMAST ON dbo.MINORCL.MAJOR_CLASS = dbo.ITEMMAST.INVEN_MAJCL AND
dbo.MINORCL.MINOR_CLASS = dbo.ITEMMAST.INVEN_MINCL AND dbo.MINORCL.ITEM_GROUP = dbo.ITEMMAST.ITEM_GROUP INNER JOIN
dbo.ITEMLOC ON dbo.ITEMMAST.ITEM = dbo.ITEMLOC.ITEM INNER JOIN
dbo.ICLOCATION ON dbo.ITEMLOC.LOCATION = dbo.ICLOCATION.LOCATION AND dbo.ITEMLOC.COMPANY = dbo.ICLOCATION.COMPANY
WHERE (dbo.ITEMLOC.LOCATION = 'TJU01') AND (dbo.MINORCL.CLASS_TYPE = 'I') AND (dbo.ITEMMAST.SALES_MAJCL = '109') AND
(dbo.ITEMMAST.SALES_MINCL = '702') AND (dbo.ITEMLOC.COMPANY = 2000) OR
(dbo.ITEMLOC.LOCATION = 'TJU01') AND (dbo.MINORCL.CLASS_TYPE = 'I') AND (dbo.ITEMMAST.SALES_MAJCL = '109') AND
(dbo.ITEMMAST.SALES_MINCL = '467') AND (dbo.ITEMMAST.SALES_MAJCL = '107') OR
(dbo.ITEMLOC.LOCATION = 'TJU01') AND (dbo.MINORCL.CLASS_TYPE = 'I') AND (dbo.ITEMMAST.SALES_MAJCL = '109') AND
(dbo.ITEMMAST.SALES_MAJCL = '106')')

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-04 : 02:05:49

quote:

got the error:
Line 16: Incorrect syntax near 'TJU01'.

Try This..

declare @InsertString varchar(8000)
SET @InsertString =
('SELECT TOP 100 PERCENT dbo.ITEMLOC.COMPANY, dbo.ITEMLOC.LOCATION, dbo.ICLOCATION.NAME, dbo.ITEMMAST.ITEM,
dbo.ITEMMAST.DESCRIPTION, dbo.ITEMMAST.SALES_MAJCL, dbo.MAJORCL.DESCRIPTION AS EXPR1, dbo.ITEMMAST.SALES_MINCL,
dbo.MINORCL.DESCRIPTION AS EXPR2
FROM dbo.MINORCL INNER JOIN dbo.MAJORCL
ON dbo.MINORCL.MAJOR_CLASS = dbo.MAJORCL.MAJOR_CLASS AND dbo.MINORCL.ITEM_GROUP = dbo.MAJORCL.ITEM_GROUP
AND dbo.MINORCL.CLASS_TYPE = dbo.MAJORCL.CLASS_TYPE
INNER JOIN dbo.ITEMMAST
ON dbo.MINORCL.MAJOR_CLASS = dbo.ITEMMAST.INVEN_MAJCL AND dbo.MINORCL.MINOR_CLASS = dbo.ITEMMAST.INVEN_MINCL
AND dbo.MINORCL.ITEM_GROUP = dbo.ITEMMAST.ITEM_GROUP
INNER JOIN dbo.ITEMLOC
ON dbo.ITEMMAST.ITEM = dbo.ITEMLOC.ITEM
INNER JOIN dbo.ICLOCATION
ON dbo.ITEMLOC.LOCATION = dbo.ICLOCATION.LOCATION AND dbo.ITEMLOC.COMPANY = dbo.ICLOCATION.COMPANY
WHERE
(dbo.ITEMLOC.LOCATION = ''TJU01'') AND (dbo.MINORCL.CLASS_TYPE = ''I'') AND (dbo.ITEMMAST.SALES_MAJCL = 109)
AND (dbo.ITEMMAST.SALES_MINCL = 702) AND (dbo.ITEMLOC.COMPANY = 2000)
OR
(dbo.ITEMLOC.LOCATION = ''TJU01'') AND (dbo.MINORCL.CLASS_TYPE = ''I'') AND (dbo.ITEMMAST.SALES_MAJCL = 109)
AND (dbo.ITEMMAST.SALES_MINCL = 467) AND (dbo.ITEMMAST.SALES_MAJCL = 107)
OR
(dbo.ITEMLOC.LOCATION = ''TJU01'') AND (dbo.MINORCL.CLASS_TYPE = ''I'') AND (dbo.ITEMMAST.SALES_MAJCL = 109)
AND (dbo.ITEMMAST.SALES_MAJCL = 106)')

print @InsertString

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-04-04 : 02:09:58

quote:

WHERE
(dbo.ITEMLOC.LOCATION = ''TJU01'') AND (dbo.MINORCL.CLASS_TYPE = ''I'') AND (dbo.ITEMMAST.SALES_MAJCL = 109)
AND (dbo.ITEMMAST.SALES_MINCL = 702) AND (dbo.ITEMLOC.COMPANY = 2000)
OR
(dbo.ITEMLOC.LOCATION = ''TJU01'') AND (dbo.MINORCL.CLASS_TYPE = ''I'') AND (dbo.ITEMMAST.SALES_MAJCL = 109)
AND (dbo.ITEMMAST.SALES_MINCL = 467) AND (dbo.ITEMMAST.SALES_MAJCL = 107)
OR
(dbo.ITEMLOC.LOCATION = ''TJU01'') AND (dbo.MINORCL.CLASS_TYPE = ''I'') AND (dbo.ITEMMAST.SALES_MAJCL = 109)
AND (dbo.ITEMMAST.SALES_MAJCL = 106)')

print @InsertString

Wondering why this conditions are repeated..
Can't we try replacing the where conditions like this..

WHERE
(dbo.ITEMLOC.LOCATION = ''TJU01'') AND (dbo.MINORCL.CLASS_TYPE = ''I'') AND (dbo.ITEMMAST.SALES_MAJCL = 109)
AND (
(dbo.ITEMMAST.SALES_MINCL = 702) AND (dbo.ITEMLOC.COMPANY = 2000) OR
(dbo.ITEMMAST.SALES_MINCL = 467) AND (dbo.ITEMMAST.SALES_MAJCL = 107) OR
(dbo.ITEMMAST.SALES_MAJCL = 106)
)

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-04 : 14:12:27

That you all for you help!!
The doble '' works and the where clause was modified. Now that I have proved this in the Query analyzer I want to write and store procedure that sent the string.

Store procedure:
----------
CREATE PROCEDURE SP_EXEC
@InsertString varchar(5000)
AS
exec (@InsertString)
GO
--------

I send this string var from visual basic, and I receive and error in the last '). What is the way I have to send the string to de SP?
First I put doble ' here too but then I get the error on 'TJU01'.

'VB Code

strQuery = "SELECT ITEMLOC.COMPANY, ITEMLOC.LOCATION, ICLOCATION.NAME, ITEMMAST.ITEM, ITEMMAST.DESCRIPTION, ITEMMAST.SALES_MAJCL, MAJORCL.DESCRIPTION AS EXPR1, ITEMMAST.SALES_MINCL,MINORCL.DESCRIPTION AS EXPR2 FROM MINORCL INNER JOIN MAJORCL ON MINORCL.MAJOR_CLASS = MAJORCL.MAJOR_CLASS AND MINORCL.ITEM_GROUP = MAJORCL.ITEM_GROUP AND MINORCL.CLASS_TYPE = MAJORCL.CLASS_TYPE INNER JOIN ITEMMAST ON MINORCL.MAJOR_CLASS = ITEMMAST.INVEN_MAJCL AND MINORCL.MINOR_CLASS = ITEMMAST.INVEN_MINCL AND MINORCL.ITEM_GROUP = ITEMMAST.ITEM_GROUP INNER JOIN ITEMLOC ON ITEMMAST.ITEM = ITEMLOC.ITEM INNER JOIN ICLOCATION ON ITEMLOC.LOCATION = ICLOCATION.LOCATION AND ITEMLOC.COMPANY = ICLOCATION.COMPANY WHERE (ITEMLOC.COMPANY = 2000) AND(ITEMLOC.LOCATION = 'TJU01') AND
(MINORCL.CLASS_TYPE = 'I') AND ( ((ITEMMAST.SALES_MAJCL ='18 ') AND (ITEMMAST.SALES_MINCL ='3 ')) OR ((ITEMMAST.SALES_MAJCL ='18 ') AND (ITEMMAST.SALES_MINCL ='4 '))')"

SQL = "SP_EXEC '" & strQuery & "'"
Set adoRS = New ADODB.Recordset
adoRS.Open sSQL, gcnSQL, adOpenForwardOnly, adLockReadOnly, adCmdText
With adoRS
If .EOF And .BOF Then 'No encontro
MsgBox "NO HUBO DATOS"
Else
MsgBox "DATOS"
End If
End With
End Sub

shifis
Posting Yak Master

157 Posts

Posted - 2003-04-04 : 14:26:47

I wrote wrong the string { I forgot the firt (' ..}
That is because in the program I have something like
strQuery = "('" & strQIni & strQMid & strQFin & "')"
But I put for you all the string together.

strQuery = "('SELECT ITEMLOC.COMPANY, ITEMLOC.LOCATION, ICLOCATION.NAME, ITEMMAST.ITEM, ITEMMAST.DESCRIPTION, ITEMMAST.SALES_MAJCL, MAJORCL.DESCRIPTION AS EXPR1, ITEMMAST.SALES_MINCL,MINORCL.DESCRIPTION AS EXPR2 FROM MINORCL INNER JOIN MAJORCL ON MINORCL.MAJOR_CLASS = MAJORCL.MAJOR_CLASS AND MINORCL.ITEM_GROUP = MAJORCL.ITEM_GROUP AND MINORCL.CLASS_TYPE = MAJORCL.CLASS_TYPE INNER JOIN ITEMMAST ON MINORCL.MAJOR_CLASS = ITEMMAST.INVEN_MAJCL AND MINORCL.MINOR_CLASS = ITEMMAST.INVEN_MINCL AND MINORCL.ITEM_GROUP = ITEMMAST.ITEM_GROUP INNER JOIN ITEMLOC ON ITEMMAST.ITEM = ITEMLOC.ITEM INNER JOIN ICLOCATION ON ITEMLOC.LOCATION = ICLOCATION.LOCATION AND ITEMLOC.COMPANY = ICLOCATION.COMPANY WHERE (ITEMLOC.COMPANY = 2000) AND(ITEMLOC.LOCATION = 'TJU01') AND
(MINORCL.CLASS_TYPE = 'I') AND ( ((ITEMMAST.SALES_MAJCL ='18 ') AND (ITEMMAST.SALES_MINCL ='3 ')) OR ((ITEMMAST.SALES_MAJCL ='18 ') AND (ITEMMAST.SALES_MINCL ='4 '))')"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources