There were no winners for the February Reader Challenge, "Check for an Empty Table." Here's a recap of the problem and the solution to the February Reader Challenge.

Problem:


Steve is a DBA who manages several SQL Server 2000 installations in his company. He needs to create a batch job that exports and imports several tables from the database servers. As part of the batch job, Steve would like to invoke a stored procedure that can determine whether a table is empty. According to the stored procedure result, he will then perform appropriate actions like executing DBCC SHOWCONTIG or DBCC DBREINDEX statements in his batch job.

Help Steve design a stored procedure that does the following: 1. Determines whether a table in a particular database is empty. (The tables can be present in different user schemas.) 2. Decides the appropriate parameters for the stored procedure and table name as a mandatory parameter. 3. Returns the status of the table (empty or not empty) as an output parameter.

Solution:


Steve can use a query like the one below to determine whether a table is empty.

-- Create sample table first:<br>
USE tempdb<br>
GO<br>
CREATE TABLE test_tbl( i INT NOT NULL ) <br>
GO<br><br>
-- Code that determines whether test_tbl is empty: <br>
DECLARE @IsEmpty BIT<br>
SET @IsEmpty = 1<br>
IF EXISTS(SELECT * FROM test_tbl) <br>
 SET @IsEmpty = 0<br>
SELECT @IsEmpty AS IsEmpty<br>
GO<br>

Steve can now incorporate the batch into a stored procedure and parameterize the table name by creating the SQL statements dynamically and executing them. The sample code below shows the stored procedure IsTableEmpty, which takes a table name as a parameter and returns 1 if the table is empty or 0 if it’s not. The input parameter @TableName needs to be protected against SQL injection attacks due to use of dynamic SQL. Since the parameter represents a table name or object identifier, the QUOTENAME system function can be used on the @TableName to form a properly quoted identifier. This ensures that if the @TableName parameter value contains a string that can be used to inject invalid SQL it will do no harm because the string will be treated as table name only.

USE tempdb<br>
GO<br>
CREATE PROCEDURE IsTableEmpty<br>
(@TableName nvarchar(128), @IsEmpty BIT OUTPUT) <br>
AS<br>
BEGIN<br>
  DECLARE @SqlStr nvarchar(4000) <br>
  SET @SqlStr = N'<br>
IF EXISTS(SELECT * FROM ' + quotename(@TableName) + N') <br>
 SET @empty = 0<br>
'<br>
  EXEC sp_executesql @SqlStr, N'@empty BIT OUTPUT', @empty = @IsEmpty OUTPUT<br>
  SET @IsEmpty = COALESCE(@IsEmpty, 1) <br>
END<br>
GO<br>

DECLARE @EMPTY BIT<br>
EXEC IsTableEmpty 'test_tbl', @empty OUTPUT<br>
SELECT @EMPTY<br><br>
-- Add one row into test_tbl: <br>
INSERT INTO test_tbl (i) VALUES(1) <br><br>
DECLARE @empty BIT<br>
EXEC IsTableEmpty 'test_tbl', @empty OUTPUT<br>
SELECT @empty<br>
GO

Steve can also modify the stored procedure to handle tables in other user schemas and any database. To pass the database name and schema name as optional parameters, Steve uses the following modified stored procedure:


USE tempdb<br>
GO<br>
ALTER PROCEDURE IsTableEmpty<br>
(@Database nvarchar(128) = NULL, @SchemaName nvarchar(128) = NULL, @TableName <br>nvarchar(128), @IsEmpty BIT OUTPUT) <br>
AS<br>
BEGIN<br>
  DECLARE @SqlStr nvarchar(4000) <br>
  DECLARE @FullTableName nvarchar(500) <br>
  SET @FullTableName = COALESCE(quotename(@Database) + N'.', N') <br>
                     + COALESCE(quotename(@SchemaName) + N'.', N'.') <br>
                     + quotename(@TableName) <br>
  SET @SqlStr = N'<br>
IF EXISTS(SELECT * FROM ' + @FullTableName + N') <br>
 SET @empty = 0<br>
'<br>
  EXEC sp_executesql @SqlStr, N'@empty BIT OUTPUT', @empty = @IsEmpty OUTPUT<br>
  SET @IsEmpty = COALESCE(@IsEmpty, 1) <br>
END<br>
GO<br><br>
-- Test the stored procedure: <br>
DECLARE @empty BIT<br>
--EXEC IsTableEmpty @TableName = 'test_tbl', @IsEmpty = @empty OUTPUT<br>
SELECT @empty<br><br>
DELETE test_tbl<br><br>
-- EXEC IsTableEmpty 'test_tbl', @empty OUTPUT<br>
Exec IsTableEmpty @TableName = 'test_tbl', @IsEmpty = @empty<br>
GO<br>
-- Drop the test_tbl: <br>
DROP TABLE test_tbl<br>
GO<br>
/* The stored procedure call below shows how to specify a database and <br>schema-qualified table name. This call below will run without <br>errors only on a SQL Server 2005 server with the AdventureWorks sample database. */<br>
DECLARE @empty BIT<br>
EXEC IsTableEmpty 'AdventureWorks', 'Person', 'Address', @empty OUTPUT<br>
SELECT @empty<br>
GO<br>