Rownum

Hi folks,
SELECT * FROM mytable
100 rows returned.
Can i get a rownum column for each record; i.e. if 100 records returned; rownum order 1,2,3....100 along with the each record position.
is it possible without using cursor?
Howdy!see http://www.dbforums.com/t1058224.html|||Assuming that you have atleast one primary key or at least a unique constraint:

Select Count(RowTable.UniqueField) as RowNumber, Mytable.Fields
from Mytable
inner join Mytable RowTable on Mytable.UniqueField >= RowTable.UniqueField

Even I had the Same problem. Thanks To Blindman for his help regarding the query.|||Thanx to r937 and blindman! :)|||Assuming that you have atleast one primary key or at least a unique constraint
Also: NULL values won't be counted. In the other thread the values were used as columnnames, so NULL is quite unlikely. Not sure about myTable.sql

Rowcount and SQLDataReader

Hi, from what I can find, there isn't a way to get the number of rows returned from a SQLDataReader command. Is this correct? If so, is there a way around this? My SQLDataReader command is as follows:

Dim commandIndAsNew System.Data.OleDb.OleDbDataAdapter(strQueryCombined, connInd)

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

Dim resultDSAsNew Data.DataSet()

'// Fill the dataset with values

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

Dim strXMLAsString = resultDS.GetXml()

Dim xmlFileListAs SqlParameter

Dim strContainsClauseAs SqlParameter

'// Create and execute the search against SQL Server

connStringSQL.Open()

commandSQL.CommandType = Data.CommandType.StoredProcedure

commandSQL.Parameters.Add("@.xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXML

commandSQL.Parameters.Add("@.strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstruct

Dim sqlReaderSourceAs SqlDataReader = commandSQL.ExecuteReader()

results.DataSource = sqlReaderSource

results.DataBind()

connStringSQL.Close()

And the stored procedure is such:

DROPPROC dbo.GetAssetList2;

GO

CREATEPROC dbo.GetAssetList2

(

@.xmlFileListvarchar(1000),

@.strContainsClausevarchar(1000)

)

AS

BEGIN

SETNOCOUNTON

DECLARE @.intDocHandleint

EXECsp_xml_preparedocument @.intDocHandleOUTPUT, @.xmlFileList

SELECTDISTINCT

AssetsMaster.AssetMasterUID,

SupportedFiles.AssetPath,

FROM

AssetsMaster,

OPENXML(@.intDocHandle,'/NewDataSet/Table',2)WITH(FILENAMEvarchar(256))AS x,

SupportedFiles

WHERE

AssetsMaster.AssetFileName= x.FILENAME

AND AssetsMaster.Extension= SupportedFiles.Extension

UNION

SELECTDISTINCT

AssetsMaster.AssetMasterUID,

SupportedFiles.AssetPath,

FROM

AssetsMaster,

OPENXML(@.intDocHandle,'/NewDataSet/Table',2)WITH(FILENAMEvarchar(256))AS x,

SupportedFiles

WHERE

AssetsMaster.AssetFileName<> x.FILENAME

ANDCONTAINS((Description, Keywords), @.strContainsClause)

AND AssetsMaster.Extension= SupportedFiles.Extension

ORDERBY AssetsMaster.DownloadsDESC

EXECsp_xml_removedocument @.intDocHandle

END

GO

How can I access the number of rows returned by this stored procedure?

Thanks,

James

I would suggest changing your datareader into a dataset. Then you can bind to the dataset, and also check how many rows are in it.|||

Thanks Motley. What is wrong with this? I am calling a stored SELECT procedure. It says there is a syntax error.

Dim connStringSQLAsNew SqlConnection("Data Source=*;Database=*;User ID=*;Password=*;Trusted_Connection=*")

'// Create the new OLEDB connection to Indexing Service

Dim commandSQLAsNew SqlCommand("GetAssetList2", connStringSQL)

Dim resultDSAsNew Data.DataSet()

Dim resultDAAsNew SqlDataAdapter()

'// Fill the dataset with values from the previous query

commandInd.Fill(resultDS)

'// Get the XML values of the dataset to send to SQL server and run a new query

Dim strXMLAsString = resultDS.GetXml()

Dim xmlFileListAs SqlParameter

Dim strContainsClauseAs SqlParameter

'// Create and execute the search against SQL Server

commandSQL.Parameters.Add("@.xmlFileList", Data.SqlDbType.VarChar, 1000).Value = strXML

commandSQL.Parameters.Add("@.strContainsClause", Data.SqlDbType.VarChar, 1000).Value = strContainsConstruct

resultDA.SelectCommand = commandSQL

resultDA.Fill(resultDS)

Dim sourceAsNew Data.DataView(resultDS.Tables(0))

resultCount.Text = source.Count.ToString

results.DataSource = source

results.DataBind()

|||

Never mind, I got it by added

commandSQL.CommandType = Data.CommandType.StoredProcedure

But now I get one extra row returned per query. It's empty. Any ideas?

row number.

All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. I think I have done this in
the past but am a bit rusty after not using SQL for a while. If
anyone could help with a code snippet this would be gretly
appreciated.Here's an example from the Pubs database:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS rownum
,*
FROM Authors AS A

--
David Portas
SQL Server MVP
--|||
David Mackintosh wrote:
> All I am trying to do is return the row number (calculated field?)
> with each row returned from an SQL query. I think I have done this in
> the past but am a bit rusty after not using SQL for a while. If
> anyone could help with a code snippet this would be gretly
> appreciated.

There are now row numbers.

Zach|||>> All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. <<

Since this would have to be for display purposes in the front end, you
ought to be doing in the front, not the database.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Row and column count in SSMS

When you execute an MDX query the Messages tab displays the number of rows and columns returned.

However, that includes the rows and columns used to hold the member names. Personally I would prefer it only returned the rows and columns in the cellset.

Anyone agree/disagree? Discuss!!!

Also, a minor point. I'd like it if the rows and columns were displayed on the results tab like the number of rows are in a SQL query. Anyone agree?

cheers

Jamie

Hi Jamie,

The row and column counts returned for empty axes are also a bit inconsistent :

>>

select {} on 0
from [Adventure Works]
-
Cell set consists of 1 rows and 0 columns.

-
select {} on 0,
{} on 1
from [Adventure Works]
-
Cell set consists of 0 rows and 0 columns.

>>

The separate Message Tab takes some getting used to, but it works OK for me...

|||

Interesting, thanks Deepak.

Anyone else?