SQL Server Forums
Profile | Register | 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
 New Topic  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
52249 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
52249 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  
 New 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.11 seconds. Powered By: Snitz Forums 2000