December 16, 2010 at 6:23 am
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
December 16, 2010 at 8:26 am
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
December 16, 2010 at 8:28 am
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
December 16, 2010 at 8:39 am
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
December 16, 2010 at 8:43 am
SS,
Got any liny's to read about Profiler?
Jim
Jim
December 16, 2010 at 9:07 am
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