Count for multiple rows in Stored Procedure

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)

Count for multiple rows in Stored Procedure

Author

Topic

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-07 : 13:21:31

What i am trying to achieve is to check if any items is attached to a promotionid in table, ITEMPROM. I have a parameter @Promotionid, which will store 1 or more promotion id such as "1,2,3,4".

After which, the stored procedure should individually check if any items are attached to each promotion id. And return the promotion ids that do not have any items attached to the promotion.

Below is the code i came up with but obviously still far from my goal. I was thinking of using a user defined function Split to split the promotion ids and check.

I could have done a for-loop inside my ASP.Net, but i prefer to call the stored procedure to do all the necessary checking when it comes to database. Thanks


CREATE PROCEDURE [CHECKITEMPROM] 
(
@PROMOTIONIDS VARCHAR(100)
)
AS
DECLARE @PROMIDS VARCHAR(100), @ITEMCOUNT INT


SELECT @ITEMCOUNT=COUNT(ITEMID)
FROM ITEMPROM
WHERE PROMOTIONID = + @PROMOTIONIDS

IF @ITEMCOUNT=0
--This line is like wrong because i wish to only store the promotion ids without any items attached to them.--
SET @PROMIDS = @PROMIDS +  "," + @PROMOTIONIDS
GO



CREATE FUNCTION SPLIT
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 13:57:36

Something like this:


CREATE PROCEDURE dbo.[CHECKITEMPROM] 
(
    @PROMOTIONIDS VARCHAR(100)
)
AS

SELECT Data
FROM dbo.SPLIT(@PROMOTIONIDS, ',')
    LEFT OUTER JOIN dbo.ITEMPROM
        ON PROMOTIONID = Data
WHERE PROMOTIONID IS NULL
ORDER BY Data
GO

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-07 : 14:25:42

Then will it return the promotion ids that do not have any items attached to it? Must i declare a variable and return it?

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 14:51:31

"Must i declare a variable and return it"

No, it wil return a list of the rows which no matching row in ITEMPROM

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-07 : 15:09:23

This "Data" attribute right is pre-defined? Where did this "Data" come from? Sorry, im really curious.

quote:
Originally posted by Kristen

"Must i declare a variable and return it"

No, it wil return a list of the rows which no matching row in ITEMPROM

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-07 : 15:18:06

Is it possible to just return like "1,2,3,4" instead of multiple rows? Probably use a variable to store the values.

Kristen
Test

22859 Posts

Posted - 2006-01-08 : 05:39:50

"Where did this "Data" come from?"

Its a "column name" for the return set from the function "SPLIT"

"Is it possible to just return like "1,2,3,4" instead of multiple rows?"

Yes, but I can't imagine [good reasons] for doing that. a) it would take more SQL horsepower and b) would be delimited as to the length that could be processed and c) quite possibly require re-splitting in the application.

If you need it concatenated then its probably best to have the application do it

Kristen

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-08 : 22:45:24

Yeah your right, i let the application handle that. Anyway, i do not quite understand the Split function. Why is there 2 insert statements?
Could you give me a simple example to explain? Thanks



CREATE FUNCTION SPLIT
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 01:42:50

>>Why is there 2 insert statements?

If the string to be splitted is 1,2,3,4 then While loop insert all except 4 that is left at last.
You need to insert that again so as second insert

Madhivanan

Failing to plan is Planning to fail

shaoen01
Yak Posting Veteran

78 Posts

Posted - 2006-01-09 : 02:00:45

But the 2nd insert is in the while loop as well, wont it insert a lot of times. In this case, the second insert will run 4 times and trying to set the value "4" only?

I also do not quite get this line: "Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))"
and "RETURNS @RtnValue table" too.

quote:
Originally posted by madhivanan

>>Why is there 2 insert statements?

If the string to be splitted is 1,2,3,4 then While loop insert all except 4 that is left at last.
You need to insert that again so as second insert

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 02:08:51

Look for Charindex in BOL, SQL Server help file
Also use
Select data
to check what it has

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources