[dba-VB] [dba-SQLServer] I'm baffled

Stuart,

When I do that I get an error "must declare the scalar variable @DataCnt"


John W. Colby
www.ColbyConsulting.com


Stuart McLachlan wrote:
> 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 + '<> ''''))'
> 
>