How to get each number of records...

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

General SQL Server Forums

New to SQL Server Programming

How to get each number of records...

Author

Topic

a4nsd
Starting Member

20 Posts

Posted - 2006-12-02 : 12:57:57

HI EVERYBODY
This is my procedure
"
CREATE PROCEDURE SP_SAMPLE_SEARCH
@Title nvarchar(256)
AS

SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc
GO
"
I exec procedure and it returns 12 results with diffirents ID
and want to get these values 1,2,3....12
How do I get these...
I am a beginner.
Thanks for help..

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-02 : 13:56:07

You want comma separated IDs as a resultset?

CREATE PROCEDURE SP_SAMPLE_SEARCH
@Title nvarchar(256)
AS

Declare @IDList varchar(250)

SELECT @IDList = coalesce(@IDList + ',','') + [ID] 
FROM [tbl_Sim] 
WHERE ([Title] LIKE '%' + @Title + '%') 
Order by [ID]

SELECT @IDList
GO

Harsh Athalye
India.
"Nothing is Impossible"

a4nsd
Starting Member

20 Posts

Posted - 2006-12-02 : 14:29:24

Thanks for your answer very much. There is an error
"Server: Msg 245, Level 16, State 1, Procedure SP_SAMPLE, Line 7
Syntax error converting the varchar value '12,' to a column of data type int."
My ID type is int

In SQL 2005, I can use ROW_NUMBER() for generating row numbers. But in SQL 2000 I don't know how to get row numbers?.
Can you show me how to get...
Thanks very much

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-02 : 16:28:22

either put the results in a temp table with identity
or
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 13

but this should be avoided and you should number your rows in your front end.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-04 : 09:43:49

>>want to get these values 1,2,3....12

Where do you want to show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 09:56:25

quote:
Originally posted by harsh_athalye

CREATE PROCEDURE SP_SAMPLE_SEARCH
@Title nvarchar(256)
AS

Declare @IDList varchar(250)

SELECT @IDList = coalesce(@IDList + ',','') + cast([ID] as varchar)
FROM [tbl_Sim]
WHERE ([Title] LIKE '%' + @Title + '%')
Order by [ID]

SELECT @IDList

Peter Larsson
Helsingborg, Sweden

a4nsd
Starting Member

20 Posts

Posted - 2006-12-05 : 07:39:16

Thanks very much. It works well
Thanks...........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources