a Condition about RowIndex of the Result Set

Question

a Condition about RowIndex of the Result Set

feodon

SSC Journeyman

Points: 98
More actions
December 20, 2008 at 8:05 am

#96864

Hello ,
I want to get the desired product ID
with a condition about Row Index of the results ,
is it possible inside an sproc or not ,
I know that this code ( the end part ) is not correct ,
but it shows want I want ,
It's also appreciable to know alternate ways to do that by code (CLR )
this will be a part of an asp.net 2.0 application
and just I want to get a productID at last
-----------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROC [dbo].[GetSelRowProductID3]
( @TagSelected nvarchar(20) ,
@RowNum int
)
AS
BEGIN
DECLARE @ProductsX TABLE
(
ProductID INT ,
RowNumber INT
)
INSERT INTO @ProductsX
SELECT
ROW_NUMBER() OVER (ORDER BY id) AS Row , id
FROM Products p
JOIN [TagProducts] tp ON p.[id]=tp.[ProductID]
JOIN [Tags] t ON t.[TagID]=tp.[TagID]
WHERE @TagSelected = t.[TagTitle]
SELECT id FROM @ProductsX
WHERE Row = @RowNum
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Can you post the table definitions and some sample data as per the link in my signature block so that someone can properly assist you?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

It'll work if you put the SELECT LIST into the same order as the COLUMN LIST in the table definition. It would be better if the INSERT had a discrete COLUMN LIST.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

feodon SSC Journeyman Points: 98 More actions · Answer 3

I solved the issue next to my post ,

Jeff Moden is right ,

the problem was because of ther order of what I entered ,

and also one of the variables name ,

excuse me for the delay ,

really thanks

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 4

Thanks for the feedback. Just remember that order typically means nothing in a database and it theoretically could change at any time. It's always best to have a Column List on Inserts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)