SQL Server - Table creation inside procedure - Asked By Vivi on 29-Jun-11 09:24 AM

can I create a table in stored procedure passing table name as a parameter.

I tried to pass a varchar parameter in stored procedure -

ALTER PROCEDURE tset123

(

@TabName VARCHAR(50)

)



AS

BEGIN

CREATE TABLE @TabName( [USERID] [varchar](200), Amount INT)

END

=======
but its throwing error-

Incorrect syntax near @TabName.

How do I can do this?







Peter Bromberg replied to Vivi on 29-Jun-11 09:40 AM
You would need to use a dynamically constructed SQL string to do this.

Declare @sql varchar(500)

Set @sql = 'CREATE TABLE ' + @TabName+ ' ( [USERID] [varchar](200), Amount INT)'
EXECUTE @sql

Ravi S replied to Vivi on 29-Jun-11 09:45 AM
HI

ry this

CREATE PROCEDURE [dbo].[Test2]
AS
BEGIN
CREATE TABLE ##OPTION (
  OPTION_PLAN VARCHAR(50)
, TOT_OPTIONS_EXCHANGED FLOAT NULL
)

   INSERT   ##OPTION
   SELECT  B.COMPONENT
, TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
    FROM TBLEXERCISEOPTIONS A
INNER JOIN TBLCOMPONENT B
ON B.COMPONENTID = A.COMPONENTID
  GROUP BY B.COMPONENT

DROP TABLE ##OPTION
GO
Jitendra Faye replied to Vivi on 30-Jun-11 01:13 AM
Yes, you can create table.

Follow this stored procedure-

CREATE PROCEDURE sp_TP_CreateTable

(
@TABLENAME as varchar (50)
)

AS
DECLARE @SQL varchar(2000)
SET @SQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@TABLENAME]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@TABLENAME]

CREATE TABLE [dbo].[" + @TABLENAME + "] (
[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Catalog Version] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mailed] [float] NULL ,
[Sales] [money] NULL ,
[Production $] [int] NOT NULL ,
[Orders] [float] NULL ,
[Response] [float] NULL ,
[Response of Test Control] [int] NOT NULL ,
[Average Invoice] [float] NULL ,
[SMP] [float] NULL ,
[SMP of Test Control] [int] NOT NULL ,
[Catalog Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Brand] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Drop Date] [smalldatetime] NULL
) ON [PRIMARY]"

EXEC(@SQL)
GO

Try this and let me know.