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
 Transact-SQL (2000)
 SP: dynamically building Select Statement

Author  Topic 

Corobori
Posting Yak Master

105 Posts

Posted - 2008-07-21 : 14:40:18
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

52326 Posts

Posted - 2008-07-21 : 14:50:44
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 - 2008-07-22 : 02:07:39
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

105 Posts

Posted - 2008-07-22 : 13:24:58
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

52326 Posts

Posted - 2008-07-22 : 14:33:50
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

105 Posts

Posted - 2008-07-24 : 10:23:25
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

105 Posts

Posted - 2008-07-25 : 15:14:57
I haved solved this issue. Thanks to all.

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

- Advertisement -