Advanced Transact_SQL

Hi everyone
I've a problem. I must convert a query dinamically composed in an ASP
script, in a stored procedure
The Visual Basic code is below.
Could you help me anyway?
SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
if(new_titolo <> "") then
mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
end if
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
if((da <> "" AND a <> "")) then
mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
end if
mySQL=trim(mySQL)
' tolgo eventuali AND finali
if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
AN") then
mySQL=mid(mySQL,1,len(mySQL)-3)
end if
mySQL=mySQL & " order by data DESC"
Thank you very much
Hi
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>
Check out http://www.sommarskog.se/dyn-search.html
John
|||in the following your string is not properly closed
if(new_sottotitolo <> "") then
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
end if
this should be
mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & "%' AND "
futher you are cheking whether the SQL string end with "AND" if true you're
removing it. but if all the three conditions fails "WHERE" clouse will throw
you an error. So try to remove the "WHERE" clouse in the "SELECT" statment is
no condition were met.
Otherwise you can include a dumy condition to avoid the error.
"SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE ID > 0 "
"elisacarli" wrote:

> Hi everyone
> I've a problem. I must convert a query dinamically composed in an ASP
> script, in a stored procedure
> The Visual Basic code is below.
> Could you help me anyway?
> SELECT ID,titolo,sottotitolo,data,file FROM tbl_promozioni WHERE
> if(new_titolo <> "") then
> mySQL=mySQL & "titolo LIKE '%" & new_sottotitolo & "%' AND "
> end if
> if(new_sottotitolo <> "") then
> mySQL=mySQL & "sottotitolo LIKE '%" & new_sottotitolo & AND "
> end if
> if((da <> "" AND a <> "")) then
> mySQL=mySQL & "(((data)>=#"& da & "#) AND ((data)<=#"& a & "#))"
> end if
> mySQL=trim(mySQL)
> ' tolgo eventuali AND finali
> if(mid(mySQL,len(mySQL)-3,3)="AND" or mid(mySQL,len(mySQL)-3,3)="
> AN") then
> mySQL=mid(mySQL,1,len(mySQL)-3)
> end if
> mySQL=mySQL & " order by data DESC"
> Thank you very much
>