?
Solved

Stored procedure

Posted on 2009-04-08
10
Medium Priority
?
204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 143

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 143

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:udaya kumar laligondla
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 750 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 143

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 143

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

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