SQL code is getting compiled even the columns are not matching in cursor declaration and fetch statement
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
DECLARE @empId INT,
DECLARE c_Emp CURSOR FAST_FORWARD FOR
SELECT EmpId, FName, DOB DOJ FROM Employee
FETCH NEXT FROM c_Emp INTO @empId,@fname,@dob,@doj
FETCH NEXT FROM c_Emp INTO @empId,@fname,@dob,@doj
When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the sys.sql_modules catalog view.
Check about Deffered Name Resolution of sql server, read this link below,
- Edited by Dineshkumar Friday, June 28, 2013 9:06 AM
Thanks for your reply. Yes I do agree when the stored procedure is created the statements in the procedure are parsed for syntactical accuracy.
But in my case in the cursor declaration has less number of columns than the columns in the Fetch statement. So this is not correct as per the cursor definition. So I want to know is there a way to know the error in the compilation time itself.
I think, the select statement is not the part of the CURSOR declaration syntactical check. Say, If you miss FOR in cursor declaration it will give you an error and can not be created the proc. Other case is that even you are not assigning the values to local variable, you will be able to create the procedure, however, it will show you an error message while executing.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
Thanks Latheesh for your reply.
So you mean't to say there is no way to know this type of issues in the compilation time itself? Is there any 3rd party tool will help me?
I read some article on the below link about SET STRICT_CHECKS ON. I think the author has submitted the idea to Microsoft team.www.sommarskog.se/strict_checks.html
>SELECT EmpId, FName, DOB DOJ FROM Employee
>FETCH NEXT FROM c_Emp INTO @empId,@fname,@dob,@doj
You just created an execution time error by defining DOJ as alias for DOB.
Generally every computer language has compile time and execution time errors, T-SQL is no exception.
The real question is: do you have to use cursor? Or set-based operations can do the job? Thanks.
Kalman Toth Database & OLAP Architect sqlusa.com
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012
>> 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. <<
You should write no more than five cursors in your entire career. putting such a kludge in a procedure is a symptom of awful coding practices. It tells the world you cannot do declarative programming and have to mimic a 12950's magnetic tape model of data.
For example, an SQL programmer would have a “Personnel” table because that is the name of a set; a mag tape programer would use “Employee” because you think in terms of record by record processing. In declarative programming, we do not use local variables. An employee identifier would never be an INTEGER; what math are you doing with it? Where is the check digit? And you have no idea what ISO-11179 rules for data element names are.
>> 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. <<
The 1970's Sybase/UNIX T-SQL compiler is a simple one-pass compiler. It does not check a lot of things until run time. In fact, the SQL model is to defer execution for as long as possible so that the plan is built with the most current schema information.
What were you actually trying to do? That cursor is simply:
SELECT emp_id, first_name, birth_date, junk_date
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
In the above, I have given the cursor as an example. The cursor which we used in the code is different and that follows all the coding standards. Sorry for my above example, my intention is I don't want to share my actual code.
In our development we have some existing cursor (it was written some years ago) and there is a requirement to add new column. So while adding the new column to the cursor I missed the comma (,) in the cursor declaration but in Fetch statement I added correctly. So now the problem is the columns count in the cursor declaration and cursor definition are not matching. I compiled my procedure and it successfully got compiled. But during runtime it threw an error.
So I was looking for some static code analyzer which should tell me the number of columns in the cursor declaration and the fetch statement are not matching.