sql server programming: CLR has a very limited set of usage for manipulating data. The most
prolific example I can think of is direct string manipulation (e.g. RegEx).
For the type of data access you are describing, you can certainly achieve
this with T-SQL and it will almost certainly outperform a similar solution
using CLR.

A



[quoted text, click to view]

[quoted text, click to view]

That really depends, what is a "load"? 10? 100? 100000? As the numbers
increase, it is certainly worthwhile to look at BULK INSERT or BCP rather
than calling a stored procedure that many times.

[quoted text, click to view]

BULK INSERT may or may not populate the percent_complete column in
sys.dm_exec_requests (I haven't checked). My guess is, no. Regular DML
statements (insert/update) do not. The next best thing I guess would be to
asynchronously select a count from the table you're inserting into, but
unless the table is marked with the connection or some way to identify that
you are the user that inserted this row, it could very well be an increasing
count from some other connection, and you may actually be blocked...

A

Hi, i am beginning SQL server programming and when reading a book on
the subject (very briefly, flicking through the book i saw a small
chapter and mention) i saw what looked like the ability to have C#
functions on the server as stored procedures, from memory it was
called CLR i belive?

What i would like to do, is have a ProcessPayroll stored procedure,
which would run on the server and calculate a set of pay slips for a
period (which would be a parameter to the procedure), it would create
pay slip records for each employee, dependant on number of hours
worked and additional allowances. It would be nice if i could write
this in C# as i am very familair with it already, and i would need to
make use of loops, branches etc (some employees have transport
allowances, housing etc, others dont), i know T-SQL has loops etc but
a) i dont know how powerfull it is, and b) i am very new to T-SQL!

The only other way i can think of is pulling all data off onto the
client, processing it in VBA (front end is access), and creating
payslip records back on the server, but this seems a total waste to me
as all data is being pulled and then sent back. Is it possible,
advisable and indeed the standard use of Stored Procedures being used
as i have described above?

Many thanks in advance

On Aug 29, 12:22 am, "Aaron Bertrand [SQL Server MVP]"
[quoted text, click to view]

Thanks for the reply, so for my scenario of generating a load of new
records would be ideal and is standard practice to use a stored
procedure on the server? How would i go about stopping the procedure
being called many times, and would it be possible to have a progress
report (as it will be generating a lot of records) - in other words
are there any callbacks on progress the front end could hook into on
the server?

Thanks again

(morleyc@gmail.com) writes:
[quoted text, click to view]

Toto, you are not in Kansas anymore.

No, there are no callbacks, and progress report is not really the same
thing anyway.

If you program in C# it may be easy, you set up a loop from 1 to whatever
and of you go.

In SQL you should, preferrably, work with a sets of data at a time. As
very simple case, say that you need update the salaries for all employees.
All get a 3% raise, but those who earn less than 20000 SEK/month get
a raise of 600. Since this raise is from 2007-03-31, employees who
left the company before this date should not be updated. This is how you
write this in SQL:

UPDATE employees
SET salary = CASE WHEN salary <= 20000
THEN salary + 600
ELSE salary * 1.03
END
WHERE enddate < '20070331'

Progress for this type of query is necessarily not meaningful, least
of all the query becomes more complex.

And why would you not write the above as a loop? The answer is performance.
If you would write the above as a loop and there are 10000 employees, it
could take several minutes for it to complete. The UPDATE statement will
complete in less than five seconds. (Probably subsecond).

Programming in SQL is a different mindset, and there is all reason
to learn the basics. Not only how, but also why.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at