Select dummy values if where clause returns zero rows
I am using SQL Server 2016. I have a SQL and it has where clause which returns zero or non-zero rows depending on data present in the table at a specific time. For example the SQL is:
select step_id, step_name from msdb.dbo.sysjobhistory where step_id = 9999
What I want is when the SQL returns zero rows, then it should return one row with dummy values that I can specify.
What I tried: I can do this by using this SQL:
DECLARE @Condition int SET @Condition = 0; IF ( SELECT count(*) FROM ( select step_id, step_name from msdb.dbo.sysjobhistory where step_id = 9999 ) t1 ) > 0 BEGIN SET @Condition = 1; END; IF @Condition = 1 BEGIN select step_id, step_name from msdb.dbo.sysjobhistory where step_id = 9999 END ELSE BEGIN SELECT 123456 AS [step_id], 'There are no step_id matching the WHERE clause' AS [step_name] END
Question: But here I am running the same SQL twice. I want to know if there is any better way to do this.
Return a value if no record is found, SELECT (SELECT id FROM tbl WHERE id = 9823474) AS id;. In Oracle you have to select from the dummy 1-row table DUAL like this: SELECT� The problem is, that if the WHERE clause eliminates all rows, you end up with an empty set. One way around it, is to use an aggregate function to force one row in the result. Then, with COALESCE (or ISNULL), you can assign your default values. This solution is only feasible if: your basic query always returns exactly 0 or 1 row
Here is a simpler way to accomplish the same thing. No need for variables and such.
select step_id, step_name from msdb.dbo.sysjobhistory where step_id = 9999 if @@ROWCOUNT = 0 SELECT 123456 AS [step_id], 'There are no step_id matching the WHERE clause' AS [step_name]
Getting SELECT to return a constant value even if zero rows match , SELECT col1, col2, col3, 1 AS query_id FROM players WHERE username=' foobar' union all select null, null, null, 1 where not exists (select 1 from players� A RETURN statement with a return value cannot be used in this context. ListPrice FROM Production.Product WHERE Color is not null UNION SELECT 0, '0', 0.0 WHERE
Here's another option, just for comparison (it returns your dummy row in the same table as your main results, if you don't have main results).
DECLARE @search_ID INT = 999; WITH data AS (SELECT step_id, step_name FROM msdb.dbo.sysjobhistory UNION ALL SELECT 12345, 'dummy') --Insert your dummy data here SELECT * FROM data WHERE step_id = @search_ID OR ( step_id = 12345 AND NOT EXISTS (SELECT * FROM data WHERE step_id = @search_ID) );
SQL SELECT without FROM should read VALUES without INSERT, Every SELECT needs a FROM clause according to the SQL standard. the standard allows the use of values without insert : The following values statement returns today's and yesterday's dates (use-case) in two rows—not two The only other standard-conforming alternative is to use a dummy table in the from clause. Therefore, if your subquery returns a NULL value, the EXISTS statement resolves to TRUE. In the following example all the SalesOrderHeader rows are returned as the WHERE clause essentially resolved to TRUE: SELECT SalesOrderID, RevisionNumber, OrderDate FROM Sales.SalesOrderHeader WHERE EXISTS (SELECT NULL)
return empty row if no record found, of concern, but more importantly it is generic, e.g. what if the condition changes say Suggest to check for return row from sql query, if zero, create a datatable price, then add a row with the data that you decide, i.e. 0, 'no record', 0. 10 END ELSE SELECT 0 as id, 'No record' as Description, 0 as price Hi All. I have a report that needs to return a count of zero for the rows that have no data, I have tried to use the Left Outer Join but my where clause is excluding the rows with no data and I
Understanding the SQL Server CASE statement, company VARCHAR(50) NOT NULL,. power INT NOT Output Of Select Query To Test Dummy Data. You can see that the condition column contains an X in each row at the moment. We will set the If the CONDITION returns true the value that follows the THEN clause is stored in columnX. Else, the value� SQL-92 introduced row value expressions, which operate on a list of values or columns rather than on a single value or column. A row value expression is a list of value expressions that you enclose in parentheses and separate by commas. You can code these expressions to operate on an entire row at once or on a selected subset of the row. To use
Documentation: 8.4: SELECT, If the ORDER BY clause is specified, the returned rows are sorted in the row is extended to the full width of the joined table by inserting null values for the do this except by introducing a dummy one-row table from which to do the SELECT. SELECT COUNT(EmployeeID) AS HeadCount FROM Employees; You can use the other clauses in a SELECT statement to further restrict and organize your returned data. For more information, see the Help topic for the clause you are using. Links provided by the UtterAccess community. UtterAccess is the premier Microsoft Access wiki and help forum.
- Really depends on what you're going to do: One idea would be like IF @@ROWCOUNT = 0 return Dummyrow
- Thank you for your suggestion Exteban. I want just one result set with resulting row (if where clause returns some row/s) or with dummy values (if where clause returns zero rows).
- Worked like magic. I'll study it to understand better. First two & fourth conditions were true. The second column in the basic SQL is nvarchar/text but it still worked. Thank you for your valuable response.
- Thank you for your suggestion Sean. This works the way I want but it pull two result sets. First with zero rows and second with dummy values. Is there a way to get single result set with dummy rows when the SQL returns zero rows?
- Thank you for your response APH but I am getting syntax error near
,. Error is:
Incorrect syntax near ','. Expecting '(', or AS.
- @300 sorry about that, I was copy-pasting to make my example work with your field names, and pasted the wrong thing. Try it now :)