[dba-SQLServer] I'm baffled

Household_Occupation_Code is a fieldname (an object)
@fldname is a string

Your SP version evaluates to:

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

rather than

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


To interpret the variable, you could build your query dynamically and then EXECute it in 
your SP.  Something like:

EXEC 'SET @DataCnt = (SELECT count(PKID) as Cnt ' + 
		'FROM dbo.tblHSIDModified ' + 
 		'WHERE (" + @fldsname + '<> ''''))'


-- 
Stuart

On 22 Sep 2009 at 22:57, jwcolby wrote:

> 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
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>