Solved

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

Posted on 2013-01-31
5
250 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
Comment Utility
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
Comment Utility
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 39

Expert Comment

by:lcohan
Comment Utility
<<
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

15 Experts available now in Live!

Get 1:1 Help Now