Solved

Stored procedure

Posted on 2009-04-08
10
199 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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