[dba-VB] I'm baffled

John,

Just an idea off the top of my head, have you tried enclosing the @fldname
in brackets like below:

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'

Paul



2009/9/23 jwcolby <jwcolby at colbyconsulting.com>

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


-- 
Paul Hartland
paul.hartland at googlemail.com