Can you pass a table to a store procedure?

pbyrum · January 25th, 2005, 06:13 PM

Hello,

I want to pass a table to a MS SQL Server stored procedure. Can this be done? Can you pass a data set with a talbe? I have multi-dimension (2 column) data that I want to save to the database.

Thanks,

Paul

jemacc · January 26th, 2005, 01:43 PM

you can not pass the table name directly. Using dynamic SQL can give you the needed results

Code:

USE EXEC 
                 CREATE PROCEDURE passtable_name @table varchar(50) 
                 AS 
                 DECLARE @sql_string VARCHAR(200) 
                 DECLARE @total FLOAT 
                 SELECT @sql_string="SELECT SUM(cost) FROM "+ @table 
                 exec (@SQL_STRING)

Jaime E. Maccou