I have a variable @tableName (NVARCHAR(255))
that is name of a table. I’m trying to perform an action if data exists in the the table.
There are two ways to check this.
Method #1 (bulky)
DECLARE @rowsInTable BIGINT DECLARE @cmd NVARCHAR(MAX) SET @cmd = 'SELECT @rowsInTable = COUNT_BIG(*) from '+ @tableName EXEC sp_executesql @cmd, N'@rowsInTable INT OUTPUT', @rowsInTable OUTPUT IF (@rowsInTable > 0) PRINT 'Do something'
Method #2 (clean)
IF( EXISTS (SELECT * FROM sys.tables WHERE name = @tableName)) PRINT 'Do something'
- Are methods #1 and #2 doing the exact same thing?
- Is it safe to use method #2? Can there be case when data exists is
@tableName
but method #2 would return false? - Is there a way I can make method #1 shorter? Or is there any third clean way to achieve this?
Advertisement
Answer
Method #1 can be optimized. Used of COUNT_BIG
can be avoided. Have a look into below query.
DECLARE @dataExists int DECLARE @cmd NVARCHAR(MAX) SET @cmd = 'SELECT @dataExists = case when Exists(Select 1 from '+ @tableName+') then 1 else 0 end' EXEC sp_executesql @cmd, N'@dataExists INT OUTPUT', @dataExists OUTPUT IF (@dataExists > 0) PRINT 'Do something'