Is it possible to pass a table name into a stored proc and use it without the Exec Function?

Question

I would like to create a SP or UDF where I supply a table and column name as a parameter and it does something to that target. I'm using Sql Server 2005

Trivial Example of what I'm trying to accomplish:

CREATE FUNCTION Example (@TableName AS VARCHAR(100))
RETURNS TABLE
AS
BEGIN
    SELECT * 
    INTO #temp
    FROM @TableName

    RETURN #temp
END

The example is just something trivial to illustrate what I'm trying to accomplish in terms of passing the Table name as a parameter.

Is this possible to do w/o concatinating strings and calling the EXEC function?

Ultimately, I'm trying to convert the answer from this question into something reusable.

Answer 1

This reeks of SQL injection. You would still need to use EXEC to do this.

Answer 2

No. Can't do it. Sadly, there is no macro pre-complier in T-SQL. The closest you'll get is SQLCMD mode, but that's only for scripts, can't use it in object definitions.

Are you doing the same thing to the table each time?

You could dynamically redefine a synonym, but that still requires an EXEC and you lose concurrency. You could serialize execution with a queue, but at that point you may be better off w/ plain old dynamic SQL.

You might try temporary tables, not passed in as a variable, but created in the parent connection or calling procedure. eg.

create proc #proc as
select * from #table
go

create table #table (col1 int)
insert #table values (1)
insert #table values (2)
insert #table values (3)
go

exec #proc
go

For more ways to share data between stored procedures, see here: http://www.sommarskog.se/share%5Fdata.html