How to determinate a filed is empty

I want to update a field if a field is blank.
I use the sql:
Update myTable set myField="abc" where (myField='')
But if the field is null, it will not be updated.
How can I do?Use IS NULL:
UPDATE myTable
SET myField = 'abc'
WHERE myField IS NULL
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uUf4HVTfGHA.1324@.TK2MSFTNGP04.phx.gbl...
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
>|||"ad" <flying@.wfes.tcc.edu.tw> wrote

>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
UPDATE myTable SET myField="abc" where myField='' OR myField is NULL
Bye, Anatoli

How to determinate a filed is empty

I want to update a field if a field is blank.
I use the sql:
Update myTable set myField="abc" where (myField='')
But if the field is null, it will not be updated.
How can I do?Use IS NULL:
UPDATE myTable
SET myField = 'abc'
WHERE myField IS NULL
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:uUf4HVTfGHA.1324@.TK2MSFTNGP04.phx.gbl...
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
>|||"ad" <flying@.wfes.tcc.edu.tw> wrote
>I want to update a field if a field is blank.
> I use the sql:
> Update myTable set myField="abc" where (myField='')
> But if the field is null, it will not be updated.
> How can I do?
UPDATE myTable SET myField="abc" where myField='' OR myField is NULL
Bye, Anatolisql

How to detect NULL in SQL-table with VB.net ?

Hi,
I want to check with VB.net whether a field in a SQL-table is NULL or not.
This code doesnot work:
If xxx = NULL then
<statements>
End If
I got the error, that NULL is not supported ?
How do I code the check ?
Help is appreciated, Gr.

Hi,

it would beDBNull.Value oryou can also useIsDBNull function (returns boolean based on if the given object has DBNull value)

|||Joteke, thanks a lot for you help,
regards from the North Sea, Ger.

How to delete a big amount of data

Hi, suppose i have 3 tables

main table

CREATE TABLE table1 (
[t1ID] [int] primary key ,
[t1Name] nvatchar(50) NOT NULL,
.....................

)

and connections tables (t1ID is a foreign key in all connection tables)

CREATE TABLE tbl2 (
[t1ID] [int] not null,
[gID] [int] not null
PRIMARY KEY (t1ID, gID)
)

CREATE TABLE tbl3 (
[t1ID] [int] not null,
[pID] [int] not null
PRIMARY KEY (t1ID, pID)
)

************************************************** *****

t1ID CLISTERED INDEX
in othre tables each primary key is clustered.

gID and pID are keys from other table: gtable, ptable

Now in case one row was deletes from table1 i need to delete all t1ID's
from the connection tables (each of connection may have e.g. 200000 rows).
If u use delete on cascade it's take a lot of time, i want to delete let's
say 1000 rows till all irrelevant rows will be deleted.

How To accomplish this??
Thanks.

--
Message posted via http://www.sqlmonster.comakej via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> Now in case one row was deletes from table1 i need to delete all t1ID's
> from the connection tables (each of connection may have e.g. 200000 rows).
> If u use delete on cascade it's take a lot of time, i want to delete let's
> say 1000 rows till all irrelevant rows will be deleted.

Well, if you have FK constraints from the connections tables to the
main table, you cannot delete the connection from that table until all
references from the connection tables are gone. So while you could batch
things like:

SET ROWCOUNT 10000
WHILE EXISTS (SELECT * FROM tbl2 WHERE tl1D = @.id_to_delete)
DELETE tbl2 WHERE tlID = @.id_to_delete
SET ROWCOUNT 0

I can't really see that it will help you.

You could of course drop the foreign-key constraint, and the start some
background process that deletes the rows in the other table, but that's
a pretty wild thing to do.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, your suggestion is helpful however how can implement it,
i guess by job that must define, so when this job will be start and who
will start it??

Also i can remove all these relationship, only the ID's will be stay, now
each time when t1ID will be deleted i will put this t1ID in some pemanent
table suppose [toDelete] and from now i need to start job or jobs that
let's say every 10 minut will remove 1000 rows from each connection table
or from first connection table after this from second and so on, in the end
i need to remove this t1ID. However i'm new in sql and i don't really know
how to implement it. Maybe u reject this and have more efficient suggestion.

Thanks

--
Message posted via http://www.sqlmonster.com|||Any ideas ???

--
Message posted via http://www.sqlmonster.com|||akej via SQLMonster.com (forum@.SQLMonster.com) writes:
> Thanks Erland, your suggestion is helpful however how can implement it,
> i guess by job that must define, so when this job will be start and who
> will start it??
> Also i can remove all these relationship, only the ID's will be stay,
> now each time when t1ID will be deleted i will put this t1ID in some
> pemanent table suppose [toDelete] and from now i need to start job or
> jobs that let's say every 10 minut will remove 1000 rows from each
> connection table or from first connection table after this from second
> and so on, in the end i need to remove this t1ID. However i'm new in sql
> and i don't really know how to implement it. Maybe u reject this and
> have more efficient suggestion.

Well, since I don't like dropping foreign-key constraints, I would first
look into the situation a little closer. It seems that you have a
"connection" and you have 200000 rows added for that connection that
you want to drop. Maybe there is reason to consider why all those rows
were added in the first place?

This may be a stupid question, but pleaes keep in mind that since I know
nothing about your business, I have to try some stabs in the dark.

But if you truly want an asynchronous delete, I would set up a in SQL
Agent that runs with some frequency and which deletes orphaned rows.
There could be a trigger on the table, so that when a row is deleted,
the job is started, but frankly, I would actually skip that step. This
job could be devised so that it deletes all orphans it can find, possibly
batched with SET ROWCOUNT. Or it could be written so that it stops after
some time, and take remaining oprhans on the next time, depending on
how you want to spread the load.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

how to define a parameter in reporting services to choose to show/hide not null/null values?

Hi All,

I am using SQL Server Reporting Services 2005 and in my report I want to create a parameter called "hasEmail" with 3 possible values (Yes, No, Both) to show/ hide the customers who have/don't have emails or both.

Can anyone please help?

Try Add Parameter-Choose Available Values-Non queried- there you can put in the label and values that you want to return (Yes,No,Both) Then you will have to use these parameters to filter your dataset. Hope that helps.|||

You said : "Then you will have to use these parameters to filter your dataset."

The problem is the parameter doesn't exactly match the field. For example, it's not like the case that: OK the parameter chosen by user is "Bicycles" so only show me the data (WHERE the param is Bicycle). The problem I have is that some of the customers have provided their emails in the database and some haven't. I want to be able to show (or not show) the customers that have (or have not) email address. Maybe I should use "EXISTS" or something, because like I said it's not a matter of exactly matching the string (bicycle for example) with the field; It's a matter of true/false if the email exists or not.

I don't know.

Any thoughts?

|||

Yes, right click table or list- if thats what you are using - properties- filters- I don't know about your particular case but here is one that I used a parameter to filter in- same concept, but how to apply to your case I'm not sure- you'll have to play with it.

(Expression)=Fields!CONT_FREQ_CODE.Value

(Operand) =

(Value) =iif(Format(Parameters!Report_Parameter_0.value,"MM")=3 or Format(Parameters!Report_Parameter_0.value,"MM")=6 or Format(Parameters!Report_Parameter_0.value,"MM")=9 or Format(Parameters!Report_Parameter_0.value,"MM")=12,Fields!CONT_FREQ_CODE.Value,"M")

|||

Thanks Kimberly,

I found my answer at

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494455&SiteID=1

Thank you anyway.

how to define a parameter in reporting services to choose to show/hide not null/null values?

Hi All,

I am using SQL Server Reporting Services 2005 and in my report I want to create a parameter called "hasEmail" with 3 possible values (Yes, No, Both) to show/hide the customers who have/don't have emails or both.

Can anyone please help?

Try using a filter with the following entries. This assumes that there is an "Email" field of type string in the data set you are using, and the parameter name with the three values is ShowOnlyWithEmail. We use Len instead of IIF and a check for Nothing, because =IIF(IsNothing(Fields!Email.Value), 0, Fields!Email.Value.Length) will throw an exception if the field value is Nothing (IIF is a function and all arguments are evaluated before the function call), and Len will return 0 if the field value is Nothing.

ExpressionOperatorValue=Len(Fields!Email.Value)
>=IIF(Parameters!ShowOnlyWithEmail.Value="Yes", 0, -1)=Len(Fields!Email.Value)

<=IIF(Parameters!ShowOnlyWithEmail.Value="No", 1, Int32.MaxValue)

If the parameter is set to "Yes", then the first filter entry will

remove any rows that do not have an email address and the second will

not filter any rows.

If the parameter is set to "No", the the first filter entry will not

filter any rows, and the second will filter all rows with a length less

than 1.

If the parameter is set to "Both", then both filter entries will not filter any rows.

Ian|||It works fine. Thank you. What does LEN stand for? (Like REM is remove).|||Len is a function from VB to determine the length of an string (or size of any object) that does not blow up if a null value is passed in.

how to define a parameter in reporting services to choose to show/hide not null/null values?

Hi All,

I am using SQL Server Reporting Services 2005 and in my report I want to create a parameter called "hasEmail" with 3 possible values (Yes, No, Both) to show/hide the customers who have/don't have emails or both.

Can anyone please help?

Try using a filter with the following entries. This assumes that there is an "Email" field of type string in the data set you are using, and the parameter name with the three values is ShowOnlyWithEmail. We use Len instead of IIF and a check for Nothing, because =IIF(IsNothing(Fields!Email.Value), 0, Fields!Email.Value.Length) will throw an exception if the field value is Nothing (IIF is a function and all arguments are evaluated before the function call), and Len will return 0 if the field value is Nothing.

ExpressionOperatorValue=Len(Fields!Email.Value)
>=IIF(Parameters!ShowOnlyWithEmail.Value="Yes", 0, -1)=Len(Fields!Email.Value)
<=IIF(Parameters!ShowOnlyWithEmail.Value="No", 1, Int32.MaxValue)

If the parameter is set to "Yes", then the first filter entry will remove any rows that do not have an email address and the second will not filter any rows.
If the parameter is set to "No", the the first filter entry will not filter any rows, and the second will filter all rows with a length less than 1.
If the parameter is set to "Both", then both filter entries will not filter any rows.

Ian|||It works fine. Thank you. What does LEN stand for? (Like REM is remove).|||Len is a function from VB to determine the length of an string (or size of any object) that does not blow up if a null value is passed in.