SQL with table name as parameter and 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 this is a security vulnerability, but this is an internal stored proc that doesn't face typical risks of SQL Injection.

What I have so far is something like the following:

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 in theory, but my problem is that my query is longer than 4000 characters. Is there another way to use @tableName in a cmd variable longer than 4000 characters (which is nvarchar's max)?

Answer 1

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

Answer 2

If you use SQL Server >= 2005, try replacing nvarchar(4000) with nvarchar(MAX).

Answer 3

DECLARE @cmd NVARCHAR(MAX);