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
LVL 2
Rajeshk_cgmAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
then, check out my suggested modifications.
it will be exactly what you need to do (output parameter)
0
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.