sp_executesql does not validate the query string

Question

Today I faced the worst situation. There is a stored procedure in database, I had to alter it, using inline query,

Everything went fine by executing this query:

DECLARE @script AS NVARCHAR(MAX)

SET @script = 'ALTER PROCEDURE GetALL
               with recompile
               AS
               BEGIN
                   SELECT *  
                   FROM dbo.Main
                   INNER JOIN dbo.alabla ON dbo.Main.ID = dbo.ATTENDANCE.EMPLOYEE_ID
               END'

EXEC sp_executesql @script ;

Above query has a syntax error alabla, the table does not exists but EXEC sp_executesql @script ; performed without any error.

and when I tried to use this stored procedure, I get an error:

Msg 208, Level 16, State 1, Procedure GetALL, Line 5
Invalid object name 'dbo.alabla'.

Can anybody tell here that how we make sure that inline query execution is safe?

Answer 1

Have a look here: How to precompile stored procedures in SQL server?

sp_recompile will only mark a method to be recompiled the next time it is called...

If your newly created procedure does not manipulate data (your example above) you might just call it immediately...

Best hint could be to have a look on SET FMTONLY even if it's deprecated (https://msdn.microsoft.com/de-de/library/ms173839(v=sql.120).aspx) or you might use sp_describe_first_result_set (and related: see the links on the page mentioned).

This should force an immediate compilation...

Furthermore have a look on

• DBCC CHECKDB
• DBCC FREEPROCCACHE
• DBCC PROCCACHE

btw: I would not use a SP if you only want to read data. Your example above would be a perfect candidate for an inlineable (single-statement, ad-hoc) TVF...