We help IT Professionals succeed at work.

Passing Table Name as Parameter in SQL - StoredProcedure

375 Views
Last Modified: 2012-05-11

I have Table1,Table2,Table3 etc with same column names.

SQL Syntax :-

Create Proc usp_GetDetails
(
      @sTableName nvarchar(100)
)
as
begin

select * from @sTableName

end

This is not working ?? what could be the possible reason ?
Comment
Watch Question

Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
chokkaStudent

Author

Commented:
I have a very length SQL Statement with multiple joins ..!!!
Sharath SData Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

That is the pain you experience when passing table or field names as parameters to stored procedures.
Its not that bad so don't get discouraged
chokkaStudent

Author

Commented:
Something like this ...

Create Proc usp_GetDetails
(
      @sTableName nvarchar(100)
)
as
begin

select * from @sTableName
inner join Table A
inner join Table B
inner join Table C
Where : Condition 1
Where : Contion 2
Where : Condition 3

end



If it is a simple Select * from @VariableName - I would easily accepted the comment id # 35471079
Sharath SData Engineer
CERTIFIED EXPERT

Commented:
How many tables you can pass? If it is static like table1, table2 or table3, you can have 3 statements and execute them based on table name. Otherwise you have to go for dynamic sql.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
use Tablename.Columnname to avoid ambiguous name error

Commented:
Try this:
ALTER PROCEDURE [dbo].[probaParamLek]
@schema sysname,
@table_name sysname,
@packID varchar(100)
AS
BEGIN
SET NOCOUNT ON;
SET @table_name = RTRIM(@table_name)
SET @packID = RTRIM(@packID)
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + QUOTENAME(@schema) + N'.' + QUOTENAME(@table_name) + N' WHERE PackageID = @packID';
EXEC sp_executesql @cmd, N'@packID varchar(100)', @packdID;
END

Commented:
And why you want several table names as variable in your query. It is not recommended.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.