Is it possible to use table variable as parameter for Stored Procedure 1

[vladibo]

Is it possible to use table variable as parameter for Stored Procedure?

Like this:
CREATE PROCEDURE SD_sp_HitLog

@tbl_result table(gui_id uniqueidentifier) OUT

AS

(but this doesn't work)

 

[mrdenny]

No, you can't use a table variable to pass an array in or out of a stored procedure. Consider using a temp table (#tbl_result) instead.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

Doesn't it have to be a global temp table to do that? And when does a global table go out of scope and destruct?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

No it doesn't need to be a global temp table. A Temp table isn't destroyed until the process is killed, or the connection is closed.

You can create a temp table, load data into it, run a proc which updates the table, then pull data from the table after the proc with out a problem.

Global Temp Tables are destroyed when the user drops them, or when the SQL Service is restarted. (They are stored in TempDB, and when SQL is restarted tempdb is rebuilt.) I try to avoide Global Temp Tables when ever possible, just in case more than one session tried to create the same global temp table.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[vladibo]

How can I pass #temp as a parameter to stored procedure?

 

[SQLSister]

You cannot pass a table in any way as a parameter to a stored procedure. You can use a temp table within a stored procedure but not pass it as a parameter.

Questions about posting. See faq183-874

 

[mrdenny]

You would need to pass it as a varchar variable and use dynamic sql to use the variable.

However using dynamic sql causes a whole set of security and performance issues.

Your best bet would be to hard code the temp table name into the proc.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

I tried creating a SP which creates a Temp table and puts two rows in it. Then from QA I did:
Exec spTest
Select * from #Temp
It fails to find #Temp. How do you keep the process or connection open? Maybe your talking about a OLEDB connection?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

OK, It looks like I was mistaken.
Temp tables created within a procedure will be dropped when the procedure ends.

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

Global temporary tables are dropped when the session that created them ends, and all other tasks has stopped using them.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

To use a temp table to get data in and out of a stored proc you have to create the table out side of the proc.

create procedure spTest as
insert into #Test
(val)
values
(1)
go
create table #Test
(val int)

exec spTest

select * from #Test

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

That quote on the Global temporary tables was about as clear as mud until I realized an open window of QA is a session. So the Global table hangs around until that window is actually closed. Unless another session was actually running a query on the table when it was closed! Then apparently it lasts for the duration of that query only.
I'd give you a 2nd star, but I don't think that works.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]