Is it possible to pass the table name into a saved process and use it without the Exec function?
I would like to create an SP or UDF where I put the table and column name as a parameter and it does something to that end. I am using Sql Server 2005
A trivial example of what I am 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 trivial to illustrate what I am trying to accomplish in terms of passing the table name as a parameter.
Is it possible to do this without string concatenation and calling EXEC function?
Ultimately, I'm trying to convert the answer from this question to something reusable.
source to share
Not. I can't do this. Unfortunately, there is no macro pre-compiler in T-SQL. The closest you will get is SQLCMD mode, but this is for scripts only, cannot use it in object definitions.
Do you do the same on the table every time?
You can override the synonym dynamically, but it still requires EXEC and you lose concurrency. You can serialize the execution with a queue, but at this point you might be better off than plain old dynamic SQL.
You can try temporary tables, not passed as a variable, but created in the parent connection or call 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 exchange data between stored procedures see here: http://www.sommarskog.se/share_data.html
source to share