SQL with a table name as a parameter and a query longer than 4000 characters

Question

I am trying to write a stored procedure that takes a table name as a parameter. Yes, I already know that this is a security vulnerability, but it is an internal stored process that does not face the typical risks of SQL Injection.

What I have so far looks something like this:

CREATE PROCEDURE [dbo].[myprocedure]
    @tableName sysname
AS
DECLARE @cmd nvarchar(4000)
SET @cmd = N' Select blah blah from ' + @tableName
EXEC (@cmd)
GO

The query will work theoretically, but my problem is that my query is longer than 4000 characters. Is there any other way to use @tableName in a cmd variable longer than 4000 characters (which is equal to nvarchar max)?

Answer 1

SQL Server >= 2005, nvarchar(4000) nvarchar(MAX).

Answer 2

DECLARE @cmd NVARCHAR(MAX);

Answer 3

Extract some of your logic into views or user-defined functions.

Answer 4

Use

DECLARE @cmd VARCHAR(8000)

instead DECLARE @cmd NVARCHAR(MAX);

NVARCHAR(MAX) ALLOWS ONLY 4000 CHARACTERS.