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

Question

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 */

M.Ali · Accepted Answer · 2015-08-09 23:34:40Z

2

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

answered Aug 9, 2015 at 23:34

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1626874 Over a year ago

Cheers any way to do it without duplicating query statements

M.Ali Over a year ago

only other way is the dynamic sql then, here you are not duplicating the query only one of the queries will be executed.

user1626874 Over a year ago

Ok cheer I will make this one as the correct answer

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related