how to - dynamic queries in stored procedure

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 2000 Forums

Transact-SQL (2000)

how to - dynamic queries in stored procedure

Author

Topic

kami909
Starting Member

3 Posts

Posted - 2005-12-21 : 16:40:39

hello every one

i want to know how should i use dynamic table name to get data from the table

like i want user to select name of the table & my query should return the the data from the selected table

i m trying this



declare @MyTable nvarchar(50)
set @MyTable='Customers'
select * from @MyTable

but its not working :-(

later i want user to select colums as well

i want to perform all this query in stored procedure

any idea ?

i m using sql server 2000

thanx any way ..

jhermiz

3564 Posts

Posted - 2005-12-21 : 16:57:11

Not possible

Read here: http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/36c766903c337075/21fe40860e4f8feb?lnk=st&q=Can+I+declare+a+table+as+a+variable+in+SQL+%3F&rnum=1&hl=en#21fe40860e4f8feb

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-21 : 22:50:14

try this..
create proc dynamicsql (@tablename varchar(100))as
declare @sqlstring varchar(200)
set @sqlstring= 'Select * from ' + @tablename
exec (@sqlstring)
subsequently u can replace '*' by a variable having column names

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-21 : 23:17:31

you may also use sp_executesql.

Refere to Books Online for details.

-----------------
[KH]
Learn something new everyday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources