Problem with stored procedure - SQL Server Forums

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)

Problem with stored procedure

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2005-12-21 : 13:30:02

Hi,
I got a stored procedure which is as following:
CREATE PROCEDURE sp_parmSailorRanking
@ClubCode varchar(50),
@Rank varchar(10)
AS
SELECT People.PeopleID, People.PeopleNameLast, People.PeopleNameFirst,
People.PeopleProfessionalClass, People.PeopleClubCode, Rank.Year, Rank.Month,
Rank.Rank
FROM People INNER JOIN Rank ON People.PeopleID = Rank.SailorID
WHERE (((People.PeopleClubCode)=@ClubCode) AND ((Rank.Rank)<=@Rank))
ORDER BY Rank.Year, Rank.Month, Rank.Rank;

When I am issuing a command as below:
Exec sp_parmSailorRanking 'SCOW', 10
I am getting message stating 18 rows updated for a number of times and then
the following error message comes up:

Server: Msg 217, Level 16, State 1, Procedure sp_parmSailorRanking, Line 11
Maximum stored procedure, function, trigger, or view nesting level exceeded
(limit 32).

However, if I just run the sql select code with the same parameter which
comes to as
SELECT People.PeopleID, People.PeopleNameLast, People.PeopleNameFirst,
People.PeopleProfessionalClass, People.PeopleClubCode, Rank.Year, Rank.Month,
Rank.Rank
FROM People INNER JOIN Rank ON People.PeopleID = Rank.SailorID
WHERE (((People.PeopleClubCode)='SCOW') AND ((Rank.Rank)<=10))
ORDER BY Rank.Year, Rank.Month, Rank.Rank;

I get a resultset showing various columns and rows as expected. I would
appreciate what's wrong when I run the stored procedure from the QA. Thanks
in advance.

jhermiz

3564 Posts

Posted - 2005-12-21 : 13:43:22

why is @Rank of type varchar ??? is it supposed to be ??

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

gamaz
Posting Yak Master

104 Posts

Posted - 2005-12-21 : 15:39:04

Thanks for your help jhermiz. I appreciate it. I had inadvertently written varchar for rank which would be int instead. With this change the sp can be executed with the same parameters and it is now giving the desired result (same as the sql statement with values of parameters embeded. Best regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources