none
SQL code is getting compiled even the columns are not matching in cursor declaration and fetch statement

    Question

  • 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

    Friday, June 28, 2013 8:57 AM

All replies

  • From BOL:

    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,

    http://msdn.microsoft.com/en-us/library/ms190686(v=sql.105).aspx

    http://dba.stackexchange.com/questions/13522/is-there-a-way-to-force-deferred-name-resolution-even-if-the-table-exists-when-c


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog



    • Edited by Dineshkumar Friday, June 28, 2013 9:06 AM     
    Friday, June 28, 2013 9:00 AM
  • Hi Dinesh,

    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.

    Regards,

    Bala

    Friday, June 28, 2013 9:10 AM
  • Bala,

    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.

    Friday, June 28, 2013 9:48 AM
  • 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

    www.sommarskog.se/strict_checks.html#cursors
    Friday, June 28, 2013 12:41 PM
  • Thats correct, if you are lucky, Erland would chime in and help you.... :)

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, June 28, 2013 12:50 PM
  • >SELECT EmpId, FName, DOB DOJ FROM Employee
    >OPEN  c_Emp
    >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

    Monday, July 08, 2013 11:25 PM
    Moderator
  • I installed one tool from www.sqlcodeguard.com It identified the issue. It compares the number of columns in cursor declaration with the number of columns in the Fetch statement.

    Thanks all your replies.

    Monday, July 15, 2013 11:40 AM



  • >> 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 
      FROM Personnel;

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

    Monday, July 15, 2013 6:46 PM
  • Hi,

    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.

    Tuesday, July 16, 2013 5:21 AM