SQL with table name as parameter and query longer than 4000 characters
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)?
If you use SQL Server >= 2005, try replacing nvarchar(4000) with nvarchar(MAX).
DECLARE @cmd NVARCHAR(MAX);
Extract some of your logic into views or user defined functions.
DECLARE @cmd VARCHAR(8000)
instead of DECLARE @cmd NVARCHAR(MAX);
NVARCHAR(MAX) ALLOWS ONLY 4000 CHARACTERS.