Who and how writes very long scripts in T-SQL? Do we have anything more
convenient than M$ SQL Query Analyzer? I finally started writing my scripts
in WinWord because of the terrible despair. It at least allows me to use
formatting, color selection, block separation, etc. Then I can copy/paste
this script into SQL Query Analyzer to check if it's correct. I realize that
it's crazy, but the Visual Studio IDE, regarding to the T-SQL scripts, is a
simple plain editor, I can't hide blocks, I can't use colors, nothing that I
can do for C#. The long script finally becomes a mess, terrible plain text
unreadable or very hardly readable. I'm talking about the very long scripts
including some general logic, variables, counters, etc., the length of such
a script can be a few thousand lines of the formatted SQL code.
Any advice?
Just D.> a script can be a few thousand lines of the formatted SQL code.
Well that is your fist and biggest mistake. TSQL is not a programming
language and should not be long and drawn out. At the very least break it
down into several manageable stored procedures. This is optimized for SET
based processing of data which should not take 1000 lines of code or more.
Andrew J. Kelly SQL MVP
"Just D." <no@.spam.please> wrote in message
news:LtHXe.131841$Ji4.30207@.fed1read03...
> All,
> Who and how writes very long scripts in T-SQL? Do we have anything more
> convenient than M$ SQL Query Analyzer? I finally started writing my
> scripts in WinWord because of the terrible despair. It at least allows me
> to use formatting, color selection, block separation, etc. Then I can
> copy/paste this script into SQL Query Analyzer to check if it's correct. I
> realize that it's crazy, but the Visual Studio IDE, regarding to the T-SQL
> scripts, is a simple plain editor, I can't hide blocks, I can't use
> colors, nothing that I can do for C#. The long script finally becomes a
> mess, terrible plain text unreadable or very hardly readable. I'm talking
> about the very long scripts including some general logic, variables,
> counters, etc., the length of such a script can be a few thousand lines of
> the formatted SQL code.
> Any advice?
> Just D.
>|||Ok,
Let's ask how to write a 1000-line T-SQL code in the most convenient way.
You suppose that I'd better call the SP from this SP or Trigger using EXEC
etc.? And how fast it should run? Another one bottle neck is the parameters
that in case of EXEC SP should be stored somewhere and sent to this SP, then
something returned back. It will not increase the speed of the script
itself.
Just D.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ehG$XDXvFHA.724@.TK2MSFTNGP14.phx.gbl...
> Well that is your fist and biggest mistake. TSQL is not a programming
> language and should not be long and drawn out. At the very least break it
> down into several manageable stored procedures. This is optimized for
> SET based processing of data which should not take 1000 lines of code or
> more.
> --
> Andrew J. Kelly SQL MVP
>
> "Just D." <no@.spam.please> wrote in message
> news:LtHXe.131841$Ji4.30207@.fed1read03...
>|||Just,
Stored procedures are faster. The reduce the overall network traffic for
each call and optimize the execution for subsequent calls on the SQL Server.
HTH
Jerry
"Just D." <no@.spam.please> wrote in message
news:XZHXe.131843$Ji4.74359@.fed1read03...
> Ok,
> Let's ask how to write a 1000-line T-SQL code in the most convenient way.
> You suppose that I'd better call the SP from this SP or Trigger using EXEC
> etc.? And how fast it should run? Another one bottle neck is the
> parameters that in case of EXEC SP should be stored somewhere and sent to
> this SP, then something returned back. It will not increase the speed of
> the script itself.
> Just D.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ehG$XDXvFHA.724@.TK2MSFTNGP14.phx.gbl...
>|||Maybe I didn't explain correctly, the script should work itself as a SP or a
Trigger, so it will be compiled and optimized.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eyfR%23QXvFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Just,
> Stored procedures are faster. The reduce the overall network traffic for
> each call and optimize the execution for subsequent calls on the SQL
> Server.
> HTH
> Jerry
> "Just D." <no@.spam.please> wrote in message
> news:XZHXe.131843$Ji4.74359@.fed1read03...
>|||I guess the first question that comes to my mind is what are you trying
to do that requires such a lengthy script? As Andrew pointed out,
T-SQL is optimized for set-based solutions (ie., doing a lot of things
at one time) as opposed to a procedural programming language. It
sounds as if (based on your short description) you're trying to write a
step-by-step operation. In most cases, there is another way, but
without knowing what you are attempting to do, that's a difficult call.
As far as your first question, I don't know of any better editor than
query analyzer. But I do think that having a master procedure call a
series of sub-procedures is one method of breaking your code up into
easily editable sections.
So, what are you trying to do?
Stu|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:553583
This task has already been working as a SP in a scheduled job every n
minutes and survived for longer than a year. I just need to rewrite it
because the format of the database is modified and it should be reflected in
the local trigger itself. What exactly..? Just a sync script that uploads
the data from one db to another db. Could be simple if the format of these
DBs was equal, but not in this case, I need to transfer different types of
data converting then and the calls themselves to be accepted by the target
database. The best way to go for us was using a scheduled job. I would not
like a system service doing these things or some external app, it's my way
to go, suppose not discussible here. But we have already forgotten the first
question. And I suppose that if there is no ideas it's better to skip the
question instead of starting this discussion in a different direction.
Thanks,
Just D.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1127173889.928153.17340@.z14g2000cwz.googlegroups.com...
>I guess the first question that comes to my mind is what are you trying
> to do that requires such a lengthy script? As Andrew pointed out,
> T-SQL is optimized for set-based solutions (ie., doing a lot of things
> at one time) as opposed to a procedural programming language. It
> sounds as if (based on your short description) you're trying to write a
> step-by-step operation. In most cases, there is another way, but
> without knowing what you are attempting to do, that's a difficult call.
> As far as your first question, I don't know of any better editor than
> query analyzer. But I do think that having a master procedure call a
> series of sub-procedures is one method of breaking your code up into
> easily editable sections.
> So, what are you trying to do?
> Stu
>|||Not to keep beating a dead horse, but have you considered DTS for the
basis of your job? It's much more flexible for data transformation (as
you describe), and it's usually faster than running a stored procedure
to do complex transformations.
I don't think that anyone has forgotten your first question, btw; it's
just that most of us have a belief that you should use the right tool
for the job. In most cases, T-SQL is not the best choice for complex
procedural transformations; there are exceptions, but they should
remain exceptions.
I'll get off my soapbox now; I'm not trying to lecture you, but I
didn't want you to walk away thinking that your question went
unanswered. It may be that it's not the best approach.
Stu|||You know, maybe you're right, but the solution was written so far ago and
still exists and even works. Maybe Sybase ASA or ASE with its built-in
synchronization is much better, I know enough about it, but sometimes we're
not choosing or even taking a tool, we're just using it. Yes, I got a
Licensed box with ASA9 from Sybase that they presented me for the critical
bug that I found in their mobile engine, I'm still sure that Sybase is the
best appropriate database for what I'm doing now, but keeping the point and
being in this newsgroup where Sybase definitely is off-topic, it's better to
skip that as well. Finally we're not responsible for the clients whose data
I'm syncing now. And this is a problem, not so huge, but solvable using
different approaches, that I'm doing right now.
Thanks,
Just D.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1127176774.800960.61880@.f14g2000cwb.googlegroups.com...
> Not to keep beating a dead horse, but have you considered DTS for the
> basis of your job? It's much more flexible for data transformation (as
> you describe), and it's usually faster than running a stored procedure
> to do complex transformations.
> I don't think that anyone has forgotten your first question, btw; it's
> just that most of us have a belief that you should use the right tool
> for the job. In most cases, T-SQL is not the best choice for complex
> procedural transformations; there are exceptions, but they should
> remain exceptions.
> I'll get off my soapbox now; I'm not trying to lecture you, but I
> didn't want you to walk away thinking that your question went
> unanswered. It may be that it's not the best approach.
> Stu
>|||This link might help; certainly on topic : - )
[url]http://www.
dba.com/[/url]"Just D." <no@.spam.please> wrote in message
news:jhJXe.131850$Ji4.111606@.fed1read03...
> You know, maybe you're right, but the solution was written so far ago and
> still exists and even works. Maybe Sybase ASA or ASE with its built-in
> synchronization is much better, I know enough about it, but sometimes
> we're not choosing or even taking a tool, we're just using it. Yes, I got
> a Licensed box with ASA9 from Sybase that they presented me for the
> critical bug that I found in their mobile engine, I'm still sure that
> Sybase is the best appropriate database for what I'm doing now, but
> keeping the point and being in this newsgroup where Sybase definitely is
> off-topic, it's better to skip that as well. Finally we're not responsible
> for the clients whose data I'm syncing now. And this is a problem, not so
> huge, but solvable using different approaches, that I'm doing right now.
> Thanks,
> Just D.
>
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1127176774.800960.61880@.f14g2000cwb.googlegroups.com...
>
