Dynamic query with SQL statement longer than 8000 chars

I have a stored procedure generating a dynamic SQL that runs against Analysi
s
Services. The statement can be quite complicated and can be longer than 8000
.
I know I can use "exec (@.strSQL1 + @.strSQL2)" but since I don't know how man
y
@.strSQL variables I'll need, I was wondering if anybody has a better solutio
n
for this.
If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
that resembles a procedure in VB? I don't think UDFs would work here.
Thanks in advance for any help,
Carmen.Carmen (Carmen@.discussions.microsoft.com) writes:
> I have a stored procedure generating a dynamic SQL that runs against
> Analysis Services. The statement can be quite complicated and can be
> longer than 8000. I know I can use "exec (@.strSQL1 + @.strSQL2)" but
> since I don't know how many @.strSQL variables I'll need, I was wondering
> if anybody has a better solution for this.
Upgrade to SQL 2005, where you can use varchar(MAX) and you are free
of all hassle.

> If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
> that resembles a procedure in VB? I don't think UDFs would work here.
If it's that bad, it may be better to build the query that has better
string capabilities, for instance VB.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Which parts of your SQL statement are dynamic? Is it possible that some of
your dynamic SQL could be done another way? Perhaps using a temp table in
place of a list for an in clause, or splitting parts of the dynamic SQL off
into views? I can only guess at what you are doing but I have to believe
that if you are getting over 16000 characters in your SQL that there must be
ways to simplify it and make the final SQL more concise.
Sometimes we get so concerned with making the current process work as
originally designed, and we overlook that there are flaws in the design.
Once we redesign the process to remove those flaws we find out that what we
thought was a problem was really only a symptom of another problem. This
may or may not be the case here, but it is worth considering nonetheless.
"Carmen" <Carmen@.discussions.microsoft.com> wrote in message
news:A7D9B446-D779-4A8B-8823-5701137660C3@.microsoft.com...
> I have a stored procedure generating a dynamic SQL that runs against
Analysis
> Services. The statement can be quite complicated and can be longer than
8000.
> I know I can use "exec (@.strSQL1 + @.strSQL2)" but since I don't know how
many
> @.strSQL variables I'll need, I was wondering if anybody has a better
solution
> for this.
> If I do have to use @.strSQL1, @.strSQL2, etc..., is there anything in T-SQL
> that resembles a procedure in VB? I don't think UDFs would work here.
> Thanks in advance for any help,
> Carmen.sql