Using stored procedure to search in Visual Basic

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using stored procedure to search in Visual Basic

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-22 : 09:54:23

nathan writes "How would I do a search using stored procedure in visual basic platform, here's my example:

================================================================

CREATE PROCEDURE ps_Customers_SELECT_NameCityCountry
@Cus_Name varchar(30) = NULL,
@Cus_City varchar(30) = NULL,
@Cus_Country varchar(30) =NULL
AS

Declare @condition INT

SELECT Cus_Name,
Cus_City,
Cus_Country
FROM Customers
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country) AND

if @condition = 1
Customers.Insertion_Date >= GETDATE()
ELSE IF @Condition = 2
Customers.suburb LIKE 'Artarmon'
ElSE IF @Contition = 3
Customers.country LIKE 'Australia'
================================================================

What I want is that if @condition = 1 (eg) it appends
Customers.insertion_Date >= GETDAte() to the Where Clause as:

.
.
WHERE Cus_Name = COALESCE(@Cus_Name,Cus_Name) AND
Cus_City = COALESCE(@Cus_City,Cus_City) AND
Cus_Country = COALESCE(@Cus_Country,Cus_Country) AND
Customers.Insertion_Date >= GETDATE()

Now from the line "if @condition = 1" it generates an error. I was wondering if you can help me to overcome that.

However, I do have other solutions to bypass this error, though it's not an efficient method;

"IF" solution
----------------------------------------------------------------
If @condition = 1

select .......... from .... where......

If @condition =2

select ..............from ...where....

If @condition =3

select............from...where....
----------------------------------------------------------------

similar to CASE solution

This method is not efficient in a sense that it's hard to update when you want to change something in stored procedure.

I was wondering is it possible to have the "if" statement after the WHERE clause

Thank you"

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-22 : 16:07:05

Search this site for Dynamic SQL - it seems to be the only solution for you.

helena

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-02-22 : 16:45:44

I suggest the solution that you had
if 1
select from where
if 2
select from where...

The reason is I think that SQL will be able to execute this much faster than true dynamic SQL because of the precompiled stuff that a stored proc does.

Michael

hdn
Starting Member

1 Post

Posted - 2002-02-24 : 22:20:03

Hi Michael,

As you can see, the select statement I showed you as an example is only 1/10 of the actual code I'm working on. If that's the solution you advised me, then it's way to cumbersome. Nevertheless, I'll probably use Dynamic SQL instead, but thank you so much anyway.

Nathan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources