Hi all,

I want to pass the name of a table as a parameter to a stored procedure...for example something like

CREATE PROCEDURE MoveDataFromTempFiles
    @tempTestChainTable varchar (150),
    @tempProcessID int

 AS
    Select * from @tempTestChainTable where ID = @tempProcessID

However, I get the error @tempTestChainTable. Do table names have to be static?? Any help regarding this will be greatly appreciated.

Thanks,

Pankaj

Hi, this is what I use when I want to pass a table
CREATE PROCEDURE dbo.getTable
    @tableName varchar(50)
    ,@fieldName varchar(20) = NULL
    ,@value varchar(20) = @fieldName
AS
  IF @fieldName = NULL
    BEGIN
    EXEC ('SELECT * FROM '+ @tableName)
    END
  ELSE
    BEGIN
    EXEC ('SELECT * FROM ' + @tableName + ' WHERE ' + @fieldName + ' = "' + @value + '" ')
    END
GO

Ian

Hi Ian.

Thanks! I will give it a shot :)

Pankaj

Using EXEC to execute a SQL Statement will attract some cost on performance. everytime it has to parse the sql statement before executing...use it when there is a absolute need.

Not to mention which such an approach opens your server to a SQL injection attack. Consider what happens when an attacker executes the stored procedure above and sets the value of @tablename to a string like:
you're in real trouble...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff
Thanks for the interesting comment, I will keep an eye this post for further/alternative developments

Regards
Ian

Wow, I never thought if it like that. I have to keep all this in mind. I will post an update as soon as I have some satisfactory solution.

Thanks,
Pankaj

In your journey for a satisfactory solution, you may be interested in the following White Paper on SQL injection:

http://www.nextgenss.com/papers/adva..._injection.pdf

and other info and links from this thread.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.