how to run a query in Store procedure from a table passed by parameter?

Posted on 2013-01-31
Last Modified: 2013-02-15
Hello guys

I have a store procedure with a parameter that I will pass a table,

how can I run this table in store procedure that I passed by parameter.


exect run_table "nome_table"

inside the store procedure

select * from @table

but this way it doesn't work, I think it must be a string of script

how do that?

Question by:hidrau
LVL 12

Expert Comment

ID: 38839488
Can you create your variable table within your stored procedure, or create your table as a temp table instead and just reference it directly in your sp?
LVL 23

Accepted Solution

Ioannis Paraskevopoulos earned 250 total points
ID: 38839491

Inside the stored procedure instead of

select * from @table

Open in new window

EXEC('SELECT * FROM ' + @table)

Open in new window

LVL 39

Expert Comment

ID: 38840647
inside the store procedure

select * from @table

but this way it doesn't work,

--with @tables like with variables - try run just:

SELECT @table;

LVL 50

Assisted Solution

Lowfatspread earned 250 total points
ID: 38843411
if you want to pass a table name to a  stored procedure and then have the procedure
run query against that table then you basically need to use dynamic sql in the query...

however it is a security issue to have such a generalised stored procedure , and normally as a solution is strongly not recommended.

declare @sql varchar(1000)

set @sql='Select * from '+@table_name_parameter


would be one way to achieve it

Author Closing Comment

ID: 38892768

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now