passing table paramater - 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)

passing table paramater

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-03-04 : 08:09:48

Hi,
i try to pass tablename and 2 table fields as parameter in stored procedure,because i have lot of dropdownlist in my project
where i have two fields id and name 5 tables , i want write a comman
stored procedure

create procedure dropdown
(

@tablename varchar(50),
@fldcode int,
@fldname varchar(50)
)

as
select @fldcode,@fldname from @tables order by @fldcode

but i through the error
Must declare the table variable "@tables".

Desikankannan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 08:16:37

create procedure dropdown
(

@tablename varchar(50),
@fldcode int,
@fldname varchar(50)
)

as
declare @sql varchar(1000)
set @sql='select '+@fldcode+','+@fldname+' from '+@tables+' order by '+@fldcode)
EXEC(@sql)

For more informations about Dynamic SQL, refer www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 08:16:53

quote:
Originally posted by desikankannan

Hi,
i try to pass tablename and 2 table fields as parameter in stored procedure,because i have lot of dropdownlist in my project
where i have two fields id and name 5 tables , i want write a comman
stored procedure

create procedure dropdown
(

@tablename varchar(50),
@fldcode int,
@fldname varchar(50)
)

as
select @fldcode,@fldname from @tables order by @fldcode

but i through the error
Must declare the table variable "@tables".

Desikankannan

Seeing that,@tablename parameter is coming in...
It should be
select @fldcode,@fldname from @tablename order by @fldcode

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 08:21:08

quote:
Originally posted by haroon2k9

quote:
Originally posted by desikankannan

Hi,
i try to pass tablename and 2 table fields as parameter in stored procedure,because i have lot of dropdownlist in my project
where i have two fields id and name 5 tables , i want write a comman
stored procedure

create procedure dropdown
(

@tablename varchar(50),
@fldcode int,
@fldname varchar(50)
)

as
select @fldcode,@fldname from @tables order by @fldcode

but i through the error
Must declare the table variable "@tables".

Desikankannan

Seeing that,@tablename parameter is coming in...
It should be
select @fldcode,@fldname from @tablename order by @fldcode

But it is a different issue. See my previous reply

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 08:26:48

quote:
Originally posted by madhivanan

quote:
Originally posted by haroon2k9

quote:
Originally posted by desikankannan

Hi,
i try to pass tablename and 2 table fields as parameter in stored procedure,because i have lot of dropdownlist in my project
where i have two fields id and name 5 tables , i want write a comman
stored procedure

create procedure dropdown
(

@tablename varchar(50),
@fldcode int,
@fldname varchar(50)
)

as
select @fldcode,@fldname from @tables order by @fldcode

but i through the error
Must declare the table variable "@tables".

Desikankannan

Seeing that,@tablename parameter is coming in...
It should be
select @fldcode,@fldname from @tablename order by @fldcode

But it is a different issue. See my previous reply

Madhivanan

Failing to plan is Planning to fail

As iam seeing that as @tablename i/p param comes in so..iam Sorry madhi

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-03-04 : 08:28:08

I try you code but it shows the error

Error converting data type varchar to int.

create procedure dropdown
(

@tables varchar(50),
@fldcode int,
@fldname varchar(50)
)

as
declare @sql varchar(1000)
set @sql='select '+@fldcode+','+@fldname+' from '+@tables+' order by '+@fldcode
EXEC(@sql)

exec dropdown 'desckey','descvalue','mst_desc'

Desikankannan

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-03-04 : 08:38:14

working well thanks a lot madhi

quote:
Originally posted by madhivanan

quote:
Originally posted by haroon2k9

quote:
Originally posted by desikankannan

Hi,
i try to pass tablename and 2 table fields as parameter in stored procedure,because i have lot of dropdownlist in my project
where i have two fields id and name 5 tables , i want write a comman
stored procedure

create procedure dropdown
(

@tablename varchar(50),
@fldcode int,
@fldname varchar(50)
)

as
select @fldcode,@fldname from @tables order by @fldcode

but i through the error
Must declare the table variable "@tables".

Desikankannan

Seeing that,@tablename parameter is coming in...
It should be
select @fldcode,@fldname from @tablename order by @fldcode

But it is a different issue. See my previous reply

Madhivanan

Failing to plan is Planning to fail

Desikankannan

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 08:49:39

quote:
Originally posted by desikankannan

working well thanks a lot madhi

quote:
Originally posted by madhivanan

quote:
Originally posted by haroon2k9

quote:
Originally posted by desikankannan

Hi,
i try to pass tablename and 2 table fields as parameter in stored procedure,because i have lot of dropdownlist in my project
where i have two fields id and name 5 tables , i want write a comman
stored procedure

create procedure dropdown
(

@tablename varchar(50),
@fldcode int,
@fldname varchar(50)
)

as
select @fldcode,@fldname from @tables order by @fldcode

but i through the error
Must declare the table variable "@tables".

Desikankannan

Seeing that,@tablename parameter is coming in...
It should be
select @fldcode,@fldname from @tablename order by @fldcode

But it is a different issue. See my previous reply

Madhivanan

Failing to plan is Planning to fail

Desikankannan

can i ask.what u did to make it as work.i can see declared fldcode as int but while exec passed it as varchar value.it's like u changed fldcode as varchar or how?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 08:52:30

yup that needs to be varchar or else it will throw error when trying to concatenate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 08:55:58

quote:
Originally posted by visakh16

yup that needs to be varchar or else it will throw error when trying to concatenate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hi visakh.Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 08:56:41

I hope @fldcode should be a varchar as it is a column name passed to the procedure

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 08:59:10

quote:
Originally posted by madhivanan

I hope @fldcode should be a varchar as it is a column name passed to the procedure

Madhivanan

Failing to plan is Planning to fail

it should be
I think that was typo from poster

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-04 : 08:59:18

quote:
Originally posted by madhivanan

I hope @fldcode should be a varchar as it is a column name passed to the procedure

Madhivanan

Failing to plan is Planning to fail

Exactly.the way you catch was awesome madhi..Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-04 : 09:07:00

quote:
Originally posted by haroon2k9

quote:
Originally posted by madhivanan

I hope @fldcode should be a varchar as it is a column name passed to the procedure

Madhivanan

Failing to plan is Planning to fail

Exactly.the way you catch was awesome madhi..Thanks.

Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources