SQL with table name as parameter and query longer than 4000 characters
sql, sql-server, stored-procedures
Solution
If you use SQL Server >= 2005, try replacing `nvarchar(4000)` with `nvarchar(MAX)`.
Problem
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)?