Hi,

I need to set the output of a query to a variable. What I want to do is to get a count of a select top query. The number of records I want to return is a variable so, as far as I'm aware, I need to create a query string and use the Execute command. This is how far I've got:

Set @sqlStatement = 'select TOP ' + convert(varchar, @numStatements) + ' * from [progressPDP].[dbo].[statementLabels] ORDER BY [labelIndex]'

Execute (@sqlStatement)

In pseudo code I think what I'm looking for is something like this:

Set @sqlStatement = 'select TOP ' + convert(varchar, @numStatements) + ' Count(*) from [progressPDP].[dbo].[statementLabels] ORDER BY [labelIndex]'

@theCount = Execute (@sqlStatement)

I then want to execute different queries depending on the value of @theCount.

Any help would be greatly appreciated.

Cheers,

Andy

Andy,

I am little confused with your requirement. As you say

Select TOP 10 * from TESTTABLE order by Col1

Returns the top 10 records in the ascending order of the values present on COL1. I dont think you would get a count of records, as you had mentioned.

An OUTPUTPARAMETER can hold a single values in it. Something like a count/value of a column based on sime criteria. It cannot hold a set of rows in it, as the code/pseudocode that you posted would return set of rows with two columns in its result, which cannot be stored in an output parameter.

May be you may want to re-post your requirement in an understandable way, so as it guide you in the right path.

Cheers!

-Vijay G