Solved

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

Posted on 2006-06-20
8
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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