Stored procedure with parameter

Hi Experts,

I have a sql query in my program.

Dim Sql As String = "Select B.[BomMstID] ,B.[BomID],B.[OrderDate],B.[CreatedDate],B.[NeedByDate],B.[NeedByDate],B.[JobNum],B.[JobDate],B.[JobDate],B.[PartID],P.ManufacturerPartNo as ManuPartNo,P.PartDescription as Description," & _
"P.LastOrderedCost,P.LastOrderedCost*B.QtyRequired  as Cost,P.ManuID,M.ManuName as Manufacturer,B.[QtyOrdered],B.[PriceCurrent],B.[PricePrevious],B.[SubAssemNum],B.[WearSparePartFlag],B.[PriceTotal],B.[PONum],B.[POCreatedDt]," & _
"B.[RFQNum],B.[RFQCreatedDt],B.[RFQExpResponseDt],B.[CustomerID],B.[RevisionLvl],B.[QtyRequired] as Quantity,B.[QtyReceived],B.[ExpectedDelDt] FROM BOM B " & _
 "left outer join Parts P  on B.PartID=P.PartID " & _
 "left outer join MANUFACTURER  M  on P.ManuID=M.manuID " & _
 "Where B.CustomerID=" & ddlCustomer.SelectedValue & " and B.JobNum = '" & ddlJob.SelectedValue & "' and B.BomID = " & ddlBOM.SelectedValue & " and B.RevisionLvl='" & Revision_Lvl & "'"

            If ddlPO.Text <> "---Select PO---" Then
                Sql = Sql & " and PONum= '" & ddlPO.SelectedValue & "'"
            End If

            If ddlRFQ.Text <> "---Select RFQ---" Then
                Sql = Sql & " and RFQNum= '" & ddlRFQ.SelectedValue & "'"
            End If

            Sql = Sql & "  order by ManuPartNo"

Right now I have written the query in the program. How can I write a stored procedure for this. I know how to write the stored procedure with parameter. But I do not know how to send the parameter depending on the selection. for example  If ddlRFQ.Text <> "---Select RFQ---" Then I need to add (Sql = Sql & " and RFQNum= '" & ddlRFQ.SelectedValue & "'") to the sql.

Thanks in advance
RadhaKrishnaKiJayaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
http://www.codeproject.com/Articles/15403/Calling-Stored-procedures-in-ADO-NET

WARNING: air code so let me know if you run into any errors with the stored procedure

CREATE PROCEDURE [ProcedureNameHere]
(
	--Fix these datatypes to match the schema...I'm guessing
	@CustomerID		INT,
	@JobNum			VARCHAR(50),
	@BomID			INT,
	@RevisionLvl	VARCHAR(50),
	@PONum			VARCHAR(50) = NULL,
	@RFQNum			VARCHAR(50) = NULL
)

SELECT B.[BomMstID],
	B.[BomID],
	B.[OrderDate],
	B.[CreatedDate],
	B.[NeedByDate],
	B.[NeedByDate],
	B.[JobNum],
	B.[JobDate],
	B.[JobDate],
	B.[PartID],
	P.ManufacturerPartNo as ManuPartNo,
	P.PartDescription as Description,
	P.LastOrderedCost,
	P.LastOrderedCost*B.QtyRequired  as Cost,
	P.ManuID,
	M.ManuName as Manufacturer,
	B.[QtyOrdered],
	B.[PriceCurrent],
	B.[PricePrevious],
	B.[SubAssemNum],
	B.[WearSparePartFlag],
	B.[PriceTotal],
	B.[PONum],
	B.[POCreatedDt],
	B.[RFQNum],
	B.[RFQCreatedDt],
	B.[RFQExpResponseDt],
	B.[CustomerID],
	B.[RevisionLvl],
	B.[QtyRequired] as Quantity,
	B.[QtyReceived],
	B.[ExpectedDelDt]
FROM BOM AS B
LEFT OUTER JOIN Parts AS P
	ON B.PartID = P.PartID
LEFT OUTER JOIN MANUFACTURER AS M
	ON P.ManuID = M.manuID
WHERE B.CustomerID = @CustomerID
	AND B.JobNum = @JobNum
	AND B.BomID = @BomID
	AND B.RevisionLvl = @RevisionLvl
	AND (@PONum IS NULL OR B.PONum = @PONum)
	AND (@RFQNum IS NULL OR B.RFQNum = @RFQNum)
ORDER BY P.ManufacturerPartNo

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just send the ddlPO.Text and ddlRFQ.Text as PO and RFQ parameters respectively and then you can make the validation inside the Stored Procedure.

DECLARE sSql NVARCHAR(MAX)


SET sSql = 'Select B.[BomMstID] ,B.[BomID],B.[OrderDate],B.[CreatedDate],B.[NeedByDate],B.[NeedByDate],B.[JobNum],B.[JobDate],B.[JobDate],B.[PartID],P.ManufacturerPartNo as ManuPartNo,P.PartDescription as Description,
			P.LastOrderedCost,P.LastOrderedCost*B.QtyRequired  as Cost,P.ManuID,M.ManuName as Manufacturer,B.[QtyOrdered],B.[PriceCurrent],B.[PricePrevious],B.[SubAssemNum],B.[WearSparePartFlag],B.[PriceTotal],B.[PONum],B.[POCreatedDt], 
			B.[RFQNum],B.[RFQCreatedDt],B.[RFQExpResponseDt],B.[CustomerID],B.[RevisionLvl],B.[QtyRequired] as Quantity,B.[QtyReceived],B.[ExpectedDelDt] 
			FROM BOM B 
				left outer join Parts P  on B.PartID=P.PartID 
				left outer join MANUFACTURER  M  on P.ManuID=M.manuID 
			WHERE B.CustomerID=' + sCustomer + ' and B.JobNum = ''' + sJob + ''' and B.BomID = ' + sBOM + ' and B.RevisionLvl=''' + Revision_Lvl + ''

If sPO <> '---Select PO---' 
	SET sSql = sSql + ' and PONum=''' + sPO + ''

If sRFQ <> '---Select RFQ---' 
	SET sSql = sSql + ' and RFQNum=''' + sRFQ + ''

SET sSql = sSql + '  order by ManuPartNo'

Open in new window

0
RadhaKrishnaKiJayaAuthor Commented:
Thank you very much for your help. It worked!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.