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)?

Answers


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.


Use

DECLARE @cmd VARCHAR(8000)

instead of DECLARE @cmd NVARCHAR(MAX);

NVARCHAR(MAX) ALLOWS ONLY 4000 CHARACTERS.

Need Your Help

How to perform multiple write from single item/line using Spring-Batch?

spring-batch

How can I perform multiple write from single item with the given data using spring-batch?

Set cURL address via POST variable

php ajax curl

I have been trying to use cURL in PHP with a dynamic address (i.e. set via a POST variable) but it isn't working!