Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Passing Table Name as Parameter in SQL - StoredProcedure


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 ?
0
chokka
Asked:
chokka
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
Ephraim WangoyaCommented:
Use dynamic sql as follows
Create Proc usp_GetDetails
(
      @sTableName nvarchar(100)
)
as
begin
  declare @sql varchar(8000)
  set @sql = 'select * from ' + @sTableName
  EXEC(@sql)
end

Open in new window

0
 
chokkaAuthor Commented:
I have a very length SQL Statement with multiple joins ..!!!
0
 
SharathData EngineerCommented:
You can create the dynamic sql with your query by passing this table name as parameter to SP. Can you explain your requirement more?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ephraim WangoyaCommented:

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
0
 
chokkaAuthor 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
0
 
SharathData EngineerCommented:
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.
0
 
Alpesh PatelAssistant ConsultantCommented:
use Tablename.Columnname to avoid ambiguous name error
0
 
havj123Commented:
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
0
 
havj123Commented:
And why you want several table names as variable in your query. It is not recommended.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now