New Stored Procedure

  • I know this is more complex than it has to be. I'm doing it this way to learn stored procedures and their usage. Here's my SP:

    USE [AssetLending]

    GO

    /****** Object: StoredProcedure [dbo].[sp_ReturnAsset] Script Date: 12/16/2010 08:13:54 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author:Jim Shaffer

    -- Create date: 15Dec2010

    -- Description:This procedure "Returns" an asset that has been "Checked Out"

    --

    -- =============================================

    ALTER PROCEDURE [dbo].[sp_ReturnAsset]

    @TransactionID int=0,

    @AssetID int=0,

    @ReturnDate datetime= getdate,

    @Comments varchar(50)=''

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    if @assetid=0

    BEGIN

    if @transactionid<>0

    UPDATE dbo.Transactions SET ReturnDate=@returndate WHERE TransID=@TransactionID

    END

    else

    UPDATE dbo.Transactions SET ReturnDate=@ReturnDate WHERE AssetID=@AssetID AND ReturnDate is null

    IF @@Error <> 0

    BEGIN

    DECLARE @StringVariable NVARCHAR(60)

    SET @StringVariable='sp_ReturnAsset error AssetID=' + cast(@assetid AS varchar(6))+ ' TransactionID=' + cast(@TransactionID AS varchar(6)) + ' Comments=' + @Comments

    RAISERROR 50001 @StringVariable

    END

    END

    And the execution:

    USE [AssetLending]

    GO

    DECLARE@return_value int

    EXEC@return_value = [dbo].[sp_ReturnAsset]

    @TransactionID = 2,

    @AssetID = NULL,

    @ReturnDate = NULL,

    @Comments = NULL

    SELECT'Return Value' = @return_value

    GO

    It returns a 0 and does not update any table.

    I'm also having trouble even calling it from Access VBA, but I'll wait until it works to address that issue....

    The table:

    USE [AssetLending]

    GO

    /****** Object: Table [dbo].[Transactions] Script Date: 12/16/2010 08:23:17 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[Transactions](

    [TransID] [int] IDENTITY(1,1) NOT NULL,

    [AssetID] [int] NOT NULL,

    [WorkerID] [int] NOT NULL,

    [TransDate] [datetime] NOT NULL,

    [ReturnDate] [datetime] NULL,

    [Notes] [varchar](150) NULL,

    CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED

    (

    [TransID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[Transactions] WITH CHECK ADD CONSTRAINT [FK_Transaction_Asset] FOREIGN KEY([AssetID])

    REFERENCES [dbo].[Asset] ([AssetID])

    GO

    ALTER TABLE [dbo].[Transactions] CHECK CONSTRAINT [FK_Transaction_Asset]

    Jim

  • Well, it doesn't work as you expected, but it probably works the way you coded it.

    Since you only gave us the code and not an explanation of the expected behaviour, I can't spot the difference.

    I would suggest you to "debug" the procedure tracing the path it takes with profiler.

    The "tuning" template should be suitable for this task.

    Are you familiar with Profiler?

    -- Gianluca Sartori

  • You could also add some PRINT commands to the IF/ELSE branches to see the path it took.

    Maybe this is easier than setting up a trace...

    -- Gianluca Sartori

  • Cool, you've given me two new tools I didn't know anything about. I'll look into 'em. I solved my problem. Turns out you can't set a datetime variable to GetDate() when you declare it. So, I set it to NULL by default, and then explicitly set it to GetDate() in the PROC. That solved the problem...

    Thank you

    USE [AssetLending]

    GO

    /****** Object: StoredProcedure [dbo].[sp_ReturnAsset] Script Date: 12/16/2010 08:13:54 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author:Jim Shaffer

    -- Create date: 15Dec2010

    -- Description:This procedure "Returns" an asset that has been "Checked Out"

    --

    -- =============================================

    ALTER PROCEDURE [dbo].[sp_ReturnAsset]

    @TransactionID int=0,

    @AssetID int=0,

    @ReturnDate datetime=NULL,

    @Comments varchar(50)=''

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    IF @ReturnDate IS NULL SET @ReturnDate=getdate()

    if @assetid=0

    BEGIN

    if @transactionid<>0

    UPDATE dbo.Transactions SET ReturnDate=@returndate WHERE TransID=@TransactionID

    END

    else

    UPDATE dbo.Transactions SET ReturnDate=@ReturnDate WHERE AssetID=@AssetID AND ReturnDate is null

    IF @@Error <> 0

    BEGIN

    DECLARE @StringVariable NVARCHAR(60)

    SET @StringVariable='sp_ReturnAsset error AssetID=' + cast(@assetid AS varchar(6))+ ' TransactionID=' + cast(@TransactionID AS varchar(6)) + ' Comments=' + @Comments

    RAISERROR 50001 @StringVariable

    END

    END

    Jim

  • SS,

    Got any liny's to read about Profiler?

    Jim

    Jim

  • Glad to see you solved your issue.

    I don't have any docs about Profiler at hand. You could start it and read the help, or google it.

    -- Gianluca Sartori

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply