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
    rasha, Jul 23, 2004
    #2

  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.

    Luis Martin, Jul 23, 2004
    #2

  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
    rasha, Jul 23, 2004
    #2

  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

    Chappy, Jul 24, 2004
    #2

  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
    rasha, Jul 24, 2004
    #2

  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.

    Luis Martin, Jul 24, 2004
    #2

  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
    Chappy, Jul 24, 2004
    #2

  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
    rasha, Jul 24, 2004
    #2

Share This Page