Solved

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

Posted on 2008-10-07
2
167 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 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

728 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