CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
Jesse Hallam
Jesse Hallam

Reputation: 6964

How do I return an empty result set from a procedure using T-SQL?

I'm interested in returning an empty result set from SQL Server stored procedures in certain events.

The intended behaviour is that a L2SQL DataContext.SPName().SingleOrDefault() will result in CLR null value.

I'm presently using the following solution, but I'm unsure whether it would be considered bad practice, a performance hazard (I could not find one by reading the execution plan), or if there is simply a better way:

SELECT * FROM [dbo].[TableName]
WHERE 0 = 1;

The execution plan is a constant scan with a trivial cost associated with it.

The reason I am asking this instead of simply not running any SELECTs is because I'm concerned previous SELECT @scalar or SELECT INTO statements could cause unintended result sets to be served back to L2SQL. Am I worrying over nothing?

Upvotes: 38

Views: 38857

Answers (5)

Alexander
Alexander

Reputation: 681

If you need column names in the response then proceed with the select TOP 0 * from that table, otherwise just use SELECT TOP 0 NULL. It should work pretty fast :)

Upvotes: 68

vikjon0
vikjon0

Reputation: 192

I think the best solution is top 0 but not using a dummy table. This does it for me

select top 0 null as column1, null as column2.

Using e.g. a system table may be fine for performance but looks unclean.

Upvotes: 5

Remus Rusanu
Remus Rusanu

Reputation: 294407

If you want to simply retrieve the metadata of a result set w/o any actual row, use SET FMTONLY ON.

Upvotes: 16

DVK
DVK

Reputation: 129491

It's an entirely reasonable approach.

To alleviate any worries about performance (whoch you shouldn't have any in the first place - the server's smart enough to avoid table scanning for 1=0), pick a table that's very small and not heavily used - I'm sure your DB schema has one.

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838806

That is a reasonable approach. Another alternative is:

SELECT TOP 0 * FROM [dbo].[TableName]

Upvotes: 32

Related Questions