Tuesday, March 20, 2012

Bad Stored procedure

I get this error "Incorrect syntax near @.TopNews"
Here is my SP:
CREATE PROCEDURE sp_TopNews
@.TopNews int
AS
SELECT TOP @.TopNews [Title], [Content] FROM [Documents]
WHERE [DatePublication] >= GETDATE() AND [Type] = "News"
AND [DateArchived] = NULL

It's my first SP so it's probably a stupid error, can anyone help me ?You cannot use a variable for the number in TOP. You could use dynamic SQL.|||Based on http://www.sqlteam.com/item.asp?ItemID=233
Try this :


CREATE PROCEDURE sp_TopNews
@.TopNews int
AS

SET rowcount @.TopNews
SELECT [Title], [Content]
FROM [Documents]
WHERE [DatePublication] >= GETDATE() AND
[Type] = "News" AND
[DateArchived] = NULL
SET rowcount 0

GO

|||Of course, without an order by statement, this is worthless...|||Thanks that worked :)|||indeed

No comments:

Post a Comment