sql-serverdynamichints

Is there a way to dynamically control a table hint without using a dynamic sql query


For example:

CREATE PROCEDURE [dbo].[procGetTable]
    (
      @SetUPDLOCK BIT,
      @RecordId BigInt
    ) 
AS

SELECT * 
FROM MYTABLE WITH (CASE WHEN @SetUPDLOCK = 1 THEN 'UPDLOCK' ELSE '' END)
WHERE MYTABLE.RecordId = @RecordId

/* P.S. I Know the above does not work it conceptual only */


Solution

  • CREATE PROCEDURE [dbo].[procGetTable]
          @SetUPDLOCK BIT,
          @RecordId BigInt
    AS
    BEGIN
       SET NOCOUNT ON; 
    
        IF (@SetUPDLOCK = 1 )
          BEGIN
                SELECT * 
                FROM MYTABLE WITH (UPDLOCK)
                WHERE MYTABLE.RecordId = @RecordId
           END
        ELSE 
          BEGIN
                SELECT * 
                FROM MYTABLE
                WHERE MYTABLE.RecordId = @RecordId
           END
    END