SQL Server Performance

Create table in stored procedure

Discussion in 'General Developer Questions' started by rasha, Jul 23, 2004.

  1. rasha New Member

    Hello,
    I want to create table in stored procedure, the following is a simple example, but it DOES Not work, no error message is created in the query analyzer but no table is created


    Create PROCEDURE Test2

    AS
    SET NOCOUNT ON
    begin


    --create table
    CREATE TABLE fgh
    (a INTEGER, b INTEGER)

    End

    Knowing that if I run the follwing code portion, the table will be created


    CREATE TABLE fgh
    (a INTEGER, b INTEGER)

    I use sql server 2000 , Any suggestions?

    Thanks a lot

    Rasha zaki
    Web Developer
    Cairo, Egypt
  2. Luis Martin Moderator

    What SO and SP (SO and SQL), because your sp works fine.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. rasha New Member

    I know it should work by logic but when I run it, no table is created, I do not know why and this is my question.
    Thanks
  4. Chappy New Member

    so what is the output when you run this code...?



    ALTER PROCEDURE Test2
    AS
    SET NOCOUNT ON
    begin
    --create table
    CREATE TABLE fgh(a INTEGER, b INTEGER)
    SELECT * from fgh
    End
    GO
    exec Test2

  5. rasha New Member

    Hi Chappy,
    It has worked with your last code line. Thank you,
    but why do I need this exec command. what I understand is that I
    instruct query analyzer to create a procedure which creates a table ...
    so why do I need to instruct it again to exec the procedure, sorry if it
    is a stupid question but I need to understand.

    Thanks a lot again


    Rasha zaki
    Web Developer
    Cairo, Egypt
  6. Luis Martin Moderator

    When I said your sp works fine, I mean I test it.

    From BOL:

    Executing a Stored Procedure
    When you have to execute a stored procedure, use the Transact-SQL EXECUTE statement. You can execute a stored procedure without using the EXECUTE keyword if the stored procedure is the first statement in the batch.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  7. Chappy New Member

    quote:Originally posted by rasha

    Hi Chappy,
    It has worked with your last code line. Thank you,
    but why do I need this exec command. what I understand is that I
    instruct query analyzer to create a procedure which creates a table ...
    so why do I need to instruct it again to exec the procedure, sorry if it
    is a stupid question but I need to understand.

    Thanks a lot again


    Rasha zaki
    Web Developer
    Cairo, Egypt

    You didnt ask sql server to create the table. You asked SQL server to create a procedure which was capable of rceating the table. But it wont run the code in the proc until the proc is executed
  8. rasha New Member

    Thanks Chappy and LuisMartin, I can understand now.
    I am now stuck with a new problem. I don't know if you have experience with it or no.
    but if you have, please help me. I will send it in a new thread.

    Thanks


    Rasha zaki
    Web Developer
    Cairo, Egypt

Share This Page