Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2008-10-07
2
Medium Priority
?
171 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

618 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