CODEWITHSUNDEEP
sql-servert-sql
alhpe
alhpe

Reputation: 1514

T-SQL Table Name as parameter

I had this code that doesn't scale well since the table name is hard coded. I would appreciate ideas about how to refactor it.

CREATE FUNCTION [dbo].[GetIntRowVersion] (@Id INT, @Table NVARCHAR(128)) RETURNS BINARY(8) AS
BEGIN
DECLARE @rowVersion AS BINARY(8)
   IF @Id = 0
      SELECT @rowVersion = 
      CASE
         WHEN @Table = 'Score' THEN (SELECT MAX([RowVersion]) FROM Score) 
         WHEN @Table = 'Department' THEN (SELECT MAX([RowVersion]) FROM Department)
         WHEN @Table = 'ContactType' THEN (SELECT MAX([RowVersion]) FROM ContactType)
         WHEN @Table = 'Region' THEN (SELECT MAX([RowVersion]) FROM Region)
      END
   ELSE
      SELECT @rowVersion = 
      CASE
         WHEN @Table = 'Score' THEN (SELECT TOP 1 [RowVersion] FROM Score WHERE Id = @Id) 
         WHEN @Table = 'Department' THEN (SELECT TOP 1 [RowVersion] FROM Department WHERE Id = @Id)
         WHEN @Table = 'ContactType' THEN (SELECT TOP 1 [RowVersion] FROM ContactType WHERE Id = @Id)
         WHEN @Table = 'Region' THEN (SELECT TOP 1 [RowVersion] FROM Region WHERE Id = @Id)
      END
RETURN @rowVersion
END

GO

Upvotes: 2

Views: 108

Answers (1)

gotqn
gotqn

Reputation: 43636

Something like this:

CREATE PROCEDURE [dbo].[GetIntRowVersion] 
(
    @Id INT
   ,@Table NVARCHAR(128)
)
AS
BEGIN;

    SET NOCOUNT ON;

    DECLARE @rowVersion AS BINARY(8);

    DECLARE @DynamicTSQL NVARCHAR(MAX);

    IF @Id = 0 
        BEGIN;
            SET @DynamicTSQL = N'SELECT @rowVersion =   (SELECT MAX([RowVersion]) FROM ' + @Table + ')';
        END;
    ELSE
        BEGIN;
            SET @DynamicTSQL = N'SELECT @rowVersion =  (SELECT TOP 1 [RowVersion] FROM ' + quotename (@Table) + ' WHERE Id = ' + CAST(@Id AS VARCHAR(12)) +');';
        END;

    EXEC sp_executesql @DynamicTSQL, N'@rowVersion AS BINARY(8) OUTPUT', @rowVersion = @rowVersion OUTPUT;

    SELECT @rowVersion AS[rowVersion];

    SET NOCOUNT OFF;

    RETURN;
END;

You need to use stored procedure as the functions are not allowed to perform CRUD operations over SQL tables and execute dynamic T-SQL statements (unless being SQL CLR functions).

You can use OUTPUT parameter of the procedure, too.

Upvotes: 1

Related Questions