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)
A开发者_开发问答S
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?
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.
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.
you are matching @col (i.e. 'field1') against empty (i.e. '') in your where clause - that will never return a row.
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.
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.
精彩评论