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 TableIn 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.MiltMilt |
|
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 TableIn 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.MiltMilt
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-25 : 12:29:41
|
[code]SELECT COUNT(*) AS cnt FROM tableUNION ALLSELECT 0WHERE NOT EXISTS (SELECT 1 FROM table)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 againMiltMilt |
|
|
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 MVPhttp://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 caseeven MuMu88's suggestion should give you result right?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|