Quick MS SQL Stored Procedure question

[Zinn]

I have this:

CREATE PROCEDURE GetAds

	@NumAds numeric = 1

AS

	SELECT	TOP @NumAds ID
	FROM		Ads 
GO

This gives me a syntax error when I have the "TOP @NumAds ID" in there. when I use "TOP 5 ID" the procedure works. I've tried casting @NumAds as a variety of different data-types, and none has worked. I really need to pass in this parameter, so what can I do?

Thanks in advance.

 

[calebb]

Try this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAds]
	@NumAds INT
AS
BEGIN
	SELECT
		TOP @NumAds ID
	FROM
		Ads
END

Then change to the correct database and run:

GetAds 5

And see what happens.

Also, you might want to use ROWCOUNT, not TOP (if end up doing any joins, TOP will return the top 10 from the first table you specify. With ROWCOUNT, the join is computed first and then returns X rows from the resulting dataset.

 

[FreiDOg]

To the best of my knowledge, MSSQL does not allow you to pass a variable to a 'top n [percent]' constraint. Though that may not be accurate for Server 2005.

You have to do it with dynamic SQL
E.g.

Create Procedure dbo.GetAds (@NumAds int)
As
Begin
Exec('select top ' + @NumAds + ' from Ads')
End

 

[mikeblas]

T-SQL doesn't allow the numeric value in the TOP clause to be bound to a variable. You'll have to build a string including the literal value, again as FreiDog suggests. This becomes problematic because any parameters you want to put in the dynamic SQL must be literals and concatenated into the string. That's tedious and error-prone, plus it forgoes any benefits of binding you might have had.

The query you provide as a starting point is fundamentally flawed; selecting with TOP without an ORDER BY is nonsense. You'll get n rows, but which ones? Since you don't establish an order, you don't know which rows will come first, not to mention which ones will be the first n.

Note further that FreiDog's code and your starting point are subtly broken; they don't provide a column list for the SELECT, and will result in an error when run. This is another problem with dynamically created SQL; you don't get the benefit of syntax checking at compile time, and must actually exercise your code in a test case to see if it works.

One way to work around several of these issues is to use the ROW_NUMBER() function to get a derived row-number column, then build a WHERE clause over that to effect your limit. The WHERE can, of course, be bound to a normal variable, including your stored procedure's parameter.

You'll end up with something like this, then:

Create Procedure dbo.GetAds (@NumAds int)
As
BEGIN
   SELECT * FROM
      (SELECT *, ROW_NUMBER() OVER(ORDER BY ADID) AS 'RowNumber' FROM ADS) AS DerivedTable
   WHERE RowNumber <= @NumAds
END

Note that there are several reasons not to use "*" as the column list, but since we don't know anything about your metadata, then I'm obliged to do so. Same for the ADID column in the ORDER BY clause; I'm just guessing at a useful column to ORDER BY your result set.

Please let me know if you have any other questions about this issue.

 

[sparc]

In sql server 2005, try

select TOP(@NumAds) * from ads

Note the ( )

 

[Zinn]

I got it. SQL Server 2000 doesn't let you pass variables into a top function. Here's the workaround:

CREATE PROCEDURE GetAds

	@NumAds int = 1

AS

SET ROWCOUNT @NumAds 

	SELECT	ID
	FROM		Ads 

SET ROWCOUNT 0

GO

Here is the page I referenced: http://sqlserver2000.databases.aspfaq.com/how-do-i-use-a-variable-in-a-top-clause-in-sql-server.html

The query you provide as a starting point is fundamentally flawed; selecting with TOP without an ORDER BY is nonsense. You'll get n rows, but which ones? Since you don't establish an order, you don't know which rows will come first, not to mention which ones will be the first n.

Mike, thanks. I actually knew all of this, I was just posting an extremely simplified version of the SP to establish what was breaking and nothing else. I was avoiding building a query string and executing at all costs, as my job was to optimize this SP and specifically make it no longer set up this way. It started out as six pages of messy code and boiled down to one simple query by the time I figured out how much crack the original programmer had been smoking

 

[mikeblas]

SET ROWCOUNT has some problems, but now that you've disclosed your using SQL Server 2000, we know that the more efficient and desireable ROW_NUMBER() approach is not available to you.