Sql server Stored Procedure

Prem Chandhar 0 Reputation points
2023-01-23T16:36:00.52+00:00
HI,

Im Prem Chandhar.

I have created one SP.



Note--table name emp but i have given space between "em p" its allowed to create and alter the sp. i belive this is not correct.i feel its a bug.if im worng kindly correct me. if its correct then im not able to log like "invalid object" error message.


ALTER proc [dbo].[usp_errrohandling]

as begin
--SET NOCOUNT ON;  
SET XACT_ABORT ON;
begin try
		begin tran
			select * from 
/* Note--table name emp but i have given space between "em p" its allowed --to create and alter the sp. is this correct? */
		commit tran

		
END TRY
BEGIN CATCH
print 'log'
insert into errorlog
  SELECT 'There was an error! ' + ERROR_MESSAGE(),ERROR_LINE(),ERROR_PROCEDURE(),ERROR_NUMBER()
  
END CATCH
GO
end try
			
				print 'rollback'
			rollback tran

		end catch

end

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,480 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 117.1K Reputation points MVP
    2023-01-23T22:51:01.52+00:00

    If I understand you correctly, your question is why the CATCH block does not catch the error with the misspelled table name.

    This is a sore point with error handling in SQL Server. (And, not is the only sore point. Sigh. There are quite a few.)

    You cannot catch compilation errors in the scope they occur. In a way that make sense, since compilation errors usually are raised before you enter the scope.... But:

    CREATE PROCEDURE MyBadSp AS
    BEGIN TRY
       PRINT 'Starting'
       SELECT * FROM NoSuchTable
    END TRY
    BEGIN CATCH
       PRINT 'This is not printed'
    END CATCH
    go
    EXEC MyBadSp
    go
    DROP PROCEDURE MyBadSp
    
    

    When you run this, the output is:

    Starting Msg 208, Level 16, State 1, Procedure MyBadSp, Line 4 [Batch Start Line 9]
    Invalid object name 'NoSuchTable'.

    Because of deferred name resolution, the error is not detected at run-time. But you cannot still catch the error. Why? Ask Microsoft.

    Note that you can catch these type of errors in an outer scope:

    CREATE PROCEDURE MyBadSp AS
    BEGIN TRY
       PRINT 'Starting'
       SELECT * FROM NoSuchTable
    END TRY
    BEGIN CATCH
       PRINT 'This is not printed'
    END CATCH
    go
    CREATE PROCEDURE MyOuterSP AS
    BEGIN TRY
       PRINT 'Starting Outer'
       EXEC MyBadSp
       PRINT 'This does not print'
    END TRY
    BEGIN CATCH
       PRINT 'Caught: ' + error_message()
    END CATCH
    go
    EXEC MyOuterSP
    go
    DROP PROCEDURE MyBadSp, MyOuterSP
    
    

    This prints:

    Starting Outer Starting Caught: Invalid object name 'NoSuchTable'

    1 person found this answer helpful.
    0 comments No comments

  2. Bruce (SqlWork.com) 71,506 Reputation points
    2023-01-23T16:45:45.44+00:00

    two issues.

    tables names allow an alias:

    select * from foo bar

    bar is now an alias name for foo

    stored procs do not give create errors for missing tables. the error will only be caught when the sp is executed.

    0 comments No comments

  3. Jingyang Li 1 Reputation point
    2023-01-23T16:52:12.3833333+00:00

    You can access your table like this :

    select * from "em p"

    or

    Select * from [em p]

    0 comments No comments

  4. PercyTang-MSFT 12,511 Reputation points Microsoft Vendor
    2023-01-24T02:16:43.5133333+00:00

    Hi @Prem Chandhar

    Note--table name emp but i have given space between "em p" its allowed --to create and alter the sp. is this correct?

    Is this your problem?

    As Bruce said, there is a space between em and p, which makes the system think that the em table has an alias for p.

    If what you want is an emp table, and this space causes the system to think it's an em table, it's clear that this is not what you want.

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.