DML Statements in code vs. Stored Procedures

Hi,
We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)
We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way".
What i mean by using seperate stored procedures is:
- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)
- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)
- The body contains a DML statement that uses the parameters
- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)
For select statements they think our approach is best...
I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.
Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...
ThanksHere was the previous big discussion on stored procs vs. dynamic sql:
Rob Howard:
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
Then Frans Bouma:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
The Rob Howard rebuttal:
http://weblogs.asp.net/rhoward/archive/2003/11/18/38446.aspx
That should be a good start.

Divide by zero error

Hi all. I am succesfully catching divide by zero errors using IIF
statements, all of the other expressions work fine except for this one below.
The proc that feeds the report does return 0's if there is no data. As of
now both fields have a value of zero, as time goes by there will be data. I
have other expressions that are all 0 and do not have any issues. I have
tried isNull, isNothing and the IIF. There are NO errors when I switch from
Layout to Preview, but the field has a ' #Error ' in preview mode. I am
sure it is somthing simple and obvious, but I am not seeing it. Can anyone
point me in the right direction? Thanks.
=IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )Try:
=iif((Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!YtdTotalWomen.Value,"proc" )) is
nothing,0,Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!YtdTotalWomen.Value,"proc" ))
darwin wrote:
> Hi all. I am succesfully catching divide by zero errors using IIF
> statements, all of the other expressions work fine except for this
one below.
> The proc that feeds the report does return 0's if there is no data.
As of
> now both fields have a value of zero, as time goes by there will be
data. I
> have other expressions that are all 0 and do not have any issues. I
have
> tried isNull, isNothing and the IIF. There are NO errors when I
switch from
> Layout to Preview, but the field has a ' #Error ' in preview mode.
I am
> sure it is somthing simple and obvious, but I am not seeing it. Can
anyone
> point me in the right direction? Thanks.
> =IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
> Sum(Fields!lyYtdWomensClothing.Value,"proc") /
> Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )|||Are you sure lyYtdWomensClothing is not returning zero...? Try using this
Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 AND
Sum(Fields!lyYtdWomensClothing.Value,"proc" ) > 0
for your IIF expression.
"darwin" wrote:
> Hi all. I am succesfully catching divide by zero errors using IIF
> statements, all of the other expressions work fine except for this one below.
> The proc that feeds the report does return 0's if there is no data. As of
> now both fields have a value of zero, as time goes by there will be data. I
> have other expressions that are all 0 and do not have any issues. I have
> tried isNull, isNothing and the IIF. There are NO errors when I switch from
> Layout to Preview, but the field has a ' #Error ' in preview mode. I am
> sure it is somthing simple and obvious, but I am not seeing it. Can anyone
> point me in the right direction? Thanks.
> =IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
> Sum(Fields!lyYtdWomensClothing.Value,"proc") /
> Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )|||nope, get a new error ... don't understand that one, the dataset is defined
on all fields in the expression.
h:\visual studio projects\paqs\PAQS Sales and Production.rdl The value
expression for the textbox â'textbox170â' refers to the field â'YtdTotalWomenâ'.
Report item expressions can only refer to fields within the current data set
scope or, if inside an aggregate, the specified data set scope.
"Michaema@.gmail.com" wrote:
> Try:
> =iif((Sum(Fields!lyYtdWomensClothing.Value,"proc") /
> Sum(Fields!YtdTotalWomen.Value,"proc" )) is
> nothing,0,Sum(Fields!lyYtdWomensClothing.Value,"proc") /
> Sum(Fields!YtdTotalWomen.Value,"proc" ))
> darwin wrote:
> > Hi all. I am succesfully catching divide by zero errors using IIF
> > statements, all of the other expressions work fine except for this
> one below.
> > The proc that feeds the report does return 0's if there is no data.
> As of
> > now both fields have a value of zero, as time goes by there will be
> data. I
> > have other expressions that are all 0 and do not have any issues. I
> have
> > tried isNull, isNothing and the IIF. There are NO errors when I
> switch from
> > Layout to Preview, but the field has a ' #Error ' in preview mode.
> I am
> > sure it is somthing simple and obvious, but I am not seeing it. Can
> anyone
> > point me in the right direction? Thanks.
> > =IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
> > Sum(Fields!lyYtdWomensClothing.Value,"proc") /
> > Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )
>|||I was under the impressiong that if you divide by 0 that Iif(a=0, 0,
b/a) was not enough. It will still try to evaluate both the true and
false parts before it picks which one to use. This has worked for me
in the past,
Iif(a=0, 0, b/Iif(a=0,1,a))
b/1 will never be the result but this will always evaluate.
I just deployed a custom assembly so I didn't have to write it like
this everyplace i wanted to use division.
Abe|||I think including the "proc" was causing some kind of error when it is
the expression being evaluated in an iif, try this and if that doesnt
work im out of ideas, i did have this same problem earlier, and the
format of this snippet below solved it.
=iif((Sum(Fields!lyYtdWomensClothing.Value) /
Sum(Fields!YtdTotalWomen.Value)) =nothing,0,Sum(Fields!lyYtdWomensClothing.Value) /
Sum(Fields!YtdTotalWomen.Value))|||I ran into the same situation. To solve it I set the textbox to hidden
if the divisor was zero. The true and false parts were always
evaluated giving me the error otherwise.
Roger|||If I remove the "proc" data set I get errors, there is more than one data set
in the report.
h:\visual studio projects\paqs\PAQS Sales and Production.rdl The value
expression for the textbox â'textbox170â' uses an aggregate expression without
a scope. A scope is required for all aggregates used outside of a data
region unless the report contains exactly one data set.
if I use this expression, there are no errors.. except for the " #Error "
message displayed on the preview page.
=iif( Sum(Fields!lyYtdTotalWomen.Value,"proc")
=0,0 , Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!lyYtdTotalWomen.Value,"proc")
)
Using Abe's Iif(a=0, 0, b/Iif(a=0,1,a)) formula, everthing is working and
happy! Thanks Greatly!!
"Alien2_51" wrote:
> Are you sure lyYtdWomensClothing is not returning zero...? Try using this
> Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 AND
> Sum(Fields!lyYtdWomensClothing.Value,"proc" ) > 0
> for your IIF expression.
> "darwin" wrote:
> > Hi all. I am succesfully catching divide by zero errors using IIF
> > statements, all of the other expressions work fine except for this one below.
> > The proc that feeds the report does return 0's if there is no data. As of
> > now both fields have a value of zero, as time goes by there will be data. I
> > have other expressions that are all 0 and do not have any issues. I have
> > tried isNull, isNothing and the IIF. There are NO errors when I switch from
> > Layout to Preview, but the field has a ' #Error ' in preview mode. I am
> > sure it is somthing simple and obvious, but I am not seeing it. Can anyone
> > point me in the right direction? Thanks.
> > =IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
> > Sum(Fields!lyYtdWomensClothing.Value,"proc") /
> > Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )|||I'm having similar problems with IIF and 'divide by zero' and liked your
solution below, but it still tells me it's dividing by zero. I'm also
retrieving the data from a stored procedure and have just the one dataset.
I even went as far as 'cast'ing all the data fields into the same datatype,
but that didn't help. Has anyone gotten the following IIF statement to work?
Here's my IIF statement, maybe I missed something..........
=iif((Fields!WNMHRS.Value=0),0.00,(
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))
Thanks
"Abe" wrote:
> I was under the impressiong that if you divide by 0 that Iif(a=0, 0,
> b/a) was not enough. It will still try to evaluate both the true and
> false parts before it picks which one to use. This has worked for me
> in the past,
> Iif(a=0, 0, b/Iif(a=0,1,a))
> b/1 will never be the result but this will always evaluate.
> I just deployed a custom assembly so I didn't have to write it like
> this everyplace i wanted to use division.
> Abe
>|||> =iif((Fields!WNMHRS.Value=0),0.00,(
>
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))
>
Should be
=iif((Fields!WNMHRS.Value=0),0.00,
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0,1,Fields!WNMHRS.Value))
I just took up the parenthesis in the front of the False part of the
first iff and removed an extra one in the middle of the second iif. I
think that should be ok...
Abe
DJanson wrote:
> I'm having similar problems with IIF and 'divide by zero' and liked
your
> solution below, but it still tells me it's dividing by zero. I'm
also
> retrieving the data from a stored procedure and have just the one
dataset.
> I even went as far as 'cast'ing all the data fields into the same
datatype,
> but that didn't help. Has anyone gotten the following IIF statement
to work?
>
> Here's my IIF statement, maybe I missed something..........
> =iif((Fields!WNMHRS.Value=0),0.00,(
>
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))
> Thanks
> "Abe" wrote:
> > I was under the impressiong that if you divide by 0 that Iif(a=0,
0,
> > b/a) was not enough. It will still try to evaluate both the true
and
> > false parts before it picks which one to use. This has worked for
me
> > in the past,
> >
> > Iif(a=0, 0, b/Iif(a=0,1,a))
> > b/1 will never be the result but this will always evaluate.
> >
> > I just deployed a custom assembly so I didn't have to write it like
> > this everyplace i wanted to use division.
> >
> > Abe
> >
> >|||Hope I didn't look too stupid, I really tried LOTS of combinations using
parentheses and not using parentheses. I'm not used to seeing problems
putting parens around the conditional part of the statement, but guess that's
an issue here. Thanks.
"Abe" wrote:
> > =iif((Fields!WNMHRS.Value=0),0.00,(
> >
> Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))
> >
> Should be
> =iif((Fields!WNMHRS.Value=0),0.00,
> Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0,1,Fields!WNMHRS.Value))
> I just took up the parenthesis in the front of the False part of the
> first iff and removed an extra one in the middle of the second iif. I
> think that should be ok...
> Abe
> DJanson wrote:
> > I'm having similar problems with IIF and 'divide by zero' and liked
> your
> > solution below, but it still tells me it's dividing by zero. I'm
> also
> > retrieving the data from a stored procedure and have just the one
> dataset.
> > I even went as far as 'cast'ing all the data fields into the same
> datatype,
> > but that didn't help. Has anyone gotten the following IIF statement
> to work?
> >
> >
> > Here's my IIF statement, maybe I missed something..........
> >
> > =iif((Fields!WNMHRS.Value=0),0.00,(
> >
> Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))
> >
> > Thanks
> >
> > "Abe" wrote:
> >
> > > I was under the impressiong that if you divide by 0 that Iif(a=0,
> 0,
> > > b/a) was not enough. It will still try to evaluate both the true
> and
> > > false parts before it picks which one to use. This has worked for
> me
> > > in the past,
> > >
> > > Iif(a=0, 0, b/Iif(a=0,1,a))
> > > b/1 will never be the result but this will always evaluate.
> > >
> > > I just deployed a custom assembly so I didn't have to write it like
> > > this everyplace i wanted to use division.
> > >
> > > Abe
> > >
> > >
>|||Yeah, no problem...I do it all the time. Sometimes its easier when
someone else looks at it :)
Abe
DJanson wrote:
> Hope I didn't look too stupid, I really tried LOTS of combinations
using
> parentheses and not using parentheses. I'm not used to seeing
problems
> putting parens around the conditional part of the statement, but
guess that's
> an issue here. Thanks.
> "Abe" wrote:
> > > =iif((Fields!WNMHRS.Value=0),0.00,(
> > >
> >
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))
> > >
> >
> > Should be
> > =iif((Fields!WNMHRS.Value=0),0.00,
> >
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0,1,Fields!WNMHRS.Value))
> > I just took up the parenthesis in the front of the False part of
the
> > first iff and removed an extra one in the middle of the second iif.
I
> > think that should be ok...
> >
> > Abe
> > DJanson wrote:
> > > I'm having similar problems with IIF and 'divide by zero' and
liked
> > your
> > > solution below, but it still tells me it's dividing by zero. I'm
> > also
> > > retrieving the data from a stored procedure and have just the one
> > dataset.
> > > I even went as far as 'cast'ing all the data fields into the same
> > datatype,
> > > but that didn't help. Has anyone gotten the following IIF
statement
> > to work?
> > >
> > >
> > > Here's my IIF statement, maybe I missed something..........
> > >
> > > =iif((Fields!WNMHRS.Value=0),0.00,(
> > >
> >
Fields!WJCHRS.Value/iif(Fields!WNMHRS.Value=0),1,Fields!WNMHRS.Value))
> > >
> > > Thanks
> > >
> > > "Abe" wrote:
> > >
> > > > I was under the impressiong that if you divide by 0 that
Iif(a=0,
> > 0,
> > > > b/a) was not enough. It will still try to evaluate both the
true
> > and
> > > > false parts before it picks which one to use. This has worked
for
> > me
> > > > in the past,
> > > >
> > > > Iif(a=0, 0, b/Iif(a=0,1,a))
> > > > b/1 will never be the result but this will always evaluate.
> > > >
> > > > I just deployed a custom assembly so I didn't have to write it
like
> > > > this everyplace i wanted to use division.
> > > >
> > > > Abe
> > > >
> > > >
> >
> >|||I found a strange way to correct. Depending on your data accuracy
requirements, this may not work for you.
There is another post with instructions to hide the field if the divisor is
zero. This is the only way I have found if you need to show a zero on a
report. I am sure the math experts will not be happy with this one, but here
it is. Make sure to calculate your data & see how much inaccuracies it will
introduce. Use at your own risk!
Basically the both sides of the IIF statement are evaluated. Even though the
IIF should see the criteria & return "0", it doesn't.
I have found if you add a small number to the divisor, you don't see the
error.
=IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
Sum(Fields!lyYtdWomensClothing.Value,"proc") /(
Sum(Fields!YtdTotalWomen.Value,"proc" ) + .0000000001 , 0 )
By adding .0000000001 you will not see the error, and assuming you are
calculating to 2 decimals, your accuracy should not be affected.
I have ran numerous scenarios & have not seen an accuracy problem when
rounding to 2 decimal points. I realize this is not the ideal solution as
it introduces inaccuracy. Hopefully in future versions there is another way
to handle.
Regards,
MB
"darwin" <darwin@.discussions.microsoft.com> wrote in message
news:7755D6FB-A5BD-41E4-BBBA-213907758B72@.microsoft.com...
> Hi all. I am succesfully catching divide by zero errors using IIF
> statements, all of the other expressions work fine except for this one
> below.
> The proc that feeds the report does return 0's if there is no data. As of
> now both fields have a value of zero, as time goes by there will be data.
> I
> have other expressions that are all 0 and do not have any issues. I have
> tried isNull, isNothing and the IIF. There are NO errors when I switch
> from
> Layout to Preview, but the field has a ' #Error ' in preview mode. I am
> sure it is somthing simple and obvious, but I am not seeing it. Can
> anyone
> point me in the right direction? Thanks.
> =IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
> Sum(Fields!lyYtdWomensClothing.Value,"proc") /
> Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )|||I finally got tired of writing cryptic IIF() expressions in order to handle
divide by zero conditions and wrote the following report function that can be
added to any report. it simplifies the appearance of the code, and lets you
specify an alternate value such as zero or nothing when divide by zero
occurs. I hope this is helpful...
' Handle divide by zero gracefully
' simpler than trying to use IIF()
Public Function CalcRatio(ByVal Numerator As Object, ByVal Denominator As
object, ByVal DivZeroDefault As Object) As Object
If Denominator <> 0 Then
Return Numerator/Denominator
Else
Return DivZeroDefault
End If
End Function
Steps:
From the menu, choose â'Reportâ', â'Report Propertiesâ'.
Click on the â'Codeâ' tab and past the above code into the window.
Click on â'OKâ'
To use the function, you have to reference the â'codeâ' collection in an
expression:
=code.CalcRatio( Fields!PYGrossProfit.Value, Fields!PYSales.Value, Nothing)
Or if you want a zero instead of a blank:
=code.CalcRatio( Fields!PYGrossProfit.Value, Fields!PYSales.Value, 0)
Enjoy!
--
Clayton Groom
Covenant Technology Parnters, LLC|||I realize this thread is almost 2 years old, but I hope someone can help out.
I have inserted the code below into my RS 2005 report,and get an error on
line 0.
"There is an error on line 0 of custom code: [BC30203] Identifier expected"
can anyone help?
thanks in advance
"Clayton Groom" wrote:
> I finally got tired of writing cryptic IIF() expressions in order to handle
> divide by zero conditions and wrote the following report function that can be
> added to any report. it simplifies the appearance of the code, and lets you
> specify an alternate value such as zero or nothing when divide by zero
> occurs. I hope this is helpful...
> ' Handle divide by zero gracefully
> ' simpler than trying to use IIF()
> Public Function CalcRatio(ByVal Numerator As Object, ByVal Denominator As
> object, ByVal DivZeroDefault As Object) As Object
> If Denominator <> 0 Then
> Return Numerator/Denominator
> Else
> Return DivZeroDefault
> End If
> End Function
> Steps:
> From the menu, choose â'Reportâ', â'Report Propertiesâ'.
> Click on the â'Codeâ' tab and past the above code into the window.
> Click on â'OKâ'
> To use the function, you have to reference the â'codeâ' collection in an
> expression:
> =code.CalcRatio( Fields!PYGrossProfit.Value, Fields!PYSales.Value, Nothing)
>
> Or if you want a zero instead of a blank:
> =code.CalcRatio( Fields!PYGrossProfit.Value, Fields!PYSales.Value, 0)
>
> Enjoy!
> --
> Clayton Groom
> Covenant Technology Parnters, LLC
>|||I'm very surprised no-one saw the problem with this:
=IIF (Sum(Fields!lyYtdTotalWomen.Value,"proc" ) > 0 ,
Sum(Fields!lyYtdWomensClothing.Value,"proc") /
Sum(Fields!YtdTotalWomen.Value,"proc" ) , 0 )
The IIF is checking lyYtdTotalWomen.Value, while the value used for division
is YtdTotalWomen.Value. Note the missing ly prefix. That makes them 2
different fields.

Distributed transaction with clustering server

I also got the same issue in my company's system.
My alternative option is to remove a pair of
"begin/commit tran" statements.
I tried without "begin/commit trans" to fail joined-tables
update from two linked servers, and it can prevent
asynchronized transaction on that table update, and thus
preserve transactional consistency. Maybe
that "begin/commit tran" has been implicitly enclosed!
Does anyone know why?
Regards,
Alfred XYZ

>--Original Message--
>I can't perform a distributed transaction between a
remote
>server to the central server.
>I have 3 sql server 2000 enterprise edition. 2 of them
are
>installed on win2K server (LOCAL1, LOCAL2) and the other
>(CENTRAL_SQL) is installed on clustering win2K server.
>None of them is in domain.
>Linked servers are defined well between them and I can
>perform select statement from any server to any other
>server.
>I can also perform a distributed transaction between the
>remote servers, from LOCAL1 to LOCAL2 and contrarily:
>set xact_abort on
>begin distributed tran
>select count(*) from
>LOCAL1.tempdb.INFORMATION_SCHEMA.TABLES
>commit
>set xact_abort off
>MSDTC service starts up with local system account or
other
>administrators belonging account.
>I can also ping the local servers from the central and
>even manage them using the Active Directory.
>When I perform a distributed transaction between a remote
>server (LOCAL1 or LOCAL2) and the central server I get an
>error:
>Server: Msg 7391, Level 16, State 1, Line 5
>The operation could not be performed because the OLE DB
>provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>[OLE/DB provider returned message: New transaction cannot
>enlist in the specified transaction coordinator. ]
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
>ITransactionJoin::JoinTransaction returned 0x8004d00a].
>What am I doing wrong?
>Thanks,
>Yuval Peleg
>DBA, Nice Systems
>.
>
We solved the problem by organizing the DNS addresses at all servers:
The clustering server is our Domain Controller, so it has to contain the
addresses of all participant servers: itself physical nodes, its virtual
address and the remote SQL server addresses.
At the local servers we signed, in TCP/IP properties, the first node of the
clustering server as the "preferred DNS server" and the second node the
"alternate DNS server".
"Alfred XYZ" <alfredxyz@.hotmail.com> wrote in message
news:0ee901c4d5f6$fc1fa560$a501280a@.phx.gbl...[vbcol=seagreen]
>I also got the same issue in my company's system.
> My alternative option is to remove a pair of
> "begin/commit tran" statements.
> I tried without "begin/commit trans" to fail joined-tables
> update from two linked servers, and it can prevent
> asynchronized transaction on that table update, and thus
> preserve transactional consistency. Maybe
> that "begin/commit tran" has been implicitly enclosed!
> Does anyone know why?
> Regards,
> Alfred XYZ
> remote
> are
> other