By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

How to create a table from a variable name?

100+
P: 219
I'm very new to stored procedures so this is probably something very dumb. I want to pass a name of a table to be created to my stored procedure. I'm using the variable @tableName in the code below but I'm getting an error:

Server: Msg 170, Level 15, State 1, Procedure usp_CodeGreyData, Line 22
Line 22: Incorrect syntax near '@tableName'.
Server: Msg 137, Level 15, State 1, Procedure usp_CodeGreyData, Line 29
Must declare the variable '@tableName'.

Expand|Select|Wrap|Line Numbers
  1. SET QUOTED_IDENTIFIER ON 
  2. GO
  3. SET ANSI_NULLS ON 
  4. GO
  5.  
  6.  
  7. /*
  8. Name:  usp_CodeGreyData
  9. Description:  This stored procedure is used to pull some data from the 
  10.           codegreydiv table to display code grey diversion and status information.
  11. Author:  Doug Morand
  12. Modification Log: Change
  13.  
  14. Description                  Date         Changed By
  15. Created procedure            11/10/2008   Doug Morand
  16. */
  17. ALTER   PROCEDURE usp_CodeGreyData
  18.     --declare variables to be passed
  19.     @startDate datetime, -- start date to search from 
  20.     @endDate datetime, -- end date to search from
  21.     @tableName char  --name of newly created table
  22. AS
  23. BEGIN
  24.     --declare local variables if any are needed
  25.     CREATE TABLE @tableName(
  26.     id int not null primary key
  27.     cgstatus varchar(50),
  28.      divstatus varchar(50),
  29.     date datetime,
  30.     time datetime    
  31.     )    
  32.  
  33.     INSERT into @tableName(cgstatus,divstatus,date,time)    
  34.     SELECT 
  35.     case codegreystatus
  36.         when 0 then ''
  37.         when 1 then 'Active'
  38.     end AS cgstatus,
  39.     case diversionstatus
  40.         when 0 then ''
  41.         when 1 then 'Active'
  42.     end AS divstatus,
  43.         date,time 
  44.     from tblCodeGreyDiv
  45.     where (date >= @startDate and date <= @endDate)
  46.  
  47. END     
  48.  
  49.  
  50.  
  51.  
  52. GO
  53. SET QUOTED_IDENTIFIER OFF 
  54. GO
  55. SET ANSI_NULLS ON 
  56. GO
  57.  
  58.  
  59.  
Nov 10 '08 #1
Share this Question
Share on Google+
3 Replies


100+
P: 152
gpl
You will need to assemble a string that contains all the sql to create your table and then Exec it; likewise you will need to do the same for the select.

Are you sure that you need to create tables like this ?

Graham
Nov 10 '08 #2

Delerna
Expert 100+
P: 1,134
Another possibility is to use if statements.

Expand|Select|Wrap|Line Numbers
  1. create proc prProcName @tbl varchar(50)
  2. as
  3.    IF @tbl="Table1"
  4.    BEGIN
  5.       'write sql to affect Table1 here
  6.    END
  7.    IF @tbl="Table2"
  8.    BEGIN
  9.       'write sql to affect Table2 here
  10.    END
  11.    IF @tbl="Table3"
  12.    BEGIN
  13.       'write sql to affect Table3 here
  14.    END
  15.  
I likewise, don't understand why you would want to do it.
That dosn't mean I am saying that you don't have a good reason for it though.
Nov 10 '08 #3

100+
P: 219
Thanks for the tips guys. The reason I wanted a table variable was so that I could have the stored procedure make a new table each time I call it.
Nov 12 '08 #4

Post your reply

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