TABLE TO SP

Question

TABLE TO SP

rs

SSCrazy

Points: 2819
More actions
April 22, 2004 at 9:51 am

#108042

Hi,
Is that possible to pass a table to stroed procedure, if yes would please give me one example?
Thanks,

Viewing 12 posts - 1 through 11 (of 11 total)

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

kokyan SSCommitted Points: 1634 More actions · Answer 1

Hi srqanqu,

I have one suggestion. You can pass the name of the table to the store procedure. Inside the procedure you will query the table using simple SQL statement!

For example:

create procedure usp_querytbl

@tblname varchar(100)

as

begin

set nocount on

declare @sql = 'select fld1, fld2 from '+@tblname

exec sp_executesql @sql

end

Regards,
kokyan

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

Kokyan gave a good example of "Dynamic SQL"... just wanted to remind everyone that dynamic SQL can be quite a bit slower than "Static SQL" because it does not pre-form an execution plan like normal stored procedures. It IS, as you've seen in Kokyan's example, VERY useful.

--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)

kokyan SSCommitted Points: 1634 More actions · Answer 3

Dynamic SQL will be slower than static SQL, but this performance issue is solved by using sp_executesql instead just use EXEC. sp_executesql will avoid recompile in the store procedure. Check BOL for sp_executesql comparing with EXEC only.

Regards,
kokyan

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

No execution plan is formed for Dynamic SQL until Runtime regardless of method of execution.

--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)

garfield_75025 SSC Veteran Points: 281 More actions · Answer 5

In Kokyan's example, don't you need to define @tblname as varchar(128) since table name in SQL 7 and above is defined as varchar(128) ?

rs SSCrazy Points: 2819 More actions · Answer 6

Thanks for your reply, but i want to pass temp table from one stored procedure to another.

imgodot Valued Member Points: 69 More actions · Answer 7

srqanqu,

I just did what you want. You don't need to do anything special to have a temp table visible in a called SP.

EXAMPLE:

------------------------------------------------

SP-1:

Creates temp table #MyTemp

Calls SP-2

------------------------------------------------

SP-2:

Get data from #MyTemp (the temp table is in scope)

-------------------------------------------------

Good luck.

-- Godot

rs SSCrazy Points: 2819 More actions · Answer 8

rs

SSCrazy

Points: 2819

April 23, 2004 at 9:25 am

#504027

Thanks.

Michael R Schmidt Mr or Mrs. 500 Points: 532 More actions · Answer 9

You need to be really careful with this for a couple of reasons, first, a #temp table is stored in the tempdb which can cause tempdb bloat, second, if the sp gets called while it is running it will overwrite previous values in #temp table.

Why would you want to pass an actual table to a sp?

Michael R. Schmidt
Developer

kokyan SSCommitted Points: 1634 More actions · Answer 10

I am not quite agree with MikeyMikey for the second reason regarding the temp table. It will not overwrire the temp table because another new temp table will be create for every session with format <temp_table_name>_timestamp.

Regards,
kokyan

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

Kokyan is correct... temp tables are always unique per session. You can see the actual name of a temp table in the tree view (press f8) of Query Analyzer. They are in the format of <TempTableName>_______________________<TimeStamp>. The number of underscores vary based on length of the table name and are used to fill the entire table name space. It's part of the reason why an "If Exists <TempTableName>" NEVER finds the temp table... you normally don't know the whole table name.

--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)