Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2013
    Posts
    2

    Unanswered: SQL code is getting compiled even the columns are not matching in cursor declaration

    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

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Creating a stored procedure is not "compiling" in the sense that a C or Java program is compiled. To find this error, you would have to run the procedure. Do you have a QA database server to test these sorts of things on?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Depending on which edition of SQL Server you are running, SSMS has a good debugger that I think can catch this kind of problem. I don't use it very often, so I'm not sure which editions/versions include it and what its features are on each combination.

    There used to be a "public domain" SQL "Lint" utility.

    I know that TOAD had a good syntax checker that catches these kind of errors, but I haven't used it in the SQL Server environment for ages. A number of the other code-centric tools for working with SQL Server include a lint-like feature (Microsoft Visual Studio used to have a pretty good one).

    If that fails, check out the SSMS Tools Pack. Its code pretty-fier might catch this kind of error, and Mladen is quite good about responding to user requests if it doesn't catch it yet!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jun 2013
    Posts
    2
    I installed one tool from SqlCodeGuard - free tool for T-SQL Analyse 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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •