Dapper return single value

Question

I want to perform this simple test to see whether a table exists. I thought it would be easy just to return an int value depending on whether the table exists.

Below is what I have tried and doesn't work:

result = connection.ExecuteScalar<int>(@"
IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = '@tableSchema' 
                 AND  TABLE_NAME = '@tableName'))
    RETURN 0;


RETURN 1;
", new { tableSchema, tableName });

Error Message:

A RETURN statement with a return value cannot be used in this context.

It's something really noddy...

Answer 1

ExecuteScalar

executes the query, and returns the first column of the first row in the result set returned by the query.

It does not return the return value of your SQL statements. You could simply use

SELECT 1
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_SCHEMA = @tableSchema
AND    TABLE_NAME   = @tableName

Then you can use ExecuteScalar and it will return 1 if the table exists. Please note that I also corrected your parameters in the SQL statements. They must not be delimited by '.