Solved

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

Posted on 2013-01-31
5
255 Views
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.

example


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?

thanks
0
Comment
Question by:hidrau
5 Comments
 
LVL 12

Expert Comment

by:Jared_S
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?
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 250 total points
ID: 38839491
Hi,

Inside the stored procedure instead of

select * from @table

Open in new window

write
EXEC('SELECT * FROM ' + @table)

Open in new window

Giannis
0
 
LVL 40

Expert Comment

by:lcohan
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;

--instead...
0
 
LVL 50

Assisted Solution

by:Lowfatspread
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

exec(@sql)

would be one way to achieve it
0
 
LVL 1

Author Closing Comment

by:hidrau
ID: 38892768
thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 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