Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2004
    Location
    Tokyo, Japan
    Posts
    17

    Question Unanswered: probrem with exiting from T_SQL batch statements

    Hi.
    Let me Say there is a script file like following,
    which has several T_SQL statements.

    In the file,my purpose is create SP if @temp is not 1, or do nothing:
    ----------------------hoge.sql --------------------------------
    DECLARE @temp int
    SELECT @temp = hoge FROM T_HOGE WHERE hoge_id = 123

    IF @temp = 1 BEGIN
    /****************
    I WANT TO EXIT HERE!!!!
    ****************/
    END

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XXX]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[XXX]
    GO

    Create procedure XXX
    AS
    .........
    GO
    --------------------------------------------------------------------

    First I tried to write simply like this:
    -------------------------------------
    IF @temp <> 1 BEGIN

    /* create SP here */
    if exit ......
    GO
    .......
    END
    -------------------------------------
    But this can not be allowed by SQL Server.

    Next I use GOTO statement:
    -------------------------------------
    IF @temp = 1 GOTO __end

    /* create SP here */
    if exit ......
    GO
    .......

    __end:
    -------------------------------------
    ...and this doesn't work either.

    finally I think to use RAISERROR function with 'WITH LOG' option
    and hope that the function disconnect the connection to DB.

    But to use RAISERROR with 'WITH LOG' option,
    I must get sysadmin role, and I can not do it in my enviornment.

    Anyone can help?

    Thanks for reading.
    Last edited by maekawa; 01-20-04 at 01:53.
    Takanobu Maekawa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •