SQL Server stored procedure - get table name from table to use in query without using dynamic sql-CodePudding

I have a situation where my database tables all have a certain GUID after the table name, and each time there is an update/upgrade, the GUID changes. As such, this creates a complication with stored procedures as I would have to get the current GUID (from a table it's stored in) at the beginning of the stored proc and append it to the table name in order to query it. I would really really like to avoid dynamic sql for all the reasons known to most of us. Is there any way around this without dynamic sql?

Thanks in advance

CodePudding user response：

One method is to create a synonym with a constant name for use in the stored procedure and recreate the synonym whenever a new table is created.

CREATE OR ALTER PROC dbo.GetFoo
AS
SELECT Col1 FROM Bar;
GO

CREATE TABLE dbo.[Bar-3BFBBE1D-AB03-4F08-AE20-F58F86E79685](Col1 int)
GO
DROP SYNONYM IF EXISTS dbo.Bar;
CREATE SYNONYM dbo.Bar FOR dbo.[Bar-3BFBBE1D-AB03-4F08-AE20-F58F86E79685];
GO
EXEC dbo.GetFoo;
GO

CREATE TABLE dbo.[Bar-BDB462D8-FF8E-4009-A756-71D7B4CE3D07](Col1 int)
GO
DROP SYNONYM IF EXISTS dbo.Bar;
CREATE SYNONYM dbo.Bar FOR dbo.[Bar-3BFBBE1D-AB03-4F08-AE20-F58F86E79685];
GO
EXEC dbo.GetFoo;
GO

CodePudding user response：

Your concern about dynamic SQL is misplaced.

Since your table name is stored in a table, and hopefully it's a uniqueidentifier type, and this isn't coming from user input, what is the problem with this approach? Where do you see any danger?

DECLARE @tablename sysname, @sql nvarchar(max);

SELECT @tablename = QUOTENAME(GUIDcolumn)
  FROM dbo.BrittleTable; -- WHERE ...

SET @sql = N'SELECT whatever FROM '   @tablename ...;
EXEC sys.sp_executesql @sql;

If it's not a uniqueidentifier type (why isn't it?), and you're worried someone could shove something else in this column, then you can do this:

DECLARE @tablename sysname, @sql nvarchar(max);

SELECT @tablename = QUOTENAME(GUIDcolumn)
  FROM dbo.BrittleTable
  WHERE TRY_CONVERT(uniqueidentifier, GUIDcolumn) IS NOT NULL;

IF @tablename IS NOT NULL
BEGIN
  SET @sql = N'SELECT whatever FROM '   @tablename ...;
  EXEC sys.sp_executesql @sql;
END

The presence of dynamic SQL does not equal the presence of danger. Like anything, it can be misused, but it can also be used responsibly with great success and zero risk. I've put together a rebuttal for claims of "we don't want to use dynamic SQL because we've heard stories" here:

Dynamic SQL

But the most important ones to read are these two tips: