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 ?

Hi

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

John

"N E" <to@to.com> wrote in message
news:3f38f8cf$0$16518$626a54ce@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 ?
>
>