SELECT statement does not work. If

[rajkum]

SELECT statement does not work.
The IF condition below returns TRUE when it should not.
If i use the column name explicitely and pass the member_id as parameter it works.
Declaration and stuff like that is done..
@erMsg and @err are Output params.
@member_id_typ and @member_id are input params.

IF (SELECT COUNT(*) FROM member_info WHERE @member_id_typ = @member_id ) = 0
BEGIN
SET @erMsg = @erMsg +'**'+&quot;Person with &quot; + @member_id_typ + &quot;-->><b>&quot; + @member_id + &quot;</b> not Found&quot;
SET @err = 'Y'
END

 

[MDXer]

Your comparing your 2 input variables rather than comparing them with column values.

IF (SELECT COUNT(*) FROM member_info WHERE @member_id_typ = @member_id ) = 0
BEGIN
SET @erMsg = @erMsg +'**'+&quot;Person with &quot; + @member_id_typ + &quot;-->><b>&quot; + @member_id + &quot;</b> not Found&quot;
SET @err = 'Y'
END
maybe you are after this:

IF (SELECT COUNT(*) FROM member_info WHERE member_id_typ = @member_id_typ And member_id = @member_id ) = 0
BEGIN
SET @erMsg = @erMsg +'**'+&quot;Person with &quot; + @member_id_typ + &quot;-->><b>&quot; + @member_id + &quot;</b> not Found&quot;
SET @err = 'Y'
END



&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[NoCoolHandle]

Try

@@rowcount rather than = 0 from a select..

I.E.

select count(*) from member_info where @member_id_typ = @memberID

if @@rowcount = 0
Begin

end

@@rowcount returns the number or rows returned by the last query..

HTH

Rob

 

[rajkum]

thanks..but

@member_id_typ is supposed to be column name which is viariable

@member_id is the value.

 

[rajkum]

NoCoolHandle,

It returns 0 no matter what!! and thats the problem.

rajkum

 

[Vancouverite]

You cannot specify a field name in a variable without using dynamic SQL. Your query needs to be changed like this:

Declare @SQL varchar(400)
Declare @Count int
SET @SQL = 'SELECT @ct = COUNT(*) FROM member_info WHERE ' + @member_id_typ + ' = @mbr_id'

EXEC sp_executeSQL @sql, N'@mbr_id int, @Ct int OUTPUT',
@mbr_id = @Member_id, @Ct = @Count

IF @Count = 0
BEGIN
SET @erMsg = @erMsg +'**'+&quot;Person with &quot; + @member_id_typ + &quot;-->><b>&quot; + @member_id + &quot;</b> not Found&quot;
SET @err = 'Y'
END