[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Dynamic query execution.

Archana

3/22/2007 1:42:00 PM

Hi all,

I want to build one update query dynamically.

Length of my update query is more than 8000 character. What should i
do to execute this query.

Because using exec i am getting error.

Any help will be truely appreciated.
5 Answers

Uri Dimant

3/22/2007 1:52:00 PM

0

Try
SET @sql1='UPDATE Table SET col=......
SET @sql2=', col=....'

EXEC(@sql1+@sql2)





"Archana" <trialproduct2004@yahoo.com> wrote in message
news:1174570925.738545.257200@l75g2000hse.googlegroups.com...
> Hi all,
>
> I want to build one update query dynamically.
>
> Length of my update query is more than 8000 character. What should i
> do to execute this query.
>
> Because using exec i am getting error.
>
> Any help will be truely appreciated.
>


--CELKO--

3/22/2007 3:38:00 PM

0

>> I want to build one update query dynamically. <<

Why not put it into a stored procedure instead? Dynamic SQL is a way
of telling the world that your design is so bad you have no idea what
to do and must hand over your job to any random user at run time.

Tony Rogerson

3/22/2007 4:16:00 PM

0

> Why not put it into a stored procedure instead? Dynamic SQL is a way
> of telling the world that your design is so bad you have no idea what
> to do and must hand over your job to any random user at run time.

Well, thats an improvement - at least you've stopped saying dynamic SQL is
Slower.

As my entry debunking your pass 1000 parameters instead of XML / CSV proves,
the process with dynamic SQL takes 27 seconds, without it takes 350 seconds
or 384 seconds; as you can see dynamic SQL is orders of magnitude faster.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/17/joe-celko-don-t-use-csv-xml-use-1-000-parameters-in...

Yet another useful use of dynamic SQL to get a maintainable and a system
that actually performs with any realism.

Personally, I think you (like the parameters thing) completely misunderstand
why and how dynamic SQL is being used - you are too rigid in your approach
to "development" and "design"; portability is not the overwelming goal in
the system life cycle - its a nice to have, performance, supportability,
scalability and cost to develop is the goal.

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1174577899.076028.109680@y80g2000hsf.googlegroups.com...
>>> I want to build one update query dynamically. <<
>
> Why not put it into a stored procedure instead? Dynamic SQL is a way
> of telling the world that your design is so bad you have no idea what
> to do and must hand over your job to any random user at run time.
>

AK

3/22/2007 4:37:00 PM

0

On Mar 22, 10:38 am, "--CELKO--" <jcelko...@earthlink.net> wrote:
> >> I want to build one update query dynamically. <<
>
> Why not put it into a stored procedure instead? Dynamic SQL is a way
> of telling the world that your design is so bad you have no idea what
> to do and must hand over your job to any random user at run time.

You missed the point completely. Learn the basics from an MVP:

http://www.sommarskog.se/dynami...

Tony Rogerson

3/22/2007 5:17:00 PM

0

> EXEC(@sql1+@sql2)

You might want to put a space in there....

EXEC( @sql1 + ' ' + @sql2 )

Tony.


"Uri Dimant" <urid@iscar.co.il> wrote in message
news:e3BS5lIbHHA.1400@TK2MSFTNGP06.phx.gbl...
> Try
> SET @sql1='UPDATE Table SET col=......
> SET @sql2=', col=....'
>
> EXEC(@sql1+@sql2)
>
>
>
>
>
> "Archana" <trialproduct2004@yahoo.com> wrote in message
> news:1174570925.738545.257200@l75g2000hse.googlegroups.com...
>> Hi all,
>>
>> I want to build one update query dynamically.
>>
>> Length of my update query is more than 8000 character. What should i
>> do to execute this query.
>>
>> Because using exec i am getting error.
>>
>> Any help will be truely appreciated.
>>
>
>