STORE PROCEDURES

[ijitnoin]

Can you call Store Produre within a SQL query? I create a procedure for the unknown parameters. I did not include every column in the previous query where the unknow was needed. I only referenced the column/fields neccessary to create the stored procedure as follows:

CREATE PROCEDURE [dbo].[MFG]
@Entered_MFG CHAR(5),
@EnterBeginning_InvoiceDate datetime,
@EnterEnding_InvoiceDate datetime
AS
SELECT qryBankMFGInvRmk.[CTRLNO], qryBatchMFGInvoiceSum.MFG,qryBatchMFGInvoiceSum.MinOfInvoiceDate AS InvoiceDate, qryBankMFGInvRmk.RemarkID
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;


GO

 

[SQLSister]

Yes you can call a stored procedure from another stored procedure or as part of a batch of queries in QA. However, you cannot reference it directly in a select statment. What you do is insert the result of the stored procedure into a temp table and then refernce the temp table inthe select.

Create table #temp (field1 varchar (10), field2 varchar (20))

insert into #temp
exec usp_my_Stored_Procedure

select field3, field 4, field2 from table1 join #temp on table1.id_field = #temp.field1

Make sure that you set up the temp table with the exact smae fields in the same order as the select returned as a result of the stored procedure.

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