• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Stored procedure

i have attached a stored procedure which i use in my project.


(i'm using this inside another stored procedure)
i need to get the value from
\\
	DECLARE @WaiterID int
	DECLARE @ServerDate datetime
	DECLARE @NewOrderID varchar(50)
 
	exec @NewMaxOrderID= dbo.SPGetMaxOrderID @ServerDate,@WaiterID
\\
 
how to get the value of @NewMaxOrderID

Open in new window

SP.txt
0
Rajeshk_cgm
Asked:
Rajeshk_cgm
  • 5
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change the procedure to this:
ALTER PROCEDURE [dbo].[SPGetMaxOrderID]
@Date Varchar(50)
, @WaiterID INT
, @MaxOrderID INT OUTPUT
AS
BEGIN
	DECLARE @OrderID Varchar(50)
	DECLARE @PerviousOrderID Int
	-- DECLARE @MaxOrderID Varchar(50) 
	SET @PerviousOrderID = 0
 
	SET @MaxOrderID = CASE WHEN DATEPART(DD, @Date) <= 9 THEN '0' + CAST(DATEPART(DD, @Date) AS VARCHAR) ELSE CAST(DATEPART(DD, @Date) AS VARCHAR) END + 
			   CASE WHEN DATEPART(MM, @Date) <= 9 THEN '0' + CAST(DATEPART(MM, @Date) AS VARCHAR) ELSE CAST(DATEPART(MM, @Date) AS VARCHAR) END + 
			  RIGHT (CAST(DATEPART(YYYY,@Date)  AS VARCHAR), 4) + '-' + CAST(@WaiterID AS VARCHAR)+ '-0'
 
	DECLARE GetOrderID CURSOR FOR
		SELECT DISTINCT OrderID FROM Sales_ProductDetails
		WHERE (OrderID LIKE CASE WHEN DATEPART(DD, @Date) <= 9 THEN '0' + CAST(DATEPART(DD, @Date) AS VARCHAR) ELSE CAST(DATEPART(DD, @Date) AS VARCHAR) END + 
			   CASE WHEN DATEPART(MM, @Date) <= 9 THEN '0' + CAST(DATEPART(MM, @Date) AS VARCHAR) ELSE CAST(DATEPART(MM, @Date) AS VARCHAR) END + 
			   RIGHT(CAST(DATEPART(YYYY, @Date)  AS VARCHAR), 4) + '-' + CAST(@WaiterID AS VARCHAR) + '-%')
	OPEN GetOrderID
	FETCH NEXT FROM GetOrderID INTO @OrderID
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		IF(@PerviousOrderID < dbo.FNGetSplitOrderID(@OrderID))
		BEGIN
			SET @PerviousOrderID = dbo.FNGetSplitOrderID(@OrderID)
			SET @MaxOrderID = @OrderID
		END
		FETCH NEXT FROM GetOrderID INTO @OrderID
	END
	CLOSE GetOrderID
	DEALLOCATE GetOrderID
 
	SELECT @MaxOrderID OrderID
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and your calling code to this:
        DECLARE @WaiterID int
        DECLARE @ServerDate datetime
        DECLARE @NewOrderID varchar(50)
 
        exec dbo.SPGetMaxOrderID @ServerDate,@WaiterID, @NewMaxOrderID OUTPUT

Open in new window

0
 
udaya kumar laligondlaTechnical LeadCommented:
in the SP you declare an out perameter and set a value for it in the SP.
read
http://msdn.microsoft.com/en-us/library/ms378108(SQL.90).aspx
sample of out parameter
CREATE PROCEDURE GetImmediateManager
   @employeeID INT,
   @managerID INT OUTPUT
AS
BEGIN
   SELECT @managerID = ManagerID
   FROM HumanResources.Employee
   WHERE EmployeeID = @employeeID
END
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Rajeshk_cgmAuthor Commented:
the datatype should be varchar . because i need to reuse the @NewOrderID .
the value ll be like '08042009-7000-01-0002'
0
 
Rajeshk_cgmAuthor Commented:
*sorry the above line should be as

the datatype should be varchar . because i need to reuse the @NewMaxOrderID.
the value ll be like '08042009-7000-01-0002'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, check out my suggested modifications.
it will be exactly what you need to do (output parameter)
0
 
Rajeshk_cgmAuthor Commented:
if i use your suggection then i'm getting conversion error

'Conversion failed when converting the varchar value '08042009-7000-0' to data type int.'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry...

ALTER PROCEDURE [dbo].[SPGetMaxOrderID]
@Date Varchar(50)
, @WaiterID INT
, @MaxOrderID VARCHAR(30) OUTPUT

Open in new window

0
 
Rajeshk_cgmAuthor Commented:
thank u angelIII... but after this i need to do another operation in the same procedure ...
the id may be returned as '08042009-7000-01-0002' or '08042009-7001-0'  

how to split this using the '-' & i need to take the value '01' or '0' from it?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't know what you mean?
please try to understand how the parameter passing works, as from there, you can continue to develop ....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now