?
Solved

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

Posted on 2008-10-07
2
Medium Priority
?
170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

771 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