SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 stored procedure error		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

desikankannan
Posting Yak Master

India
134 Posts
Posted - 11/02/2010 :  02:12:42  Show Profile  Send desikankannan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts
Posted - 11/02/2010 :  02:18:22  Show Profile  Reply with Quote
You need to use dynamic SQL for this.


PBUH
Go to Top of Page

desikankannan
Posting Yak Master

India
134 Posts
Posted - 11/02/2010 :  02:24:48  Show Profile  Send desikankannan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 11/02/2010 :  03:56:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Make sure to read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts
Posted - 11/02/2010 :  04:30:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000