Help for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

Help - How to validate parameters?

I need to validate a parameters Length to make sure it is 8 characters long
and whether it is of numeric value and if it is not to display a validation
error message. Is this possible. I'm using reporting service and SQL Server
2000.
Any help will greatly appreciated.Have you tried placing custom code to validate these, then placing a dummy
textbox at the top of the report to call the validate code?
Eg. =Code.ValidateParameters()
Get this to show a box or something if it fails.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:D2458D65-E2C3-4C80-951E-D44B389929AA@.microsoft.com...
>I need to validate a parameters Length to make sure it is 8 characters long
> and whether it is of numeric value and if it is not to display a
> validation
> error message. Is this possible. I'm using reporting service and SQL
> Server
> 2000.
> Any help will greatly appreciated.|||Hi, Craig,
my code looks like this:
declare @.lastmonth char(7)
@.lastmonth = code.mycode() <-- will return '2006.02'
select comments from theTable
where yearmonth = @.lastmonth
I execute it and get an error "syntax error near @.lastmonth"
the program of mycode() is in layout, report, report properties, code.
Thanks!
Henry
"Ken" wrote:
> I need to validate a parameters Length to make sure it is 8 characters long
> and whether it is of numeric value and if it is not to display a validation
> error message. Is this possible. I'm using reporting service and SQL Server
> 2000.
> Any help will greatly appreciated.|||Well for starters you can't put multiple lines in the dataset. If you were
doing this you can only do:
="select comments from theTable where yearmonth = "& Code.mycode()
Which will return the SQL statement with the date included.
I thought the original question though was how to validate the parameters?
I was thinking of putting a Testbox on the form which has a value of
=Code.ValidateParameter(Parameters!DateParam.Value)
as an example. Then you can make this visible if the text box has anything
in it.
You can use the same syntax for the SQL statement if you wanted to check the
parameter before you passed it in to the database within the dataset.
Craig
"Henry Chen" <HenryChen@.discussions.microsoft.com> wrote in message
news:7F512BB0-8D5C-4E16-B1E2-6A0270BB0117@.microsoft.com...
> Hi, Craig,
> my code looks like this:
> declare @.lastmonth char(7)
> @.lastmonth = code.mycode() <-- will return '2006.02'
> select comments from theTable
> where yearmonth = @.lastmonth
> I execute it and get an error "syntax error near @.lastmonth"
> the program of mycode() is in layout, report, report properties, code.
> Thanks!
> Henry
>
> "Ken" wrote:
>> I need to validate a parameters Length to make sure it is 8 characters
>> long
>> and whether it is of numeric value and if it is not to display a
>> validation
>> error message. Is this possible. I'm using reporting service and SQL
>> Server
>> 2000.
>> Any help will greatly appreciated.