Help with Dynamic SQL

[yaaadman]

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?

 
      INSERT INTO StagingFinal(col1, col2,

                               col3,col4, col5, col6, col7, col8)

               SELECT stag.id,

                      REPLACE(stag.col1,stag.col1,'User' + stag.col1)

                    , us.id , ua.Users

                   , (CASE WHEN UPPER(Job) = 'Accounting' THEN 0 ELSE 1 END)

                    , stag.EndDate ,

                      CONVERT(INT, REPLACE(stag.earnings,'.000',''))

                    , stag.rep

               FROM Staging stag ,

                     dbo.Users us ,

                     dbo.UserAccs ua

               WHERE stag.cus = ''

               AND us.id = ua.id

               AND REPLACE(ua.AccountNumber,'-','') = stag.AccountNumber

[vijaii]

Use the below Stored Procedure as an example


--Stored Procedure to select data from the given table name parameter
CREATE PROCEDURE DYNAMICTABLENAM E
(
@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 DYNAMICTABLENAM E @TABLENAME='TAB LENAME'

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?

 
      INSERT INTO StagingFinal(col1, col2,

                               col3,col4, col5, col6, col7, col8)

               SELECT stag.id,

                      REPLACE(stag.col1,stag.col1,'User' + stag.col1)

                    , us.id , ua.Users

                   , (CASE WHEN UPPER(Job) = 'Accounting' THEN 0 ELSE 1 END)

                    , stag.EndDate ,

                      CONVERT(INT, REPLACE(stag.earnings,'.000',''))

                    , stag.rep

               FROM Staging stag ,

                     dbo.Users us ,

                     dbo.UserAccs ua

               WHERE stag.cus = ''

               AND us.id = ua.id

               AND REPLACE(ua.AccountNumber,'-','') = stag.AccountNumber

[yaaadman]

Use the below Stored Procedure as an example


--Stored Procedure to select data from the given table name parameter
CREATE PROCEDURE DYNAMICTABLENAM E
(
@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 DYNAMICTABLENAM E @TABLENAME='TAB LENAME'

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

[srinit]

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?

 
      INSERT INTO StagingFinal(col1, col2,

                               col3,col4, col5, col6, col7, col8)

               SELECT stag.id,

                      REPLACE(stag.col1,stag.col1,'User' + stag.col1)

                    , us.id , ua.Users

                   , (CASE WHEN UPPER(Job) = 'Accounting' THEN 0 ELSE 1 END)

                    , stag.EndDate ,

                      CONVERT(INT, REPLACE(stag.earnings,'.000',''))

                    , stag.rep

               FROM Staging stag ,

                     dbo.Users us ,

                     dbo.UserAccs ua

               WHERE stag.cus = ''

               AND us.id = ua.id

               AND REPLACE(ua.AccountNumber,'-','') = stag.AccountNumber

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