Help with Dynamic SQL

Use the below Stored Procedure as an example


--Stored Procedure to select data from the given table name parameter
CREATE PROCEDURE DYNAMICTABLENAME
(
@TABLENAME VARCHAR(100)
)
AS

DECLARE @strSQL NVARCHAR(4000)
DECLARE @strTableName VARCHAR(100)

set @strSQL = 'select * from '
set @strSQL = @strSQL + @TABLENAME
print @strSQL
EXEC sp_ExecuteSQL @strSQL


--To execute the Stored Procedure
EXEC DYNAMICTABLENAME @TABLENAME='TABLENAME'

I want to use the following code below as Dynamic SQL because the first table name in the FROM clause, Staging, will change significantly. The code is part of a stored procedure that accepts the table name as an argument. How do I do that?

Expand|Select|Wrap|Line Numbers

1.  
2.       INSERT INTO StagingFinal(col1, col2,
3.  
4.                                col3,col4, col5, col6, col7, col8)
5.  
6.                SELECT stag.id,
7.  
8.                       REPLACE(stag.col1,stag.col1,'User' + stag.col1)
9.  
10.                     , us.id , ua.Users
11.  
12.                    , (CASE WHEN UPPER(Job) = 'Accounting' THEN 0 ELSE 1 END)
13.  
14.                     , stag.EndDate ,
15.  
16.                       CONVERT(INT, REPLACE(stag.earnings,'.000',''))
17.  
18.                     , stag.rep
19.  
20.                FROM Staging stag ,
21.  
22.                      dbo.Users us ,
23.  
24.                      dbo.UserAccs ua
25.  
26.                WHERE stag.cus = ''
27.  
28.                AND us.id = ua.id
29.  
30.                AND REPLACE(ua.AccountNumber,'-','') = stag.AccountNumber
31.  
32.  
33.  

Use the below Stored Procedure as an example


--Stored Procedure to select data from the given table name parameter
CREATE PROCEDURE DYNAMICTABLENAME
(
@TABLENAME VARCHAR(100)
)
AS

DECLARE @strSQL NVARCHAR(4000)
DECLARE @strTableName VARCHAR(100)

set @strSQL = 'select * from '
set @strSQL = @strSQL + @TABLENAME
print @strSQL
EXEC sp_ExecuteSQL @strSQL


--To execute the Stored Procedure
EXEC DYNAMICTABLENAME @TABLENAME='TABLENAME'

Thanks for your help Vijaii, I actually figure it out through trials and errors

I want to use the following code below as Dynamic SQL because the first table name in the FROM clause, Staging, will change significantly. The code is part of a stored procedure that accepts the table name as an argument. How do I do that?

Expand|Select|Wrap|Line Numbers

1.  
2.       INSERT INTO StagingFinal(col1, col2,
3.  
4.                                col3,col4, col5, col6, col7, col8)
5.  
6.                SELECT stag.id,
7.  
8.                       REPLACE(stag.col1,stag.col1,'User' + stag.col1)
9.  
10.                     , us.id , ua.Users
11.  
12.                    , (CASE WHEN UPPER(Job) = 'Accounting' THEN 0 ELSE 1 END)
13.  
14.                     , stag.EndDate ,
15.  
16.                       CONVERT(INT, REPLACE(stag.earnings,'.000',''))
17.  
18.                     , stag.rep
19.  
20.                FROM Staging stag ,
21.  
22.                      dbo.Users us ,
23.  
24.                      dbo.UserAccs ua
25.  
26.                WHERE stag.cus = ''
27.  
28.                AND us.id = ua.id
29.  
30.                AND REPLACE(ua.AccountNumber,'-','') = stag.AccountNumber
31.  
32.  

Hi , try like this
[code]
create procedure sp_name(@table varchar(20))
AS
exec('select * from '+@table)