pulling my hair out over udf and dynamic SQL

Hi, I hope someone can help me.
I have a stored procedure that creates some dynamic SQL which is used
to populate a payroll system with timesheets information. As part of
the SQL that is created a udf is called to calculate the employees base
pay for the month.
the issue is that if I execute the query string using the exec command
the udf does not fire, however if I copy the querystring into QA and
run it, the results are fine, any pointers here would be great.
Thanks
SimonADO? If so, try SET NOCOUNT ON. If not, can you repro the "not firing" scena
rio in QA?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<simon.wilkinson@.cohenschemist.co.uk> wrote in message
news:1127406711.829918.196100@.g49g2000cwa.googlegroups.com...
> Hi, I hope someone can help me.
> I have a stored procedure that creates some dynamic SQL which is used
> to populate a payroll system with timesheets information. As part of
> the SQL that is created a udf is called to calculate the employees base
> pay for the month.
> the issue is that if I execute the query string using the exec command
> the udf does not fire, however if I copy the querystring into QA and
> run it, the results are fine, any pointers here would be great.
> Thanks
> Simon
>|||post your SQL statements
<simon.wilkinson@.cohenschemist.co.uk> wrote in message
news:1127406711.829918.196100@.g49g2000cwa.googlegroups.com...
> Hi, I hope someone can help me.
> I have a stored procedure that creates some dynamic SQL which is used
> to populate a payroll system with timesheets information. As part of
> the SQL that is created a udf is called to calculate the employees base
> pay for the month.
> the issue is that if I execute the query string using the exec command
> the udf does not fire, however if I copy the querystring into QA and
> run it, the results are fine, any pointers here would be great.
> Thanks
> Simon
>|||Can you post a script to reproduce the problem? Please include DDL.
If you find yourself needing dynamic SQL for basic data manipulation tasks
then that's often an indicator of a poor underlying design. Have you tried
to solve the problem without the dynamic code?
David Portas
SQL Server MVP
--|||Why are you using dynamic SQL? Are the rules for the payroll
constantly changing from month to month, moment to moment? Why do
users know more at run time about how to do a payroll than you know at
design time?
This kind of programming is usually a sign of a lack of a proper data
model and the use of a UDF is usually a sign of proceudral coding in
SQL instead of relational code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Hi all,
I managed to sort it, Looks like the udf was not needed after all in
this instance, but as to why that particular one would not fire when
called through a generated sql string is a mystery to me, especially
when others worked fine.
Tibor - thanks for the tip but I was still testing within QA, but will
bare the NO COUNT in mind should I come across the problem when using
ADO.
David - Thanks for the offer however I did not fancy posting a
storedprocedure containing over 500 lines of code that is not fully
commented as I am still devleloping it. This is not really what I
would term basic data manipulation, due to the rules of our pay
periods, and other factors. I did try to solve the issue without using
generated/dynamic SQL but kept hitting a brick wall, thanks again for
the offer.
Celko - I am using dynamic/generated SQL as trying to build a flat out
view for this task was proving a real headache. Our payroll rules do
not change from month to month, moment to moment, however working
things out like how our pay periods are structured does change, whether
this be a 5 w pay month or a four w pay month, plus the fact that
the actual period dates differ, and we are not simply paid from the
first to the 31st of each month that would be too easy. The users do
not know more at run time than I know as design time, all they know is
the last day of the pay month. From this the rules are applied and the
script generated to produce the output.
Correct me if I am wrong but aren't udfs ideal for things like business
rules and such like, for example, I am using udf's to manage, our
sickness rules, holiday rules and payroll rules. I would not really
fancy having to re-write views and stored procedures because our
sickness rules have changed slightly due to us buying another company
and honouring their sickness rules, I would be much happier just
changing a udf.
I see this a lot on these groups, saying udf's are bad and
dynamic/generated SQL is a sign of defeat, something I do not agree
with.
Anyway thats my rant over with, thanks for the offer of assistance, but
all I was really looking for was, maybe someone who had come across
this strange issue before.|||Nothing in what you've said indicates to me that dynamic SQL is an
obvious choice. The calendar, payment dates and stuff can go in a
table, where it can be easily modified without code changes.
I wouldn't say dynamic code is universally bad but it isn't best
practice for a production application. All too often, dynamic SQL is
just used as a kludge around poor data design. That may or may not be
the case here but I've worked with a few payroll systems and I haven't
yet seen one that couldn't represent all the configuration information
as data elements rather than dynamic code. In fact AFAIK that's pretty
standard practice with packaged payroll systems (tax and regulatory
stuff is usually implemented as separate modules that can be patched
for localization support), which in principle have to cope with a much
more complex and varied set of circumstances across a variety of
different business.
David Portas
SQL Server MVP
--|||David,
Fair comments I feel,
For some reason I had not actually thought of putting the pay dates and
periods into a table, not sure why as this is now obvious, maybe I
should get my school bag and go home! Actually doing that will get rid
of most of the dynamic/generated stuff I have in this instance as most
of it is working that out, lucky I am not actually writing a payroll
system but creating an add on.
Thanks.
Simon|||>> Our payroll rules do not change from month to month, moment to moment, ho
wever working things out like how our pay periods are structured does change
, whether this be a 5 w pay month or a four w pay month, plus the fact
that the actual period dat
es differ, and we are not simply paid from the first to the 31st of each mon
th that would be too easy. <<
You are still thinking with procedures and not relations. Look up the
use of an auxiliary Calendar table in one of my books. Do not try to
use dynamic SQL to construct this data every time you do a payroll.|||David and Celko,
Thanks for the tips, the using a table for dates/Auxiliary table worked
a treat.
Thanks
Simon.