EXCUTING A PROCEDURES

[ijitnoin]

The procedure is as follows:

CREATE PROCEDURE [dbo].[MFG]
@Entered_MFG CHAR(5),
@EnterBeginning_InvoiceDate datetime,
@EnterEnding_InvoiceDate datetime

AS

Set nocount on

SELECT qryBankMFGInvRmk.[CTRLNO], qryBatchMFGInvoiceSum.MFG,qryBatchMFGInvoiceSum.MinOfInvoiceDate AS InvoiceDate, qryBankMFGInvRmk.RemarkID,qryBankMFGInvRmk.RemarkCode, qryBankMFGInvRmk.Position, qryBankMFGInvRmk.[CHECK], tblManufacturerInfo.MFGNAME, qryBankMFGInvRmk.FACE_INVCD AS [Pay Face], qryBankMFGInvRmk.FEE_INVCD AS [Pay Fee], qryBankMFGInvRmk.POST_INVCD AS [Pay Post], qryBankMFGInvRmk.CCHFEE_INVCD AS [Pay CCH], qryBankMFGInvRmk.INV_TOTAL AS [Pay Total], CASE WHEN [PARTIAL] = '' THEN 'T' ELSE 'F' END AS P, qryBatchMFGInvoiceSum.FACE_INVCD AS FACEVALUE, qryBatchMFGInvoiceSum.CCHFEE_INVCD AS CCHVALUE, qryBatchMFGInvoiceSum.FEE_INVCD AS FEEVALUE, qryBatchMFGInvoiceSum.POST_INVCD AS POSTVALUE, qryBatchMFGInvoiceSum.INV_TOTAL AS TOTALVALUE, qryBatchMFGInvoiceSum.FACE_INVCD-qryBankMFGInvRmk.FACE_INVCD AS FACEDIFF, qryBatchMFGInvoiceSum.POST_INVCD-qryBankMFGInvRmk.POST_INVCD AS POSTDIFF, qryBatchMFGInvoiceSum.FEE_INVCD-qryBankMFGInvRmk.FEE_INVCD AS FEEDIFF, qryBatchMFGInvoiceSum.CCHFEE_INVCD-qryBankMFGInvRmk.CCHFEE_INVCD AS CCHDIFF
FROM (tblManufacturerInfo INNER JOIN qryBatchMFGInvoiceSum ON tblManufacturerInfo.MFG = qryBatchMFGInvoiceSum.MFG) INNER JOIN (qryBankMFGInvRmk INNER JOIN qryBankSummary_ACCT ON (qryBankMFGInvRmk.[CTRLNO] = qryBankSummary_ACCT.[CTRLNO]) AND (qryBankMFGInvRmk.BankID = qryBankSummary_ACCT.BankID)) ON (qryBatchMFGInvoiceSum.MFG = qryBankMFGInvRmk.MFG) AND (qryBatchMFGInvoiceSum.INVOICE = qryBankMFGInvRmk.INVOICE)
WHERE ((qryBatchMFGInvoiceSum.MFG=@Entered_MFG) AND (qryBatchMFGInvoiceSum.MinOfInvoiceDate>=@EnterBeginning_InvoiceDate) And (qryBatchMFGInvoiceSum.MinOfInvoiceDate<= @EnterEnding_InvoiceDate))
ORDER BY dbo.qryBankMFGInvRmk.[CTRLNO], dbo.qryBankMFGInvRmk.[INVOICE], dbo.qryBankMFGInvRmk.RemarkID;

set nocount off

EXEC MFG
GO

What is wrong since no records are received? Also, I want to be able to enter those unknown values.

 

[bborissov]

You not pass any parameter to that Procedure

Borislav Borissov

 

[SQLBill]

You didn't enter any values. Where are the values for these variables:
@Entered_MFG CHAR(5),
@EnterBeginning_InvoiceDate datetime,
@EnterEnding_InvoiceDate datetime

Your EXEC statement should be something like:

EXEC dbo.MFG 'abcd1', '2006-01-01 12:00', '2006-02-01 12:00'

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

Y'know, watching upsized & generated Access query without TGML ain't fun...

CREATE PROCEDURE [dbo].[MFG]
	@Entered_MFG CHAR(5),
	@EnterBeginning_InvoiceDate datetime,
	@EnterEnding_InvoiceDate datetime
AS	
SET NOCOUNT ON

SELECT  MIR.[CTRLNO], 
	MIS.MFG,
	MIS.MinOfInvoiceDate AS InvoiceDate, 
	MIR.RemarkID,
	MIR.RemarkCode, 
	MIR.Position, 
	MIR.[CHECK], 
	MI.MFGNAME, 
	MIR.FACE_INVCD AS [Pay Face], 
	MIR.FEE_INVCD AS [Pay Fee], 
	MIR.POST_INVCD AS [Pay Post], 
	MIR.CCHFEE_INVCD AS [Pay CCH], 
	MIR.INV_TOTAL AS [Pay Total], 
	CASE WHEN [PARTIAL] = '' THEN 'T' ELSE 'F' END AS P, 
	MIS.FACE_INVCD AS FACEVALUE, 
	MIS.CCHFEE_INVCD AS CCHVALUE, 
	MIS.FEE_INVCD AS FEEVALUE, 
	MIS.POST_INVCD AS POSTVALUE, 
	MIS.INV_TOTAL AS TOTALVALUE, 
	MIS.FACE_INVCD-MIR.FACE_INVCD AS FACEDIFF, 
	MIS.POST_INVCD-MIR.POST_INVCD AS POSTDIFF, 
	MIS.FEE_INVCD-MIR.FEE_INVCD AS FEEDIFF, 
	MIS.CCHFEE_INVCD-MIR.CCHFEE_INVCD AS CCHDIFF
FROM tblManufacturerInfo MI 
INNER JOIN qryBatchMFGInvoiceSum MIS ON MI.MFG = MIS.MFG 
INNER JOIN qryBankMFGInvRmk IR ON (MIS.MFG = MIR.MFG) AND (MIS.INVOICE = MIR.INVOICE)
INNER JOIN qryBankSummary_ACCT ACCT ON (MIR.[CTRLNO] = ACCT.[CTRLNO]) AND (MIR.BankID = ACCT.BankID)
WHERE MIS.MFG=@Entered_MFG 
	AND (MIS.MinOfInvoiceDate>=@EnterBeginning_InvoiceDate)  AND (MIS.MinOfInvoiceDate<= @EnterEnding_InvoiceDate)
ORDER BY MIR.[CTRLNO], 
	MIR.[INVOICE], 
	MIR.RemarkID
	
GO

And wouldn't EXEC MFG without arguments cause runtime error?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Also the exec is within the proc (before the GO statement)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ijitnoin]

In other words, there is no way a user will be a to select records for a criteria of the field that may change each time the procedures is executed.

In Access, the criteria of a field is based on the value entered by the user, not pre-determined This could be = or >= ect.

Thanks for your effort.

 

[vongrunt]

And qryBankSummary_ACT is not used in query. Maybe it is necessary (to filter rows), maybe not... let's see what happens when sproc is supplied with arguments first.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLSister]

Of course the user can supply the values that is what the input values inthe procedure aref or. But the user interface has to have some way for the user to specify those values and then create the exec statement for the proc. This is a user interface issue not a stored prodcedure issue if the proc returns the correct values in Query analyzer when you specify the paramenters you want.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[ijitnoin]

There must be another way the argument/parameter can be pass to a procedure at the time of execution rather than predetermined since the argument/parameter can change each time it is executed.

For some reason, I was thinking the user would be prompt for the argument rather than giving an error message stating that the parameter was missing.

I made the changes you suggested. If there is a way in SQL the parameter can be enter and passed during the process, let me know.

Thanks for your help.

 

[gmmastros]

There must be another way the argument/parameter can be pass to a procedure at the time of execution rather than predetermined since the argument/parameter can change each time it is executed.

Well, actually, there a MANY different ways that the parameters can be passed in.

1. You could write some VB Code, with a form where you prompt the user for the input parameters, then call the SP with the parameters.
2. You could write some C++ Code, with a form where you prompt the user for the input parameters, then call the SP with the parameters.
3. You could write some C# Code, with a form where you prompt the user for the input parameters, then call the SP with the parameters.
4. You could write some ASP Code, with a form where you prompt the user for the input parameters, then call the SP with the parameters.

Etc...

With SQL Server, if you want prompts or any other type of 'visual' stuff, you need to write it in another language.

SQL Server is ONLY responsible for data. You send it parameter values, it returns data. That's it.
Pretty front end data collection, presentation, etc, happens at the front end. Not in the database.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom