How to Create Table from Stored Procedure?

Question

I am trying to create table from stored procedure, but while executing the procedure, I am getting these errors:

Msg 343, Level 15, State 1, Line 1
Unknown object type 'TABLEPunith_INC_T_Partner_PlusEligible_YE_APJC' used in a CREATE, DROP, or ALTER statement.
Msg 4701, Level 16, State 1, Line 1
Cannot find the object "PTEST" because it does not exist or you do not have permissions.

This is my stored procedure

ALTER PROCEDURE [dbo].[SP_PTEST2]
AS
BEGIN
   DECLARE @OBJECTNM    NVARCHAR(100)
   DECLARE @SQLSTR      NVARCHAR(MAX) 

   SET @OBJECTNM =  'PTEST'

   IF (EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE NAME = 'PTEST'))
   BEGIN
        SET @SQLSTR = 'DROP TABLE ' + @OBJECTNM
        EXEC(@SQLSTR)
   END

   SET @SQLSTR = 'CREATE TABLE'  + @OBJECTNM + '(
    Country             VARCHAR(255),
    BE_Geo_ID               INT,
    BE_Geo_Name         VARCHAR(255),
    Certification           VARCHAR(200),   
    )'

   EXEC(@SQLSTR)

   SET @SQLSTR = 'TRUNCATE TABLE ' + @OBJECTNM
   EXEC(@SQLSTR)

   SET @SQLSTR = 'BULK INSERT ' + @OBJECTNM +
                 ' FROM ''C:\Desktop\Part plus.csv''
                   WITH (
                       FIELDTERMINATOR = '','',
                       ROWTERMINATOR = ''\n'',
                       FIRSTROW = 2
                  )'

   EXEC(@SQLSTR)
End

I spent hours on this but I am unable execute the stored procedure, also when I tried with different type procedure is executed but table is not created.

Somebody please help me on this.
Thanks in Advance.

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Apr 4, 2014 at 11:42
Thanks marc_s for suggestion, i have updated SP name and tried. — Punith
– Punith, Commented Apr 4, 2014 at 12:35

Daniel E. · Accepted Answer · 2014-04-04 12:38:01Z

4

SET @SQLSTR = 'CREATE TABLE_'  + @OBJECTNM + '(
    Country             VARCHAR(255),

Missing a space, add it where I put a _ above.
@SQLSTR was reading CREATE TABLEPTEST( instead of CREATE TABLE PTEST(

EDIT, the comment isn't clear, replace your line with the line below exactly. The above did not work because I used an underscore as a placeholder so that you could see where the missing space was.

SET @SQLSTR = 'CREATE TABLE '  + @OBJECTNM + '(

edited Apr 4, 2014 at 12:38

answered Apr 4, 2014 at 11:36

Daniel E.

2,0793 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Punith Over a year ago

Hello Daniel, I have edited like above but still i get the same error, so i edited like SET @SQLSTR = 'CREATE' + 'TABLE' + @OBJECTNM + '(..)', but now I am getting error like Incorrect syntax near 'Country'. i have updated question please have a look.

Daniel E. Over a year ago

you now have no spaces at all. it evaluates to CREATETABLEPTEST( the line needs to be SET @SQLSTR = 'CREATE TABLE ' + @OBJECTNM + '(

Collectives™ on Stack Overflow

How to Create Table from Stored Procedure?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related