Determine if row is empty and not null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Determine if row is empty and not null

Author

Topic

sirmilt
Starting Member

49 Posts

Posted - 2013-04-25 : 11:32:21

I need to create a simple stored procedure to set a value of 0 if there are no records in a table. This code:
SELECT (*) FROM Table

In Visual Basic because the first row of a table with no data is filled with null values it throws an error completed. What I need to do is determine if there are records or not.

Milt

Milt

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 11:55:23

Here is one way to do it:
You can do
[CODE] SELECT COUNT(*) as cnt FROM Table [/CODE]
If the cnt is > 0 then you can run your
[CODE] SELECT (*) FROM Table [/CODE]

quote:
Originally posted by sirmilt

I need to create a simple stored procedure to set a value of 0 if there are no records in a table. This code:
SELECT (*) FROM Table

In Visual Basic because the first row of a table with no data is filled with null values it throws an error completed. What I need to do is determine if there are records or not.

Milt

Milt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-25 : 12:29:41

[code]
SELECT COUNT(*) AS cnt FROM table
UNION ALL
SELECT 0
WHERE NOT EXISTS (SELECT 1 FROM table)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sirmilt
Starting Member

49 Posts

Posted - 2013-04-25 : 14:58:29

Thank you both for the responses.

While I wasn't quite sure exactly what the entry "WHERE NOT EXISTS (SELECT 1 FROM table)" does, the procedure worked perfectly. I'll have to do a little research to figure out why.

Thanks again
Milt

Milt

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-25 : 15:49:27

I am curious too...
Visakh, your query returns two rows of zeros when the table is empty... why do you need two rows?

sirmilt
Starting Member

49 Posts

Posted - 2013-04-25 : 18:07:37

I don't need two rows, all I wanted was the actual nubber of records that have been entered.

Milt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 00:17:19

quote:
Originally posted by sirmilt

I don't need two rows, all I wanted was the actual nubber of records that have been entered.

Milt

in case of table being empty what should be your returned resultset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sirmilt
Starting Member

49 Posts

Posted - 2013-04-26 : 09:27:06

Thanks to all of you for the help.

If there are no records all I need is a 0. The while I don't understand the last line of the code, the code posted by visakh16 seems to work fine.

Milt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:30:22

quote:
Originally posted by sirmilt

Thanks to all of you for the help.

If there are no records all I need is a 0. The while I don't understand the last line of the code, the code posted by visakh16 seems to work fine.

Milt

If thats the case

even MuMu88's suggestion should give you result right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources