Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

stored procedure determines which query to run based on value sent from vb.net application

I have a stored procedure that I execute from my VB.NET application.  I want to have 2 query options in the sproc so it will return different values depending on the variable I send to the sproc.

In my code I have option 1 query and option 2 query.

How can I run only one query based on a value in a variable (I want either JobNumber or EstimateNumber.... Not Both) ?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Jason Hammond
-- Create date: 10/7/2008
-- Description:	Fills datagrid on salesmainpage when customer selected
-- =============================================
Alter PROCEDURE APP_FillDataGridonSalesMain
 
@ccustno varchar(50) = NULL,
@jobstatus int = NULL,
@jobvoid int = NULL
 
AS
BEGIN
	
 
	SET NOCOUNT ON;
 
select  
 
-- Option 1 query
 
EstimateNumber [Est Number]
,JobDescriptions [Description]
,convert(varchar(12),CreateDate,101) Created
,convert(varchar(12),DueDate,101) Due
,Priority Priority
, case 
when jobstatus = 1 then 'Open'
when jobstatus = 2 then 'Complete'
when jobstatus = 3 then 'Billed'
when jobstatus = 5 then 'Estimate'
end  as [Job Status]
 
from sales_jobentry
where Ccustno = isnull(@Ccustno,ccustno) 
and jobstatus = isnull(@jobstatus,jobstatus)
and jobvoid = isnull(@jobvoid,0)
 
 
-- Option 2 Query
 
 
 
 
select  
 
JobNumber  [Job Number]
,JobDescriptions [Description]
,convert(varchar(12),CreateDate,101) Created
,convert(varchar(12),DueDate,101) Due
,Priority Priority
, case 
when jobstatus = 1 then 'Open'
when jobstatus = 2 then 'Complete'
when jobstatus = 3 then 'Billed'
when jobstatus = 5 then 'Estimate'
end  as [Job Status]
 
from sales_jobentry
where Ccustno = isnull(@Ccustno,ccustno) 
and jobstatus = isnull(@jobstatus,jobstatus)
and jobvoid = isnull(@jobvoid,0)
 
 
 
 
 
END
GO

Open in new window

0
logoncom
Asked:
logoncom
1 Solution
 
chapmandewCommented:
If @Value = 1
BEGIn
run query 1 here
END
ELSE
BEGIN
run query 2 here.
END
0
 
logoncomAuthor Commented:
Great, Thanks
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now