Solved

Stored procedure

Posted on 2009-04-08
10
196 Views
Last Modified: 2012-05-06
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
Comment
Question by:Rajeshk_cgm
  • 5
  • 4
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24095634
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24095635
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
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24095651
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
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24095656
the datatype should be varchar . because i need to reuse the @NewOrderID .
the value ll be like '08042009-7000-01-0002'
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24095702
*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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24095785
then, check out my suggested modifications.
it will be exactly what you need to do (output parameter)
0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24095884
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24095924
sorry...

ALTER PROCEDURE [dbo].[SPGetMaxOrderID]

@Date Varchar(50)

, @WaiterID INT

, @MaxOrderID VARCHAR(30) OUTPUT

Open in new window

0
 
LVL 2

Author Comment

by:Rajeshk_cgm
ID: 24095967
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24096071
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now