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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SP: dynamically building Select Statement
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 07/21/2008 :  14:40:18  Show Profile  Visit Corobori's Homepage  Reply with Quote
I am rewriting an old application and moving a whole bunch of select queries to Stored Procedure.

In my original application I was building the SQL Statement dynamically prior of running it. I am trying to find out the best practice to do it in SQL Server.

Here is a pseudo-code of what I want:

@MyParamNum1 int = 0
@MyParamNum2 int = 0

Select col1, col2, col3 From tblMyTable
Where Status=1
if @MyParamNum1 <> 0 then
and col4=@MyParamNum1
end if
if @MyParamNum2 <> 0 then
and col5=@MyParamNum2
end if

jean-luc
www.corobori.com

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/21/2008 :  14:50:44  Show Profile  Reply with Quote
Select col1, col2, col3 From tblMyTable
Where Status=1
and (col4=@MyParamNum1 or @MyParamNum1=0)
and (col5=@MyParamNum2 or @MyParamNum2=0)
Go to Top of Page

boyfriendalex
Starting Member

15 Posts

Posted - 07/22/2008 :  02:07:39  Show Profile  Click to see boyfriendalex's MSN Messenger address  Reply with Quote
quote:
Originally posted by Corobori

I am rewriting an old application and moving a whole bunch of select queries to Stored Procedure.

In my original application I was building the SQL Statement dynamically prior of running it. I am trying to find out the best practice to do it in SQL Server.

Here is a pseudo-code of what I want:

@MyParamNum1 int = 0
@MyParamNum2 int = 0

Select col1, col2, col3 From tblMyTable
Where Status=1
if @MyParamNum1 <> 0 then
and col4=@MyParamNum1
end if
if @MyParamNum2 <> 0 then
and col5=@MyParamNum2
end if

jean-luc
www.corobori.com


can u explain more details about your problem?
so you need to build dynamical query with variables in procedure?
Go to Top of Page

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 07/22/2008 :  13:24:58  Show Profile  Visit Corobori's Homepage  Reply with Quote
quote:
Originally posted by boyfriendalex
can u explain more details about your problem?
so you need to build dynamical query with variables in procedure?



Yes. With optional variables

I also need to parse a string. Say my user are going to send as a search string "Toyota Corolla" I have to build the where statement either WHERE myTxtField like '%Toyota%' OR myTxtField like '%Corolla%' and the other option WHERE myTxtField like '%Toyota%' AND myTxtField like '%Corolla%'

Up to now it is being done in VB where I am preparint the complete SQL Statement but for security reason I want/need to do that in a SP


jean-luc
www.corobori.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/22/2008 :  14:33:50  Show Profile  Reply with Quote
quote:
Originally posted by Corobori

quote:
Originally posted by boyfriendalex
can u explain more details about your problem?
so you need to build dynamical query with variables in procedure?



Yes. With optional variables

I also need to parse a string. Say my user are going to send as a search string "Toyota Corolla" I have to build the where statement either WHERE myTxtField like '%Toyota%' OR myTxtField like '%Corolla%' and the other option WHERE myTxtField like '%Toyota%' AND myTxtField like '%Corolla%'

Up to now it is being done in VB where I am preparint the complete SQL Statement but for security reason I want/need to do that in a SP


jean-luc
www.corobori.com


will you be having another parameter to decide if you want AND/OR for search strings given?
Go to Top of Page

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 07/24/2008 :  10:23:25  Show Profile  Visit Corobori's Homepage  Reply with Quote
quote:
Originally posted by visakh16
will you be having another parameter to decide if you want AND/OR for search strings given?



Yes, they are about half a dozen of parameters involved in the where statement. Say 5 are simple ones, by simple I mean numerical Ids (mycategoryid=3) and the one left is a search in a string field where I can have any number of search strings where either all string or any string must are too be searched

jean-luc
www.corobori.com
Go to Top of Page

Corobori
Posting Yak Master

Chile
105 Posts

Posted - 07/25/2008 :  15:14:57  Show Profile  Visit Corobori's Homepage  Reply with Quote
I haved solved this issue. Thanks to all.

jean-luc
www.corobori.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000