stored procedure doubt - SQL Server Forums

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

SQL Server Development (2000)

stored procedure doubt

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-18 : 09:33:16

Gauri writes "I am writing the following SP:

CREATE PROCEDURE [dbo].[odItemValuesCount1]
(
@retval int output,
@regdate datetime,
@duedate datetime,
@categoryname varchar(50),
@itemvalue1 varchar(50) = '%',
@itemvalue2 varchar(50) = '%',
@itemvalue3 varchar(50) = '%',
@itemvalue4 varchar(50) = '%',
@itemvalue5 varchar(50) = '%',
@itemvalue6 varchar(50) = '%',
@itemvalue7 varchar(50) = '%',
@itemvalue8 varchar(50) = '%',
@itemvalue9 varchar(50) = '%',
@itemvalue10 varchar(50) = '%'
)
AS
--begin transaction
select @retval = 0
declare @count int
select @count =0

declare itemcatid1 cursor for
select System_itemCategoryID from odItemCategory where names = @categoryname

declare @itemcatid1 int
open itemcatid1
fetch itemcatid1 into @itemcatid1
close itemcatid1
deallocate itemcatid1

declare @count1 int

/* counts all items whethere they are available, reserved or rented from the database*/

set @count1 = (select count (*) from odItem where System_itemID IN (
SELECT distinct (odItemProperties.System_itemID)
FROM odItemProperties
WHERE (
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue1))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue2))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue3))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue4))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue5))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue6))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue7))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue8))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue9))
AND
(System_itemID IN (SELECT System_itemID FROM odItemProperties where itemValue LIKE @itemvalue10))

) and (System_itemStatusID = 1 or System_itemStatusID = 2 or System_itemStatusID = 5) and System_itemCategoryID = @itemcatid1 and deleted = 0))

/* */
declare itemid1 cursor read_only for

SELECT distinct (odItemProperties.System_itemID)
FROM odItemProperties, odItem
WHERE (
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue1))
AND
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue2))
AND
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue3))
AND
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue4))
AND
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue5))
AND
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue6))
AND
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue7))
AND
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue8))
AND
(odItemProperties.System_itemID IN (SELECT odItemProperties.System_itemID FROM odItemProperties where itemValue LIKE @itemvalue9))
AND
(odItemProperties.System_itemID IN (SELECT od

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-18 : 09:36:32

You didnt give full information. What are you trying to do?
Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources