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...
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 '
.
Instead of using IF..ELSE statement you can use the following query and check whether the result is greater than zero.
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '@tableSchema' AND
TABLE_NAME = '@tableName'
BTW your query has syntax error, that's why you are seeing that error.