Solved

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

Posted on 2008-10-07
2
163 Views
Last Modified: 2012-05-05
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
Comment
Question by:logoncom
2 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22661299
If @Value = 1
BEGIn
run query 1 here
END
ELSE
BEGIN
run query 2 here.
END
0
 

Author Closing Comment

by:logoncom
ID: 31503919
Great, Thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to enforce inte 8 58
sql Audit table 3 74
How do i Copy All the Tables from One Database to Another in SQL Server? 7 47
Query 14 57
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question