Stored procedure - Delay validation

Question

Stored procedure - Delay validation

Pete Bishop

Default port

Points: 1491
More actions
March 8, 2025 at 5:10 pm

#4554139
I have a utility stored procedure of the form:
```
CREATE PROCEDURE
    [dbo].[sp_Utility]
        @rule [varchar](8),
        @result [int] OUTPUT
AS
BEGIN
    IF @rule = 'Rule1'
    BEGIN
        SELECT @result = [result] FROM [dbo].[table1] OPTION (TABLE HINT([f], INDEX ([idx_table1]), NOLOCK))
    END

    IF @rule = 'Rule2'
    BEGIN
        SELECT @result = [result] FROM [dbo].[table2] OPTION (TABLE HINT([f], INDEX ([idx_table2]), NOLOCK))
    END
END
GO
```
If the [idx_table1] index is disabled and I run:
```
DECLARE @result [int]
EXEC [dbo].[sp_Utility] 'Rule2', @result OUTPUT
SELECT @result
```
I get the error:
Msg 315, Level 16, State 1, Procedure master.dbo.sp_Utility, Line 9 [Batch Start Line 0]
Index "idx_table1" on table "master.dbo.table1" (specified in the FROM clause) is disabled or resides in a filegroup which is not online.
This error occurs despite the fact that this section of code isn't being executed and any other code in the stored procedure is ignored whether before or after the IF statements. BEGIN TRY has no impact on the problem.
Is there any way to delay validation of a stored procedure until specific code is executed? This isn't a syntax issue - which I would expect to be flagged.
Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

I think the only way you can get that 'delayed validation' would be to use dynamic SQL.

With that said - the real problem here is that you are even using table/index hints in the first place. In some very rare cases - using a table/index hint would be needed and this is probably not one of those cases. I also see you have NOLOCK in place and you really need to be careful about including that.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Pete Bishop Default port Points: 1491 More actions · Answer 2

Hi Jeffrey.

Thanks for the feedback. In my *real* use case, the index hint *is* needed or the query takes an age to run whereas with the index hint it's pretty quick.

This may be due to poor index management but that's outside of my control - end-user managed tables - so I'm having to force the index to be used.

Also with the NOLOCK, the consequences of doing that are acceptable in the real use case.

Pete Bishop Default port Points: 1491 More actions · Answer 3

My "resolution" was to change the code to something of the form:

CREATE FUNCTION
    [dbo].[fnc_Rule1]()
RETURNS
    [int]
AS
BEGIN
    DECLARE @result [int]
    SELECT @result = [result] FROM [dbo].[table1] OPTION (TABLE HINT([f], INDEX ([idx_table1]), NOLOCK))
    RETURN @result
END

CREATE FUNCTION
    [dbo].[fnc_Rule2]()
RETURNS
    [int]
AS
BEGIN
    DECLARE @result [int]
    SELECT @result = [result] FROM [dbo].[table2] OPTION (TABLE HINT([f], INDEX ([idx_table2]), NOLOCK))
    RETURN @result
END

CREATE PROCEDURE
    [dbo].[sp_Utility]
        @rule [varchar](8),
        @result [int] OUTPUT
AS
BEGIN
    IF @rule = 'Rule1'
    BEGIN
        SELECT @result = [dbo].[fnc_Rule1]()
    END

    IF @rule = 'Rule2'
    BEGIN
        SELECT @result = [dbo].[fnc_Rule2]()
    END
END
GO

Not the greatest solution but this is quite niche and at least it works - and the rule-specific code can be modified in isolation.