Link to home
Start Free TrialLog in
Avatar of Altaf Patni
Altaf PatniFlag for India

asked on

passing parameter in sql procedure

hi
i am trying to create a stored procedure which have 3 different query
first query for insert data from one table to temp table,
second query can get min(invo_date) from table and
third query for getting Summarized data from table,

problem is when i am calling this stored procedure from vb code its says expected PrvsFromDate  parameter

please check stored procedure
ALTER PROCEDURE [dbo].[Get_InvoDtld]
	-- Add the parameters for the stored procedure here
	--@InputDate DATE,
	@StartDate DATE,
	@EndDate   DATE,
	@delvAT varchar(100),            --Input parameter
	@PrvsFromDate DATE OUTPUT,
	@PrvsTODate DATE	

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	
	INSERT INTO dbo.InvoDtld_TEMP SELECT * FROM dbo.InvoDtld WHERE CAST(FLOOR(CAST(Invo_Date AS FLOAT))AS DATETIME) BETWEEN @StartDate and @EndDate and CustoName = @delvAT
	
BEGIN
	
	SELECT	@PrvsFromDate = Min(M_Date) 
	FROM	InvoDtld 
	WHERE	CustoName = @delvAT


BEGIN
	
	SELECT		Sum(Invo_Amt),
			Sum(Comission), 
			sum(Balance), 
			Sum(Amt_Rcvd), 
			Sum(Amt_Paid),
			Sum(Invo_Amt) - Sum(Comission) - Sum(Amt_Rcvd) + Sum(Amt_Paid) AS Final_Balance
	FROM	InvoDtld 
	WHERE	CustoName = @delvAT AND 
			CAST(FLOOR(CAST(Invo_Date AS FLOAT))AS DATETIME) BETWEEN @PrvsFromDate AND @PrvsTODate

END		
END	
END

Open in new window

please help
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think Output parameter is not required. Try code below. In this case you will get 2 datasets returned. You cal still catch the first dataset and get min date value from the table InvoDtld.

ALTER PROCEDURE [dbo].[Get_InvoDtld]
	
	@StartDate DATE,
	@EndDate   DATE,
	@delvAT VARCHAR(100),
	@PrvsFromDate DATE,
	@PrvsTODate DATE

AS
BEGIN
	
	SET NOCOUNT ON;
	
	INSERT INTO dbo.InvoDtld_TEMP SELECT * FROM dbo.InvoDtld WHERE ( CAST(FLOOR(CAST(Invo_Date AS FLOAT))AS DATETIME) BETWEEN @StartDate and @EndDate ) and CustoName = @delvAT
	
	BEGIN
	
		SELECT	Min(M_Date) 
		FROM	InvoDtld 
		WHERE	CustoName = @delvAT		
	
		SELECT		
				Sum(Invo_Amt),
				Sum(Comission), 
				sum(Balance), 
				Sum(Amt_Rcvd), 
				Sum(Amt_Paid),
				Sum(Invo_Amt) - Sum(Comission) - Sum(Amt_Rcvd) + Sum(Amt_Paid) AS Final_Balance
		FROM	InvoDtld 
		WHERE	CustoName = @delvAT AND 
				CAST(FLOOR(CAST(Invo_Date AS FLOAT))AS DATETIME) BETWEEN @PrvsFromDate AND @PrvsTODate

	END	
END

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
crystal_Tech, a feedback will be appreciated.
Cheers.
crystal_Tech, if you want to mark my comment as Assisted Solution please mark the correct one (ID: 41837949).
Cheers
Avatar of Altaf Patni

ASKER

sorry point was for your 41837949 comment, but by mistake i select your other comment
and i dont know how to correct it now..  :-(
Click on the "Request Attention" button below the main question so a moderator can reopen the question so you can close it accordlying.
Thanks