How to create a table from a variable name?

[dmorand]

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_CodeGreyDat a, Line 22
Line 22: Incorrect syntax near '@tableName'.
Server: Msg 137, Level 15, State 1, Procedure usp_CodeGreyDat a, Line 29
Must declare the variable '@tableName'.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*
Name:  usp_CodeGreyData
Description:  This stored procedure is used to pull some data from the 
	      codegreydiv table to display code grey diversion and status information.
Author:  Doug Morand
Modification Log: Change

Description                  Date         Changed By
Created procedure            11/10/2008   Doug Morand
*/
ALTER   PROCEDURE usp_CodeGreyData
    --declare variables to be passed
    @startDate datetime, -- start date to search from 
    @endDate datetime, -- end date to search from
    @tableName char  --name of newly created table
AS
BEGIN
    --declare local variables if any are needed
    CREATE TABLE @tableName(
	id int not null primary key
	cgstatus varchar(50),
 	divstatus varchar(50),
	date datetime,
	time datetime	
    )	
   
    INSERT into @tableName(cgstatus,divstatus,date,time)    
    SELECT 
	case codegreystatus
	    when 0 then ''
	    when 1 then 'Active'
	end AS cgstatus,
	case diversionstatus
	    when 0 then ''
	    when 1 then 'Active'
	end AS divstatus,
        date,time 
    from tblCodeGreyDiv
    where (date >= @startDate and date <= @endDate)
    	
END     




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

[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

[Delerna]

Another possibility is to use if statements.

[code=sql]
create proc prProcName @tbl varchar(50)
as
IF @tbl="Table1"
BEGIN
'write sql to affect Table1 here
END
IF @tbl="Table2"
BEGIN
'write sql to affect Table2 here
END
IF @tbl="Table3"
BEGIN
'write sql to affect Table3 here
END
[/code]

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.

[dmorand]

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.