Solved

Problem with triggering SP_EXECUTESQL with a @stmt-Parameter > 4000 characters ...

Posted on 2006-06-20
8
700 Views
Last Modified: 2012-05-05
Hi all,

My platform is Windows 2000 Server with a SQL Server 2000 running.

I hav a Stored Procedure on my SQL-Database which builds and executes a specific query dynamically using 'exec SP_EXECUTESQL @MySQLStatement' .

Therefor I declared a local var @SQL of type nvarchar(4000).
This variable is filled up with a large SQL-Statement (depending on some input-parameters of the Stored Procedure).

Now the problem is that in some cases my built SQL-Statement is getting larger than 4000 characters. And then I get an error of course because my SQL-Statement is not complete.

Is there a way to work around this problem and make use of another type of variable or do some conversion or ...?

I found something about ntext-variables, but I can't declare them as local vars in my Stored Procedure.

Anyone having a solution for my problem ?
Thanks in advance,

The Mayor.
0
Comment
Question by:wimmeyvaert
  • 4
  • 3
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16940995
I am not aware of workarounds.
the only thing I would check out is if you could not make the query shorter.
for example, if you use table names -> use short aliases
you might create views for this kind of things with shorted column names (for the internal use only)
etc
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16941037
wimmeyvaert,
> I found something about ntext-variables, but I can't declare them as
> local vars in my Stored Procedure.

Create the nText variable as an argument, and pass a null value from the front end, use this variable to store the dynamic string

CREATE Proc TestSP
@arg1 varchar(10)
,@nSQL ntext = null
AS
BEGIN
     SET @Nsql = ''
     SET @nSQL  = 'SELECT ...'

   EXEc  sp_ExecuteSQL @nText


END
GO



sp call will be like this

exec TestSP @arg1='tablename'
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 16941141
Hi,

@AngelIII : I aleardy make use of aliases
                 Maybe you have a point when you talk anout views. I will investigate this.

@aneeshattingal : Looks good. But I get an error when assigning something to the ntext-parameter. The error is 'Error 409 : THe assignment operator cannot take a ntext data type as an argument'.

I will do some investigation with the 2 comments I got this far. I'll get back from the moment I have news.

Thanks a lot for your fast support.

Best regards,

The Mayor.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 6

Author Comment

by:wimmeyvaert
ID: 16941647
Hi all,

I tried to work with a View instead, but it is not possible to setup a View because it is the SELECT-clause of my SQL-statement that takes the most characters.

I was not able to get aneeshattinga's code to work (so far).

Since the stored procedure is triggered from within a self written application (in Delphi), I made the workaround in my application.
I changed the Stored procedure so that it now returns the SQL-Statement instead of running it.
Therefor I make use of a varchar(8000) output parameter.

From within my application I now trigger the Stored Procedure and the catch the output-param. Next I trigger a new query which I fill with the value of the output-param of my Stored Proc.

I did it this way because I needed to have a quick solution for my problem (for urgent reporting tools used in our company).

This is working now, altough I prefer to have a final solution and make use of the  sp_ExecuteSQL from within my Store Procedure.

So I will leave this question open for some time and pray that there is someone out there who has a solution.

Best regards,

The Mayor.

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16941730
> I was not able to get aneeshattinga's code to work (so far).
I will try on this
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 16941778
read this

sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005, use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql despite you query string is longer, because you want to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():

DECLARE @sql1 nvarchar(4000),
        @sql2 nvarchar(4000),
        @state char(2)
SELECT @state = 'CA'
SELECT @sql1 = N'SELECT COUNT(*)'
SELECT @sql2 = N'FROM dbo.authors WHERE state = @state'
EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
                         N''@state char(2)'',
                         @state = ''' + @state + '''')
This works, because the @stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.

You can even use output parameters by using INSERT-EXEC, as in this example:

CREATE TABLE #result (cnt int NOT NULL)
DECLARE @sql1  nvarchar(4000),
        @sql2  nvarchar(4000),
        @state char(2),
        @mycnt int
SELECT @state = 'CA'
SELECT @sql1 = N'SELECT @cnt = COUNT(*)'
SELECT @sql2 = N'FROM dbo.authors WHERE state = @state'
INSERT #result (cnt)
   EXEC('DECLARE @cnt int
         EXEC sp_executesql N''' + @sql1 + @sql2 + ''',
                            N''@state char(2),
                               @cnt   int OUTPUT'',
                            @state = ''' + @state + ''',
                            @cnt = @cnt OUTPUT
         SELECT @cnt')
SELECT @mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.



the original article can be viewed from
http://www.sommarskog.se/dynamic_sql.html

0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 16941934
Hi aneeshattingal,

This could be a solution for me.
I test it out and let you know.

Thanks.

The Mayor.
0
 
LVL 6

Author Comment

by:wimmeyvaert
ID: 16949050
Thanks to all for your help/input !!!

Best regards,

The Mayor.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 95
MIcrosoft SQL 2014 Database Copy Question 16 51
Row insertion failed. Array 5 46
job schedule 8 16
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question