problem with stored procedure and tablename parameter

[N E]

hello

I am trying to do a procedure that :
1 - Take a table name, a field name and a value as parameter
2 - check if the fieldname=value in the tablename table
3 - returns 1 if so, 0 if not

A -
I tried :

drop procedure checkmytable
GO

create procedure checkmytable
@p_table nvarchar(50),
@p_field nvarchar(50),
@p_value nvarchar(50)
as
if exists ( select * from @p_table where @p_field = @p_value)
return 1
else
return 0

declare
@res int

exec @res=checkmytable 'mytable' , 'myfield' , '456123'
print @res
in this case, I always get the error : 'must declare @p_table'

B -
I tried

drop procedure checkmytable
GO

create procedure checkmytable
@p_table nvarchar(50),
@p_field nvarchar(50),
@p_value nvarchar(50)
as

return exec( 'if exists (select * from' + @p_table + ' where ' + @p_field +
' = ' + @p_value + ') return 1 else return 0' )
GO

declare
@res int

exec @res=checkmytable 'AM_ASSET' , 'ASSET_ID' , '18705'
print @res
in this case, I always get 0

any help ?

[John Bell]

Hi

Read http://www.algonet.se/~sommar/dynamic_sql.html and the other
interesting articles on Erlands site.

John

"N E" <[e-mail]> wrote in message
news:3f***********************@news.free.fr...

hello

I am trying to do a procedure that :
1 - Take a table name, a field name and a value as parameter
2 - check if the fieldname=value in the tablename table
3 - returns 1 if so, 0 if not

A -
I tried :

drop procedure checkmytable
GO

create procedure checkmytable
@p_table nvarchar(50),
@p_field nvarchar(50),
@p_value nvarchar(50)
as
if exists ( select * from @p_table where @p_field = @p_value)
return 1
else
return 0

declare
@res int

exec @res=checkmytable 'mytable' , 'myfield' , '456123'
print @res
in this case, I always get the error : 'must declare @p_table'

B -
I tried

drop procedure checkmytable
GO

create procedure checkmytable
@p_table nvarchar(50),
@p_field nvarchar(50),
@p_value nvarchar(50)
as

return exec( 'if exists (select * from' + @p_table + ' where ' + @p_field + ' = ' + @p_value + ') return 1 else return 0' )
GO

declare
@res int

exec @res=checkmytable 'AM_ASSET' , 'ASSET_ID' , '18705'
print @res
in this case, I always get 0

any help ?