[dba-SQLServer] I'm baffled

jwcolby jwcolby at colbyconsulting.com
Tue Sep 22 21:57:39 CDT 2009


I am trying to return a count of data items in a field of my table.  When I directly execute the 
following I get a count:

SELECT COUNT(PKID) as Cnt
FROM dbo.tblHSIDModified
WHERE (Household_Occupation_code <> '')

The count is 15675589.

When I try to do the "same thing" in a SP (to store the count) the count is equal to the number of 
records in the table.  The SQL is:

DECLARE @DataCnt int

SET @DataCnt = (SELECT count(PKID) as Cnt
		FROM dbo.tblHSIDModified
		WHERE (@fldname <> ''))

PRINT @fldname + ' counted: ' + cast(@DataCnt as varchar) + ' valid data elements'

As you can see everything is the same except @FldName which is being read out of a cursor of field 
names.  It seems obvious that @fldname <> '' evaluates to true every record.  I don't know enough to 
know why.

Somehow I need to "translate" @FldName to the actual name of the field.

What is the syntax to get what I need here?

TIA,

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list