Records n recordcount by appending with SELECT COUNT(*) FROM (// Appended here //) for a situation

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)

Records n recordcount by appending with SELECT COUNT(*) FROM (// Appended here //) for a situation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-17 : 08:27:56

Hemant writes "The table design we have is such that we fire one query to get the name of the column that we have to select in the other query.
I have done this by first getting the column name from the first query:

Declare @Column nvarchar(20)
SELECT @Column=ColumnName FROM table

This variable @Column is used to build another query which is executed using EXEC(@Query)

DECLARE @Query nvarchar(1024)
SELECT @Query = 'SELECT ' + @Column + ' FROM something'
EXEC(@Query)

Now, I have a generic function which takes in the above query and returns the dataset when executed directly and the count of the records when appended with

SELECT COUNT(*) FROM (// Query Appended HERE //) ALIAS

The dataset is obtained successfully but the problem lies in getting the recordcount using the same query.

Looking for a help on the same.

Thanks!"

Kristen
Test

22859 Posts

Posted - 2004-09-18 : 06:43:43

[code]
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE #MyTempTable
(
MyRowCount int
)

INSERT INTO #MyTempTable (MyRowCOunt)
EXEC ('SELECT COUNT(*) FROM (SELECT * FROM dbo.Customers) X')

SELECT * FROM #MyTempTable

SELECT COUNT(*) AS [CustomerCount] FROM dbo.Customers

DROP TABLE #MyTempTable
SET NOCOUNT OFF
[/code]
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources