Solved

Stored procedure

Posted on 2009-04-08
10
197 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

23 Experts available now in Live!

Get 1:1 Help Now