470,596 Members | 1,430 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

Help with Dynamic SQL

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.  
Jun 27 '07 #1
3 1249
vijaii
15
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.  
Jun 27 '07 #2
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
Jun 27 '07 #3
srinit
43
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)
Jun 28 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by Stuart D. Gathman | last post: by
5 posts views Thread by Steve | last post: by
83 posts views Thread by deppy_3 | last post: by
30 posts views Thread by Alf P. Steinbach | last post: by
3 posts views Thread by =?Utf-8?B?YzY3NjIyOA==?= | last post: by
reply views Thread by richard12345 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.