Problem with the cursor declaration and fetch stmt

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 2008 Forums

Transact-SQL (2008)

Problem with the cursor declaration and fetch stmt

Author

Topic

subbu_msis
Starting Member

1 Post

Posted - 2013-06-28 : 06:46:26

Hi,

I have some stored procedure and there is a cursor inside it. I added some new columns to the table and those columns I included in the cursor declaration and fetch statement. In the cursor declaration I forgot to add comma (,) in between the new columns. So SQL Server it considers as a alias name for that column so syntactically it is correct. But logically in the cursor declaration having less number of columns than the columns in the fetch statement. So it should throw an error. But the procedure is getting compiled without raising any error. But if I execute the procedure that time it is throwing the error.

For example, below I have given the sample procedure. In this procedure, in the cursor declaration I removed the comma (,) between DOB and DOJ. If I compile this procedure it is getting compiled. But when execute that time only it is throwing the error. So I am interested in if any option is available to know the error in the compilation time itself.

ALTER PROCEDURE Test
AS
BEGIN
BEGIN TRY

DECLARE @empId INT,
@fname VARCHAR(50),
@dob DATE,
@doj DATE

DECLARE c_Emp CURSOR FAST_FORWARD FOR
SELECT EmpId, FName, DOB DOJ FROM Employee
OPEN c_Emp
FETCH NEXT FROM c_Emp INTO @empId,@fname,@dob,@doj
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT @fname
FETCH NEXT FROM c_Emp INTO @empId,@fname,@dob,@doj
END

CLOSE c_Emp
DEALLOCATE c_Emp
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
CLOSE c_Emp
DEALLOCATE c_Emp
END CATCH
END

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-28 : 09:32:45

SQL server assumes that you are aliasing a column when there is a comma missing between column names.
Try aliasing all your columns to avoid this problem.
For example try to do:
[CODE]
SELECT EmpId as [EmpId], FName as [FName], DOB as [DOB], DOJ as [DOJ] FROM Employee

[/CODE]
if you miss a ',' in the above statement you will immediately get an error.

rs3gold
Starting Member

2 Posts

Posted - 2013-06-28 : 22:49:57

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources