Return a value if no rows are found in Microsoft tSQL

by

This is similar to Adam Robinson’s, but uses ISNULL instead of COUNT. 

SELECT ISNULL(
(SELECT 1 FROM Sites S
WHERE S.Id = @SiteId and S.Status = 1 AND 
      (S.WebUserId = @WebUserId OR S.AllowUploads = 1)), 0)

If the inner query has a matching row, then 1 is returned. The outer query (with ISNULL) then returns this value of 1. If the inner query has no matching row, then it doesn’t return anything. The outer query treats this like a NULL, and so the ISNULL ends up returning 0.

More Related Contents:

  1. SQl CASE Statement
  2. Levenshtein distance in T-SQL
  3. How to get sp_executesql result into a variable?
  4. How can I use optional parameters in a T-SQL stored procedure?
  5. Select query to remove non-numeric characters
  6. T-SQL Decimal Division Accuracy
  7. Selecting a Record With MAX Value
  8. T-SQL Pivot? Possibility of creating table columns from row values
  9. What is a “batch”, and why is GO used?
  10. TSQL divide by zero encountered despite no columns containing 0
  11. T-SQL loop over query results
  12. How to insert default values in SQL table?
  13. How to Round a Time in T-SQL
  14. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  15. t-sql get all dates between 2 dates [duplicate]
  16. Check if a varchar is a number (TSQL)
  17. How to pivot table with T-SQL?
  18. Using Aliases in Where Clause or an Alternative Option?
  19. Extract the first word of a string in a SQL Server query
  20. Testing for inequality in T-SQL
  21. Can I use the MERGE statement in SQL Server 2005?
  22. Replace a newline in TSQL
  23. Search for one value in any column of any table inside a database
  24. How to use an Alias in a Calculation for Another Field
  25. Check if a varchar is a number (T-SQL)
  26. Using a cursor with dynamic SQL in a stored procedure
  27. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  28. Convert unix epoch timestamp to TSQL datetime
  29. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  30. Stored procedure hangs seemingly without explanation

Leave a Comment