is it possible to have variant condition clause in procedure?

i want to use OLEDB to build a COM for my app

in the case, i want to execute a select statement which the where-clause is variant.

ex,

select * from db1 where code='abc'

select * from db1 where name='mike'

As it's very difficult to change sql-command in oledb, i want to build a procedure like this,

create procedure viewDB
@.filter CHAR(20)

as

select * from db1 where @.filter

go

but failed!

i tried EXEC(select), but i cant get the variants when building a oledb consumer

No, you cannot pass the whole WHERE clause as a parameter. You could pass only values. In your case if this is predefined set of the types of conditions, then you could create additional parameter in your SP and pass type of the condition there. then, inside of SP, first check type of the query using IF statement and based on it call specific SQL

IF @.MyTYPE='A'

select * from db1 where code=@.filter

ELSE

select * from db1 where name=@.filter

|||

oh, yeah! why i didnt come up with this smart idea, haha

but i found a better solution, bind the columns manually!

|||What is that? Is it constracting SQL statement dinamically inside of SP?|||

no, it's not that!

is easy!

build a SP like this:

create procedure test
@.p char(40)
as
exec( 'select * from testdb where ' + @.p )
go

when executing this sp in sql-server, we can get the columns correctly! but, when using the ole-wizard to build a oledb consumer , the columns disappear. the fact is, the columns lay there steadily, it's ole-wizard didn't bind the columns for us, haha

so let's DIY

|||

But this is worst way to do. It is a pure SQL injection. If I pass next string in your parameter then it will be executed with the different result

Assuming I am passing next value in a parameter

1=1; SHUTDOWN --

Then it will execute your SELECT and then it will shutdown server completely. I could execute DELETE statement or something else. This is how hakers could get control of your server

|||

oh, thanks for telling me that!

but, what if i remove the string after the semi-colon?

my plan is to create a procedure, and use a ATL oledb consumer to access it, if i dont expose the SP name, i think the hackers wont hack me this way

|||They do not need to know your SP name. All the nee to do is to pass value like that to the parameter from your application and job is done. For example, if your screen accepts input for the parameter from outside then screen will accept this value and code will be executed. Another drawback of the dynamic SQL is that it is slower. It means your SP will be recompiled each time when you call it and new execution plan will be prepared.|||

really thanks this piece of infomation!

so, 'select * from table where col=@.p' is safe right?

ok, i will try to re-code my SP

|||

i fond it is almost impossible to code my SP like you suggestted, because my condition clause is so complicate.

i figured out this new plan, and i want to get some advice from you, thx

create procedure proc
@.p1 varchar(10),
@.p2 varchar(10),
...
@.pn varchar(10)

as

if @.p1 is not null
begin
select * into retTable from table where col1= @.p1
--select * into tmpTable from table where col1= @.p1
end
if @.p2 is not null
begin
if object_id('tmpTable) is not null
drop table tmpTable
select * into tmpTable from retTable where col2=@.p1
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

if @.p3 is not null
begin
if object_id('tmpTable') is not null
drop table tmpTable
select * into tmpTable from retTable where col3=@.p3
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

...
...

go

BUT, i wonder if it's efficiency !

could you give me some suggestion or a better solution, thx

|||

I see that code for the second and third IF statements is the same. Does it mean that it suppose to be something like below? It is not an actual code that could work, but shows an idea. If idea is correct then you could pass array of values as one parameter into stored procedure using XML string and then use it inside of the IN clause. If this is what you need, then I will post a code that shows how to pass arrfay of values into SP and how to use it there

if @.p2 is not null
begin
if object_id('tmpTable) is not null
drop table tmpTable
select * into tmpTable from retTable where col2 IN (@.p1, @.p3)
if object_id('retTable') is not null
drop table retTable
select * into retTable from tmpTable
end

|||

well, it should be 'col3' in the 3rd IF statement

so, u mean my idea wont work actually right?

what do u mean pass a parameter using XML string? how can i parse it in the SP?

|||

Here is my article about how touse XML to pass array of values into SP.

http://support.microsoft.com/kb/555266/en-us

I will try to think about ideas how to do this in your case and will let you know

|||

now i have a problem about injection attack!

could you tell me if the following code safe.

procedure sp_a
@.p CHAR(40)
as
select * from tab1 where col1=@.p
go

|||Yes, it is safe if you do not do anything else inside if this SP. Just small suggestion. If you can, select just the fields you need and avoid using *. It will impove performance