how to get table name as input parameter in SQL procedure

• nikki_d

  Mr or Mrs. 500

  Points: 569

  More actions

  September 26, 2011 at 12:46 am

  #244400

  Hi

  I am using follwing query for creating and inserting a table through a procedure..

  Alter PROCEDURE CreateInsertTable

  @TableName varchar (500),

  @InputTable varchar (1000)

  AS

  BEGIN

  declare @sql varchar(4000)

  select @sql = ' CREATE TABLE ' + @TableName + '

  (RecordNo BIGINT IDENTITY(1,1),

  GrpAddressID BIGINT,

  OccType1_CommaSeperated VARCHAR(1000) )'

  exec (@sql)

  declare @ssql nvarchar(4000)

  select @ssql = 'INSERT INTO ' + @TableName + ' (GrpAddressID)

  SELECT GrpAddressID

  FROM '+ @InputTable +'

  GROUP BY GrpAddressID

  ORDER BY GrpAddressID'

  exec (@ssql)

  END

  Execute statement :

  EXEC CreateTable @TableName= 'CommaSEperated3' ,@InputTable='AtulTest'

  Its Showing err message :

  Msg 8144, Level 16, State 2, Procedure CreateTable, Line 0

  Procedure or function CreateTable has too many arguments specified.

  Please help to resolve it.

  Thanks

  Atul

• CoolCodeShare

  SSCrazy

  Points: 2584

  More actions

  September 26, 2011 at 12:57 am

  #1386294

  Execute statement :

  EXEC CreateTable TableName= 'CommaSEperated3' ,@InputTable='AtulTest'

  Please read your question again by yourself. TableName should be written as @TableName. Also your procedure name is different in definition.

• nikki_d

  Mr or Mrs. 500

  Points: 569

  More actions

  September 26, 2011 at 1:06 am

  #1386299

  Sorry...That was my mistake...Actually name of procedure was incorrect...NOw its running fine..

  Thanks