Error in SQL query . Need help !!!

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

Transact-SQL (2000)

Error in SQL query . Need help !!!

Author

Topic

admin001
Posting Yak Master

166 Posts

Posted - 2004-08-30 : 06:04:11

Hi ,

I am creating a new SP , but I am getting an error message . The query is as follows :

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'GetMyOpenRequests'
AND type = 'P')
DROP PROCEDURE GetMyOpenRequests
GO

CREATE PROCEDURE GetMyOpenRequests
@maxRecords int(4),
@requestor_NTID string(12)
AS
IF @maxRecords = 0
BEGIN
SELECT [tblRequests].*, [tblCardTypes].[CardTypeName], [tblRequestStatus].[RequestStatusName]
FROM [tblRequests]
LEFT OUTER JOIN [tblCardTypes] ON [tblCardTypes].[PK_CardTypeID] = [tblRequests].[FK_CardTypeID]
LEFT OUTER JOIN [tblRequestStatus] ON [tblRequestStatus].[PK_RequestStatusID] = [tblRequests].[FK_RequestStatusID]
WHERE (([tblRequests].[RequestActive] = 1) AND ([tblRequests].[FK_RequestStatusID] = 1)
AND ([tblRequests].[Request_CardNo] IS NULL) AND ([tblRequests].[Requestor_NTID] = @requestor_NTID))
ORDER BY [tblRequests].[Request_Date] DESC;
END
ELSE
BEGIN
SELECT TOP @maxRecords [tblRequests].*, [tblCardTypes].[CardTypeName], [tblRequestStatus].[RequestStatusName]
FROM [tblRequests]
LEFT OUTER JOIN [tblCardTypes] ON [tblCardTypes].[PK_CardTypeID] = [tblRequests].[FK_CardTypeID]
LEFT OUTER JOIN [tblRequestStatus] ON [tblRequestStatus].[PK_RequestStatusID] = [tblRequests].[FK_RequestStatusID]
WHERE (([tblRequests].[RequestActive] = 1) AND ([tblRequests].[FK_RequestStatusID] = 1)
AND ([tblRequests].[Request_CardNo] IS NULL) AND ([tblRequests].[Requestor_NTID] = @requestor_NTID))
ORDER BY [tblRequests].[Request_Date] DESC;
END
GO

When I execute , I get a error message as
" Line 18: Incorrect syntax near '@maxRecords'."

Wondering what did I miss !!!

Thank you very much .

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-08-30 : 06:13:15

Select top @var isn't allowed.

Here are some answers http://www.sqlteam.com/item.asp?ItemID=233

Damian

gpl
Posting Yak Master

195 Posts

Posted - 2004-08-30 : 08:19:50

Try this article to fix it
http://www.sqlteam.com/item.asp?ItemID=233

curses, didnt refresh my browser
Graham

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources