ODBC Error - 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

SQL Server Development (2000)

ODBC Error

Author

Topic

rbarlow
Starting Member

26 Posts

Posted - 2007-05-11 : 18:01:11

I have a weird error.. I'm using a stored procedure to return a resultset through an ODBC connection.

Here is a procedure definition:

CREATE PROCEDURE spGetStudents @permission smallint
AS
BEGIN
	IF @permission = 1
		SELECT id, name FROM vStudentList
	ELSE
		SELECT name FROM vStudentList
END
go

When I try to loop through the resultset, I get an "ODBC error fetch type out of range" during the final MoveNext before EOF. I do a check for EOF before the MoveNext and it is false.

What is weird is that if I remove the logic in the stored procedure like so:

CREATE PROCEDURE spGetStudents @permission smallint
AS
BEGIN
	SELECT id, name FROM vStudentList
END
go

and run the same code then it executes perfectly. I would assume that both procedures would return the same resultsets because I call both of them with:

EXEC spGetStudents 1

Does anybody have any idea what is happening with this?

mattyblah
Starting Member

49 Posts

Posted - 2007-05-12 : 01:20:45

why not something like this?

CREATE PROCEDURE spGetStudents @permission smallint
AS
BEGIN
SELECT case @permission when 1 then id else null end as id, name FROM vStudentList
END
go

rbarlow
Starting Member

26 Posts

Posted - 2007-05-12 : 16:37:37

yeh that would probably work but I'm more concerned about why I got the error because I know there will be complicated procedures I will need to write in the future that will not only contain 1 SELECT statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources