February 13, 2012 at 12:25 pm
I've written a SP (sorta...) to retrieve the PO ID I should charge for a given days' work. Here's the SP so far:
-- =============================================
-- Author:<Author,,James Shaffer>
-- Create date: <Create Date,,2/13/2012>
-- Description:<Description,,Returns exactly one PO ID for a given Date, ProjectID, and VendorID>
-- =============================================
CREATE PROCEDURE cpas.POIDVendorProjectDate
@VendorID int,
@ProjectID int,
@WorkDate datetime
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT ID FROM tblPO WHERE
VendorID = @VendorID
AND ExpirationDate >= @WorkDate
AND (ProjectID IS NULL OR ProjectID = @ProjectID)
AND CapitalExpense = (SELECT CAPEX FROM tblProjects WHERE ID=@ProjectID)
AND GroupCode in (1,3,5)
END
So, What I wnat the SP to do is return as follows (Pseudo Code):
Select Case @@RecordCount
Case 0
Return 0
Case 1
Return the POID selected
Case else
Return the negative of @@RecordCount
End Case
Can anyone help me finish this one?
Jim
February 13, 2012 at 12:30 pm
May I suggest an output parameter instead? The return code is supposed to be an indication of success or failure, not a data value.
CREATE PROCEDURE cpas.POIDVendorProjectDate
@VendorID int,
@ProjectID int,
@WorkDate datetime
@PO_ID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RowCount int;
-- Insert statements for procedure here
SELECT @PO_ID = ID FROM tblPO WHERE
VendorID = @VendorID
AND ExpirationDate >= @WorkDate
AND (ProjectID IS NULL OR ProjectID = @ProjectID)
AND CapitalExpense = (SELECT CAPEX FROM tblProjects WHERE ID=@ProjectID)
AND GroupCode in (1,3,5);
SET @RowCount = @@RowCount;
IF (@RowCount != 1)
SET @PO_ID = -1*@RowCount;
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 13, 2012 at 12:58 pm
Perfect. Thank you.
Here's the exact procedure (couple of typos corrected) that I ended up creating:
USE [xxxx]
GO
/****** Object: StoredProcedure [dbo].[cpas_POIDVendorProjectDate] Script Date: 02/13/2012 14:56:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,James Shaffer>
-- Create date: <Create Date,,2/13/2012>
-- Description:<Description,,Returns exactly one PO ID for a given Date, ProjectID, and VendorID>
-- =============================================
CREATE PROCEDURE [dbo].[cpas_POIDVendorProjectDate]
@VendorID int,
@ProjectID int,
@WorkDate datetime,
@PO_ID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RowCount int;
SELECT @PO_ID = ID FROM tblPO WHERE
VendorID = @VendorID
AND ExpirationDate >= @WorkDate
AND (ProjectID IS NULL OR ProjectID = @ProjectID)
AND CapitalExpense = (SELECT CapitalExpense FROM tblProjects WHERE ID=@ProjectID)
AND GroupCode in (1,3,5);
SET @RowCount = @@RowCount;
IF (@RowCount != 1)
SET @PO_ID = -1*@RowCount;
END
GO
Jim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply