how to pass user defined table in sql procdure

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)

how to pass user defined table in sql procdure

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-02-13 : 11:32:41

hi,

i want to pass user defined table, and two fields as parameter in stored procedure
and get the result like

select field1,field2 from table

Desikankannan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 11:34:37

are you using sql 2008? table valued parameters are allowed only from sql 2008 onwards

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

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-02-13 : 11:46:31

Hi,
iam using sql 2005,

i wants to right general procedure to fetch data to dropdownlist in asp.net

like create procedure dropdownvalues(@tablename,@field1,@field2)
as
select @field1,field2 from @tablename

whether it can work in sql2005 server

Desikankannan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 11:50:24

quote:
Originally posted by desikankannan

Hi,
iam using sql 2005,

i wants to right general procedure to fetch data to dropdownlist in asp.net

like create procedure dropdownvalues(@tablename,@field1,@field2)
as
select @field1,field2 from @tablename

whether it can work in sql2005 server

Desikankannan

Nope. you cant pass table like this. however you could just pass table name alone and do like this


create procedure dropdownvalues(@tablename,@field1,@field2)
as
EXEC('select '+ @field1 + ','+ field2 + ' from ' + @tablename)
go

but dynamic sql is not recommended. why should you pass fields & table like this?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources