Returning stored proc values


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Returning stored proc values

Hybrid View

  1. #1
    Yog Guest

    Returning stored proc values


    Look at the following script

    ********************** sp_testerror *********************

    SET NOCOUNT ON

    if (exists (select * from sysobjects
    where name = 'testerror' and type = 'U '))
    drop table testerror
    go

    create table testerror (LoginName varchar(20) null)
    go


    insert testerror (LoginName) values ('xavier')
    go


    create unique nonclustered index testerror1 on testerror (loginname)

    go

    drop proc sp_testerror
    go

    create proc sp_testerror @newname varchar(20)
    as

    DECLARE @Message varchar(255)

    BEGIN TRANSACTION

    insert testerror (LoginName) values (@newname)

    IF (@@error != 0)
    BEGIN
    Select @Message = 'did not work'
    Select Message = (@newname + ' ' + @Message)
    ROLLBACK TRAN
    RETURN
    END
    ELSE
    BEGIN

    Select @Message = 'did work'
    Select Message = (@newname + ' ' + @Message)
    END


    COMMIT TRANSACTION
    go



    NOW, EXECUTE THIS,
    EXEC sp_testerror 'test'

    When i try to insert a duplicate , i should get a message like
    'did not work test' instead of the sql error message?
    What should i change in this proc to do this ?

    Yog


  2. #2
    DaveSatz Guest

    Re: Returning stored proc values


    create proc sp_testerror @newname varchar(20)
    as

    DECLARE @Message varchar(255)

    BEGIN TRANSACTION

    insert testerror (LoginName) values (@newname)

    IF @@error <> 0
    BEGIN
    Set @Message = @newname + ' ' + 'did not work'
    ROLLBACK TRAN
    RAISERROR ( @Message, 16, 1)
    RETURN
    END
    ELSE
    BEGIN
    Set @Message = @newname + ' ' + 'did work'
    PRINT @Message -- not want to raise an error if it worked
    END


    COMMIT TRANSACTION
    go



    --
    HTH,
    David Satz
    Principal Software Engineer
    Hyperion Solutions
    ->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "Yog" <yogaraj@usa.net> wrote in message news:3ac997bb$1@news.devx.com...
    >
    > Look at the following script
    >
    > ********************** sp_testerror *********************
    >
    > SET NOCOUNT ON
    >
    > if (exists (select * from sysobjects
    > where name = 'testerror' and type = 'U '))
    > drop table testerror
    > go
    >
    > create table testerror (LoginName varchar(20) null)
    > go
    >
    >
    > insert testerror (LoginName) values ('xavier')
    > go
    >
    >
    > create unique nonclustered index testerror1 on testerror (loginname)
    >
    > go
    >
    > drop proc sp_testerror
    > go
    >
    > create proc sp_testerror @newname varchar(20)
    > as
    >
    > DECLARE @Message varchar(255)
    >
    > BEGIN TRANSACTION
    >
    > insert testerror (LoginName) values (@newname)
    >
    > IF (@@error != 0)
    > BEGIN
    > Select @Message = 'did not work'
    > Select Message = (@newname + ' ' + @Message)
    > ROLLBACK TRAN
    > RETURN
    > END
    > ELSE
    > BEGIN
    >
    > Select @Message = 'did work'
    > Select Message = (@newname + ' ' + @Message)
    > END
    >
    >
    > COMMIT TRANSACTION
    > go
    >
    >
    >
    > NOW, EXECUTE THIS,
    > EXEC sp_testerror 'test'
    >
    > When i try to insert a duplicate , i should get a message like
    > 'did not work test' instead of the sql error message?
    > What should i change in this proc to do this ?
    >
    > Yog
    >




  3. #3
    Yog Guest

    Re: Returning stored proc values


    Hi David,

    Thanks for your help, anyway i get the same error..

    Server: Msg 2601, Level 14, State 3, Procedure sp_testerror, Line 8
    Cannot insert duplicate key row in object 'testerror' with unique index 'testerror1'.
    The statement has been terminated.
    Server: Msg 50000, Level 16, State 1, Procedure sp_testerror, Line 14
    test did not work

    I didn't actually wanted the other error messages, but only the message
    'test did not work'

    Thank you!!
    Yog

    "DaveSatz" <davidsatz@yahoo.com> wrote:
    >
    >create proc sp_testerror @newname varchar(20)
    >as
    >
    > DECLARE @Message varchar(255)
    >
    > BEGIN TRANSACTION
    >
    > insert testerror (LoginName) values (@newname)
    >
    > IF @@error <> 0
    > BEGIN
    > Set @Message = @newname + ' ' + 'did not work'
    > ROLLBACK TRAN
    > RAISERROR ( @Message, 16, 1)
    > RETURN
    > END
    > ELSE
    > BEGIN
    > Set @Message = @newname + ' ' + 'did work'
    > PRINT @Message -- not want to raise an error if it worked
    > END
    >
    >
    > COMMIT TRANSACTION
    >go
    >
    >
    >
    >--
    >HTH,
    >David Satz
    >Principal Software Engineer
    >Hyperion Solutions
    >->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
    >(Please reply to group only - emails answered rarely)
    >-----------------------------------------------------------------
    >"Yog" <yogaraj@usa.net> wrote in message news:3ac997bb$1@news.devx.com...
    >>
    >> Look at the following script
    >>
    >> ********************** sp_testerror *********************
    >>
    >> SET NOCOUNT ON
    >>
    >> if (exists (select * from sysobjects
    >> where name = 'testerror' and type = 'U '))
    >> drop table testerror
    >> go
    >>
    >> create table testerror (LoginName varchar(20) null)
    >> go
    >>
    >>
    >> insert testerror (LoginName) values ('xavier')
    >> go
    >>
    >>
    >> create unique nonclustered index testerror1 on testerror (loginname)
    >>
    >> go
    >>
    >> drop proc sp_testerror
    >> go
    >>
    >> create proc sp_testerror @newname varchar(20)
    >> as
    >>
    >> DECLARE @Message varchar(255)
    >>
    >> BEGIN TRANSACTION
    >>
    >> insert testerror (LoginName) values (@newname)
    >>
    >> IF (@@error != 0)
    >> BEGIN
    >> Select @Message = 'did not work'
    >> Select Message = (@newname + ' ' + @Message)
    >> ROLLBACK TRAN
    >> RETURN
    >> END
    >> ELSE
    >> BEGIN
    >>
    >> Select @Message = 'did work'
    >> Select Message = (@newname + ' ' + @Message)
    >> END
    >>
    >>
    >> COMMIT TRANSACTION
    >> go
    >>
    >>
    >>
    >> NOW, EXECUTE THIS,
    >> EXEC sp_testerror 'test'
    >>
    >> When i try to insert a duplicate , i should get a message like
    >> 'did not work test' instead of the sql error message?
    >> What should i change in this proc to do this ?
    >>
    >> Yog
    >>

    >
    >



  4. #4
    DaveSatz Guest

    Re: Returning stored proc values

    there is no way I know of to avoid that

    "Yog" <yogaraj@usa.net> wrote in message news:3acb4064$1@news.devx.com...
    >
    > Hi David,
    >
    > Thanks for your help, anyway i get the same error..
    >
    > Server: Msg 2601, Level 14, State 3, Procedure sp_testerror, Line 8
    > Cannot insert duplicate key row in object 'testerror' with unique index

    'testerror1'.
    > The statement has been terminated.
    > Server: Msg 50000, Level 16, State 1, Procedure sp_testerror, Line 14
    > test did not work
    >
    > I didn't actually wanted the other error messages, but only the message
    > 'test did not work'
    >
    > Thank you!!
    > Yog
    >
    > "DaveSatz" <davidsatz@yahoo.com> wrote:
    > >
    > >create proc sp_testerror @newname varchar(20)
    > >as
    > >
    > > DECLARE @Message varchar(255)
    > >
    > > BEGIN TRANSACTION
    > >
    > > insert testerror (LoginName) values (@newname)
    > >
    > > IF @@error <> 0
    > > BEGIN
    > > Set @Message = @newname + ' ' + 'did not work'
    > > ROLLBACK TRAN
    > > RAISERROR ( @Message, 16, 1)
    > > RETURN
    > > END
    > > ELSE
    > > BEGIN
    > > Set @Message = @newname + ' ' + 'did work'
    > > PRINT @Message -- not want to raise an error if it worked
    > > END
    > >
    > >
    > > COMMIT TRANSACTION
    > >go
    > >
    > >
    > >
    > >--
    > >HTH,
    > >David Satz
    > >Principal Software Engineer
    > >Hyperion Solutions
    > >->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
    > >(Please reply to group only - emails answered rarely)
    > >-----------------------------------------------------------------
    > >"Yog" <yogaraj@usa.net> wrote in message news:3ac997bb$1@news.devx.com...
    > >>
    > >> Look at the following script
    > >>
    > >> ********************** sp_testerror *********************
    > >>
    > >> SET NOCOUNT ON
    > >>
    > >> if (exists (select * from sysobjects
    > >> where name = 'testerror' and type = 'U '))
    > >> drop table testerror
    > >> go
    > >>
    > >> create table testerror (LoginName varchar(20) null)
    > >> go
    > >>
    > >>
    > >> insert testerror (LoginName) values ('xavier')
    > >> go
    > >>
    > >>
    > >> create unique nonclustered index testerror1 on testerror (loginname)
    > >>
    > >> go
    > >>
    > >> drop proc sp_testerror
    > >> go
    > >>
    > >> create proc sp_testerror @newname varchar(20)
    > >> as
    > >>
    > >> DECLARE @Message varchar(255)
    > >>
    > >> BEGIN TRANSACTION
    > >>
    > >> insert testerror (LoginName) values (@newname)
    > >>
    > >> IF (@@error != 0)
    > >> BEGIN
    > >> Select @Message = 'did not work'
    > >> Select Message = (@newname + ' ' + @Message)
    > >> ROLLBACK TRAN
    > >> RETURN
    > >> END
    > >> ELSE
    > >> BEGIN
    > >>
    > >> Select @Message = 'did work'
    > >> Select Message = (@newname + ' ' + @Message)
    > >> END
    > >>
    > >>
    > >> COMMIT TRANSACTION
    > >> go
    > >>
    > >>
    > >>
    > >> NOW, EXECUTE THIS,
    > >> EXEC sp_testerror 'test'
    > >>
    > >> When i try to insert a duplicate , i should get a message like
    > >> 'did not work test' instead of the sql error message?
    > >> What should i change in this proc to do this ?
    > >>
    > >> Yog
    > >>

    > >
    > >

    >




  5. #5
    Yog Guest

    Re: Returning stored proc values


    Hi David,
    Thank you - i understand . In that case, is there any way to handle multiple
    error messages from a Coldfusion frontend. The code is
    (ie am executing the stored proc from Coldfusion)

    <CFTRY>
    <cfquery name="AddRecord" datasource="cpdp">
    sp_testerror 'test'
    </cfquery>
    <CFCATCH TYPE="database">
    <CFSET ErrorMessage = CFCATCH.message>
    <cfset URLString="../test/TestError.cfm?Status=#ErrorMessage#">
    <CFLOCATION URL="#URLString#">
    </CFCATCH>
    </CFTRY>

    Thank you
    Yog

    "DaveSatz" <davidsatz@yahoo.com> wrote:
    >there is no way I know of to avoid that
    >
    >"Yog" <yogaraj@usa.net> wrote in message news:3acb4064$1@news.devx.com...
    >>
    >> Hi David,
    >>
    >> Thanks for your help, anyway i get the same error..
    >>
    >> Server: Msg 2601, Level 14, State 3, Procedure sp_testerror, Line 8
    >> Cannot insert duplicate key row in object 'testerror' with unique index

    >'testerror1'.
    >> The statement has been terminated.
    >> Server: Msg 50000, Level 16, State 1, Procedure sp_testerror, Line 14
    >> test did not work
    >>
    >> I didn't actually wanted the other error messages, but only the message
    >> 'test did not work'
    >>
    >> Thank you!!
    >> Yog
    >>
    >> "DaveSatz" <davidsatz@yahoo.com> wrote:
    >> >
    >> >create proc sp_testerror @newname varchar(20)
    >> >as
    >> >
    >> > DECLARE @Message varchar(255)
    >> >
    >> > BEGIN TRANSACTION
    >> >
    >> > insert testerror (LoginName) values (@newname)
    >> >
    >> > IF @@error <> 0
    >> > BEGIN
    >> > Set @Message = @newname + ' ' + 'did not work'
    >> > ROLLBACK TRAN
    >> > RAISERROR ( @Message, 16, 1)
    >> > RETURN
    >> > END
    >> > ELSE
    >> > BEGIN
    >> > Set @Message = @newname + ' ' + 'did work'
    >> > PRINT @Message -- not want to raise an error if it worked
    >> > END
    >> >
    >> >
    >> > COMMIT TRANSACTION
    >> >go
    >> >
    >> >
    >> >
    >> >--
    >> >HTH,
    >> >David Satz
    >> >Principal Software Engineer
    >> >Hyperion Solutions
    >> >->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
    >> >(Please reply to group only - emails answered rarely)
    >> >-----------------------------------------------------------------
    >> >"Yog" <yogaraj@usa.net> wrote in message news:3ac997bb$1@news.devx.com...
    >> >>
    >> >> Look at the following script
    >> >>
    >> >> ********************** sp_testerror *********************
    >> >>
    >> >> SET NOCOUNT ON
    >> >>
    >> >> if (exists (select * from sysobjects
    >> >> where name = 'testerror' and type = 'U '))
    >> >> drop table testerror
    >> >> go
    >> >>
    >> >> create table testerror (LoginName varchar(20) null)
    >> >> go
    >> >>
    >> >>
    >> >> insert testerror (LoginName) values ('xavier')
    >> >> go
    >> >>
    >> >>
    >> >> create unique nonclustered index testerror1 on testerror (loginname)
    >> >>
    >> >> go
    >> >>
    >> >> drop proc sp_testerror
    >> >> go
    >> >>
    >> >> create proc sp_testerror @newname varchar(20)
    >> >> as
    >> >>
    >> >> DECLARE @Message varchar(255)
    >> >>
    >> >> BEGIN TRANSACTION
    >> >>
    >> >> insert testerror (LoginName) values (@newname)
    >> >>
    >> >> IF (@@error != 0)
    >> >> BEGIN
    >> >> Select @Message = 'did not work'
    >> >> Select Message = (@newname + ' ' + @Message)
    >> >> ROLLBACK TRAN
    >> >> RETURN
    >> >> END
    >> >> ELSE
    >> >> BEGIN
    >> >>
    >> >> Select @Message = 'did work'
    >> >> Select Message = (@newname + ' ' + @Message)
    >> >> END
    >> >>
    >> >>
    >> >> COMMIT TRANSACTION
    >> >> go
    >> >>
    >> >>
    >> >>
    >> >> NOW, EXECUTE THIS,
    >> >> EXEC sp_testerror 'test'
    >> >>
    >> >> When i try to insert a duplicate , i should get a message like
    >> >> 'did not work test' instead of the sql error message?
    >> >> What should i change in this proc to do this ?
    >> >>
    >> >> Yog
    >> >>
    >> >
    >> >

    >>

    >
    >



  6. #6
    DaveSatz Guest

    Re: Returning stored proc values

    CF only returns the first error message - at least using ODBC - OLEDB may be
    different - I did not try. Post this on the Allaire forums.

    Dave

    "Yog" <yogaraj@usa.net> wrote in message news:3acc870e$1@news.devx.com...
    >
    > Hi David,
    > Thank you - i understand . In that case, is there any way to handle

    multiple
    > error messages from a Coldfusion frontend. The code is
    > (ie am executing the stored proc from Coldfusion)
    >
    > <CFTRY>
    > <cfquery name="AddRecord" datasource="cpdp">
    > sp_testerror 'test'
    > </cfquery>
    > <CFCATCH TYPE="database">
    > <CFSET ErrorMessage = CFCATCH.message>
    > <cfset URLString="../test/TestError.cfm?Status=#ErrorMessage#">
    > <CFLOCATION URL="#URLString#">
    > </CFCATCH>
    > </CFTRY>
    >
    > Thank you
    > Yog
    >
    > "DaveSatz" <davidsatz@yahoo.com> wrote:
    > >there is no way I know of to avoid that
    > >
    > >"Yog" <yogaraj@usa.net> wrote in message news:3acb4064$1@news.devx.com...
    > >>
    > >> Hi David,
    > >>
    > >> Thanks for your help, anyway i get the same error..
    > >>
    > >> Server: Msg 2601, Level 14, State 3, Procedure sp_testerror, Line 8
    > >> Cannot insert duplicate key row in object 'testerror' with unique index

    > >'testerror1'.
    > >> The statement has been terminated.
    > >> Server: Msg 50000, Level 16, State 1, Procedure sp_testerror, Line 14
    > >> test did not work
    > >>
    > >> I didn't actually wanted the other error messages, but only the message
    > >> 'test did not work'
    > >>
    > >> Thank you!!
    > >> Yog
    > >>
    > >> "DaveSatz" <davidsatz@yahoo.com> wrote:
    > >> >
    > >> >create proc sp_testerror @newname varchar(20)
    > >> >as
    > >> >
    > >> > DECLARE @Message varchar(255)
    > >> >
    > >> > BEGIN TRANSACTION
    > >> >
    > >> > insert testerror (LoginName) values (@newname)
    > >> >
    > >> > IF @@error <> 0
    > >> > BEGIN
    > >> > Set @Message = @newname + ' ' + 'did not work'
    > >> > ROLLBACK TRAN
    > >> > RAISERROR ( @Message, 16, 1)
    > >> > RETURN
    > >> > END
    > >> > ELSE
    > >> > BEGIN
    > >> > Set @Message = @newname + ' ' + 'did work'
    > >> > PRINT @Message -- not want to raise an error if it worked
    > >> > END
    > >> >
    > >> >
    > >> > COMMIT TRANSACTION
    > >> >go
    > >> >
    > >> >
    > >> >
    > >> >--
    > >> >HTH,
    > >> >David Satz
    > >> >Principal Software Engineer
    > >> >Hyperion Solutions
    > >> >->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
    > >> >(Please reply to group only - emails answered rarely)
    > >> >-----------------------------------------------------------------
    > >> >"Yog" <yogaraj@usa.net> wrote in message

    news:3ac997bb$1@news.devx.com...
    > >> >>
    > >> >> Look at the following script
    > >> >>
    > >> >> ********************** sp_testerror *********************
    > >> >>
    > >> >> SET NOCOUNT ON
    > >> >>
    > >> >> if (exists (select * from sysobjects
    > >> >> where name = 'testerror' and type = 'U '))
    > >> >> drop table testerror
    > >> >> go
    > >> >>
    > >> >> create table testerror (LoginName varchar(20) null)
    > >> >> go
    > >> >>
    > >> >>
    > >> >> insert testerror (LoginName) values ('xavier')
    > >> >> go
    > >> >>
    > >> >>
    > >> >> create unique nonclustered index testerror1 on testerror

    (loginname)
    > >> >>
    > >> >> go
    > >> >>
    > >> >> drop proc sp_testerror
    > >> >> go
    > >> >>
    > >> >> create proc sp_testerror @newname varchar(20)
    > >> >> as
    > >> >>
    > >> >> DECLARE @Message varchar(255)
    > >> >>
    > >> >> BEGIN TRANSACTION
    > >> >>
    > >> >> insert testerror (LoginName) values (@newname)
    > >> >>
    > >> >> IF (@@error != 0)
    > >> >> BEGIN
    > >> >> Select @Message = 'did not work'
    > >> >> Select Message = (@newname + ' ' + @Message)
    > >> >> ROLLBACK TRAN
    > >> >> RETURN
    > >> >> END
    > >> >> ELSE
    > >> >> BEGIN
    > >> >>
    > >> >> Select @Message = 'did work'
    > >> >> Select Message = (@newname + ' ' + @Message)
    > >> >> END
    > >> >>
    > >> >>
    > >> >> COMMIT TRANSACTION
    > >> >> go
    > >> >>
    > >> >>
    > >> >>
    > >> >> NOW, EXECUTE THIS,
    > >> >> EXEC sp_testerror 'test'
    > >> >>
    > >> >> When i try to insert a duplicate , i should get a message like
    > >> >> 'did not work test' instead of the sql error message?
    > >> >> What should i change in this proc to do this ?
    > >> >>
    > >> >> Yog
    > >> >>
    > >> >
    > >> >
    > >>

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center