Executing Long Dynamic SQL

Introduction

When we are working with vary long dynamic SQL we often face some problem, when we execute the statement. So here is a simple question is in mind what to use for executing a long dynamic query.

In this article we are trying to clarify some point related to long dynamic SQL Execution.

What to Choose EXEC or sp_executesql

If we choose sp_executesql it takes not more than 4000 character.

Why?

sp_executesql statements parameter is a Unicode staring. So it takes maximum string length as 8000 bytes. Each character of Unicode takes 2 bytes so the maximum length it can takes is 4000 character.

What about EXEC

The EXEC has no limitation on other hand. But we have to consider the security part of sql.

So what to do?

To understand it here is a simple example.

DECLARE @sql1 VARCHAR(max)

DECLARE @sql2 VARCHAR(max)

SET @sql1 = 'long string part -1'

SET @sql2 = 'long string part -2'

EXEC (@sql1 + @sql2)

Hope you like it.

Posted by: MR. JOYDEEP DAS