SQL Quickie (Sql Server 2000)

Phileas Fogg

Rightio, this works:

SELECT TOP 10 * FROM tblFoo

But this doesn't.

DECLARE @NumRows int
SET @NumRows = 10
SELECT TOP @NumRows * FROM tblFoo

Now the code in the second example is actually being executed as a varchar with sp_executesql which, imho, is not ideal. Anybody know of another way?

Kai

DECLARE @NumRows int
DECLARE @sqlQ varchar(500)
SET @NumRows = 10

Select @sqlQ = 'Select TOP ' + @NumRows + ' * FROM tblFoo'
sp_executesql @sqlQ


Try that.

bonkey

What you want is probbly something like:

@SqlToExecute = 'SELECT TOP ' + @NumRows + ' * FROM tblFoo'

sp_executesql @SQLTOExecute

This is subtly different from what you supplied. From what you described, you seem to be trying to execute all three lines....which is invalid - the value for the number of rows cannot be parameterised.

In my case, I am substituting the number of rows into the string, so that what gets executed is a SELECT statement which is exactly like the one that you say works.

Hopefully you see the difference?

I agree that using sp_executesql isn't ideal...but that then begs the question of why you're using it. At a guess, this is code thats running inside a stored procedure. What are you doing that you want to limit the output dynamically?

Could you use whatever access library to specify it client-side, perhaps?

In ADO.Net, for example, you can set a MaxRows property on a Recordset. The Fill Method of the DataAdapter also allows you to specify a max number of rows.

jc

Kai

great minds ...

Phileas Fogg

Sorry I've been unclear. It's only the the last line that's currently being executed by sp_executesql, the code was written by my predecessor and is exactly the same as what you've both given me. While it's adequate there must be a better way of achieving the same thing, the client-side code is actualy using a DataReader which doesn't have a MaxRows property but I'll probably be changing it to a dataset anyway, so I'll use MaxRows after all.

Thanks

bonkey

Phileas Fogg wrote:

It's only the the last line that's currently being executed by sp_executesql, the code was written by my predecessor and is exactly the same as what you've both given me.

In fairness, what you put in the first post is most defniitely not the same as what we gave you....but if you're saying thats what it is, then ok.

While it's adequate there must be a better way of achieving the same thing,

There's lots of ways....it just depends what you call "the same thing", and what said "thing" is

the client-side code is actualy using a DataReader which doesn't have a MaxRows property but I'll probably be changing it to a dataset anyway, so I'll use MaxRows after all.

Alternately, have a read of this:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21468035.html

Basically - set the ROWCOUNT on the connection, rather than requesting TOP N from the query.

amen

yikes
setting ROW COUNT
I'd shoot someone in work for doing that unless that had a really really good excuse for doing that

Phileas Fogg

bonkey wrote:

In fairness, what you put in the first post is most defniitely not the same as what we gave you

I never claimed it was. The code in question is a varchar being executed by sp_executesql which, if I'm not mistaken, is what the following is:

@SqlToExecute = 'SELECT TOP ' + @NumRows + ' * FROM tblFoo'

sp_executesql @SQLTOExecute

Although I could be wrong as you haven't provided the declare statement for @SqltoExecute, excuse the assumption.

RowCount is also not an option, I'm going to do it in client memory as you suggested.

bonkey

Phileas Fogg wrote:

I never claimed it was.

You posted a set of code and asked how to fix it.

We suggested how it can be fixed, only to be told that what you had was not what you initially posted, but rather what we had suggested as the correction....

I was kinda hinting that if you had posted what you wanted fixed, it would be much easier to fix it. Anyway....not important.

The following code works. I've got a SQL Server to hand this time, and have tested it.

declare @sql nvarchar(4000)
declare @rowNum int
set @rowNum = 10

set @sql = 'SELECT TOP ' + convert(nvarchar, @rowNum) + ' * from authors'

exec sp_executesql @sql
GO

I tried using varchar for @sql, but received an error that sp_executesql requires ntext/nchar/nvarchar.

@rownum has to be converted - if you don't, you'll get an error telling you that it couldn't convert 'SELECT TOP ' to int.

Note that the stored procedure is prefixed by EXEC. This is required in MSSQL Server if the call to the stored procedure isn't the only command in a batch. This is because MSSQL uses reserved words to determine the beginning of a new command, rather than command terminators. Stored-procedure names are not reserved words, but EXEC is.

I've also tested this code by wrapping it in a stored procedure, and calling the stored procedure. It works fine there too.

jc