CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008stored-procedures
jrara
jrara

Reputation: 17001

Stored procedure: how to use column as an input

I'm trying to create a simple stored procedure to count the number of empty records in my database:

CREATE PROCEDURE dbo.cnt_empty 
    @col NVARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

        SELECT COUNT(@col) AS cnt
        FROM dbo.mytable
        WHERE @col = ''

END
GO

EXECUTE dbo.cnt_empty @col = N'field1' -- nvarchar(10)

I returns 0 for all the columsn I tested. What is wrong with this procedure?

Upvotes: 1

Views: 508

Answers (5)

Nathanial Woolls
Nathanial Woolls

Reputation: 5291

By doing this, your SQL statement is treating the parameter like a string, not like the name of a column. Take a look at sp_executesql. That will help you build up a SQL string and execute it.

Upvotes: 2

Sergii Kudriavtsev
Sergii Kudriavtsev

Reputation: 10522

Take a look at this article: http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

Basically using EXEC statement or sp_executesql should be your choice.

Upvotes: 1

Joe Mancuso
Joe Mancuso

Reputation: 2129

Your string is not being assessed as the column name, so you are actually running "where 'field1' = ''"

You need to do something like this

set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
    '] where [' + @columnName + '] is not null';
-- print @sql; --uncomment for debugging
exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

Look at http://blog.hoegaerden.be/2009/02/15/script-find-all-empty-columns-in-database/ for the full script.

Upvotes: 4

Purplegoldfish
Purplegoldfish

Reputation: 5294

What you want to do is declare a variable like @sql VARCHAR(500)

Then do

SET @sql = 'SELECT COUNT('+@col+') AS cnt FROM dbo.mytable'

Then try use the built in sp called sp_Executesql http://msdn.microsoft.com/en-us/library/ms188001.aspx

This is because you are selecting the count of the variable not the count of the column.

Upvotes: 1

iandayman
iandayman

Reputation: 4467

you are matching @col (i.e. 'field1') against empty (i.e. '') in your where clause - that will never return a row.

Upvotes: 1

Related Questions