Solved

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

Posted on 2006-06-20
8
673 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 142

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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now