stored procedure error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure error

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-02 : 02:12:42

hi,
i try to pass field,tablename dynamically from front end,below
i have created procedure, but i through the error
Must declare the table variable "@tablename".

create procedure getkey
(
@fldgaugeid varchar(50),
@tablename varchar(50),
@keyfield varchar(40),
@description varchar(75)
)
as
set nocount on
select @keyfield from @tablename where @description = @fldgaugid

return @keyfield

Desikankannan

Sachin.Nand

2937 Posts

Posted - 2010-11-02 : 02:18:22

You need to use dynamic SQL for this.

PBUH

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-02 : 02:24:48

Hi,
pls give the suggestions
below again iam sending the code, i got the following error
Incorrect syntax near '@key'
Must declare the table variable "@tablename".

create procedure getkey
(
@fldgaugeid varchar(50),
@tablename varchar(50),
@description varchar(75)
)
as
set nocount on
declare @key int
set @key = 0;
@key = select desckey from @tablename where @description = @fldgaugid
return @key

Desikankannan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-02 : 03:56:10

Make sure to read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-11-02 : 04:30:12

Try this


create procedure getkey
(
@fldgaugeid varchar(50),
@tablename varchar(50),
@keyfield  varchar(50),
@description varchar(75)
)
as 
set nocount on

declare @sql nvarchar(max)

select @sql =
 'select ' +  @keyfield + ' from ' +  @tablename  + ' where ' + '''' +@description + '''' + ' = ' + @fldgaugeid

 
 exec sp_executesql 
 @sql,
 N'@fldgaugeid varchar(50),@tablename varchar(50),@keyfield  varchar(50),@description varchar(75)',
 @fldgaugeid,@tablename,@keyfield,@description

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources