send Table name to procedure as a parameter

Question

send Table name to procedure as a parameter

pooya1072

SSC Veteran

Points: 256
More actions
March 17, 2013 at 3:11 pm

#271781

hi
i create a procedure in my database.this SP select all rows from a table :
CREATE PROCEDURE FillTable
AS
BEGIN
select * from [dbo].[TableName]
END
now ,i want to send the name of table as a parameter to SP . then this name (parameter) use in Select command .
how can i do it with this way or any way you know?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Dynamic SQL is the only way you can do that.

I strongly recommend you do not go this route. Procedures should have a well-defined function, not do whatever based on a parameter passed. You wouldn't write a function in C# that could return Employees, Departments or Cars depending on the parameters passed, don't do it in SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

I gree with Gail on the dynamic SQL part of this. Depending on what you're actually doing, I may agree with her on not doing. It really does depend on the situation.

With that thought is mind, what is the end goal here and why do you think you need to use a variable table name? Are you, for example, using a "poor mans" type of partitioning that uses dates as part of a table name of identically structured tables to indicate what date range the data for each table is in?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pooya1072 SSC Veteran Points: 256 More actions · Answer 3

pooya1072

SSC Veteran

Points: 256

March 17, 2013 at 3:54 pm

#1597924

ok...thanks

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

pooya1072 (3/17/2013)
ok...thanks

Wait a minute... if you take the time to explain the end goal, there might be a better way. We just don't know the end goal here well enough to come up with an alternate solution and there could be many depending on what you're actually trying to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)