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 for creating stored procedure

ALTER PROCEDURE companyinsert

@.companyid INT IDENTITY(1,1) NOT NULL,
@.companyname varchar(20),
@.address1 varchar(30)

AS

INSERT INTO companymaster
( companyname, address1)
VALUES (@.companyname,@.address1)

i don't want the companyname having the same names are recorded again with the different company id..

Can anyone help me and modify my code according it's giving error...in the @.companyid.

It is being done in sql server 2005 with asp.net C# 2005

You cannot declare a parameter with IDENTITY property in a stored proc. I think what you are looking for is more along these lines. You might want to read up documentation on SCOPE_IDENTITY(). Briefly, it returns the Identity value that was created due to your INSERT.

ALTER PROCEDURE companyinsert@.companyidINT OUTPUT,@.companynamevarchar(20),@.address1varchar(30)ASBEGINSET NOCOUNT ONIFNOT EXISTS(SELECT *FROM companymasterWHERE CompanyName = @.companynameAND Address1 = address1)BEGININSERT INTO companymaster ( companyname, address1)VALUES (@.companyname,@.address1)SELECT @.companyid = SCOPE_IDENTITY()ENDSET NOCOUNT OFFEND

Help Altering Database Table from NULL to NOT NULL

I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. ThanksYou alter columns to NOT NULL, not tables. Can you explain why you're trying to do this? What are you going to put in all the columns that have NULL? Why does the table have NULL in the first place. You should post what the table looks like with a couple sample rows.|||My apologies I meant a column. Currently a remote table that I'm adding a new column to where the people there have no idea how to use SQL Server. I've made a new SQL script to add a new column and have to set the column to NULL since its a new column being added to an existing table. After the apropriate data is in the new column I need to set the column to NOT NULL for future entries.....|||Could you add the column allowed to have NULL, then update the table where the column get's some value, then alter the table setting the column to NOT NULL?|||Thats pretty much what I'm trying to do but via scripts. Circumstances keep me from doing this manually so I'm trying to get it setup via a script.....|||so at which of the three steps are you stuck?|||Are you just trying to change the column null to not null if something is meet?|||if exists (select 1 from your_table where new_column is null)
raiserror ('You can't make column NON-NULLABLE if there is at least 1 NULL in it!, 15, 1)
else
alter table your_table alter column new_column <data_type> not null

HELP : empty string to Null conversion by SQL

Hi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHNHi folks,

I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.

THANKS JOHN

Dunno how you're going to do it without naming column names.

UPDATE MyTable
SET Col1 = Null, Col2 = Null, Col3 = Null
WHERE [insert where clause here]

Note that this will set Col1, Col2 and Col3 to Null; not just where the are equal to an empty string, but where there are values as well.

You may consider the CASE statement to selectively change blank values to NULL.

Regards,

hmscott|||As an alternate consideration, you might try:

SELECT
'UPDATE [' + TABLE_NAME + '] SET [' + COLUMN_NAME + '] = NULL
WHERE [' + COLUMN_NAME + '] = '''
FROM INFORMATION_SCHEMA.columns
WHERE DATA_TYPE IN ('char','nchar','varchar','nvarchar')

This will give you a bunch of individualized UPDATE statements. You would then have to run each statement seperately.

Regards,

hmscott|||Ok, but please use:
set [YourColumn] = NULLIF([YourColumn], '')
...or you will wipe out all your data.