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 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?

 
      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 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

[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)