Return 0 rather than Null

i have query which does the following select x from y where t = "House"

x is an integer. If no record is found, how do i get it to return 0 rather than null?

Use a stored procedure and return the affected rows. This will result in 0 if the Select statement you exampled gets no results.|||This is a query within a stored procedure so is there any other way?|||Why can't you add it to the stored procedure?|||Because i would prefer to do it an alternative way not using a stored procedure.|||The only other way is to assign a value of 0 to an int if the calling app detects that no rows were returned to it by the select statement.|||

Are you using this to populate a datatable or a dataset? This would be the most logical way to handle both cases (returning rows OR looking for a record count).

In your code, do:

' assuming table is a System.Data.DataTable that has been' populated with the results of a SQL query.If table.Rows.Count > 0Then' the table has rows.Else' the table has no rows.End If

|||Thanks for your help. It is returned to a variable within a stored procedure. Could I use an IF statement or anything within the SP?|||

Sorry, I didn't see that your select statement was inside an sp.

You can use an if statement if you want. Just like c++ or c#, if it's only one line after the if, you don't need anything else. If there are multiple lines, use BEGIN and END tags.

Also, I'm not sure how to get the number of rows within the last select statement, but you could perform a count before doing the select.

DECLARE @.Countint-- count the number of rows to be selectedSELECT @.Count =Count(*)from MyTableWhere FName ='Jane'IF @.Count > 0BEGIN-- Perform the SQL statement to get rows.SELECT *from MyTableWhere FName ='Jane'ENDELSEBEGIN-- return 0, no rows were returned.Return 0-- or Return @.CountEND

|||

this would work faster:

IF EXISTS(SELECT *from MyTableWhere FName ='Jane')

BEGIN
-- Perform the SQL statement to get rows.
SELECT *from MyTableWhere FName ='Jane'
END
ELSE
BEGIN
-- return 0 in first cell in one row
SELECT count(*)from MyTableWhere FName ='Jane' -- or just SELECT 0
-- and return if you need
RETURN 0

END