Stored Procedure Sebquery as a Paramater

[WertmanTheMad]

Ok,

This sounds dangerous (and yes I know it is)
But its in a controlled enviroment and I really need to know how to do
this.

How can I pass a Subquery for an Exist or In clause as a paramater
Something like this

CREATE procedure dbo.mytry
@funk varchar(1000)
as

Select * from Customers where exists(
@funk
)

GO

So I would execute something like so

exec mytry @funk='Select ID From Customers where ID < 100'

Any Ideas, I have tried LOTS of things but I can actually get it to
work.

I need to use it conjunction with a 3rd party product that can only
select from a Stored Procedure, and I can only pass paramaters to the
SP.

Any suggestions ?

Thanks

Chris

[Simon Hayes]

"WertmanTheMad" <cw******@webchamps.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Ok,

This sounds dangerous (and yes I know it is)
But its in a controlled enviroment and I really need to know how to do
this.

How can I pass a Subquery for an Exist or In clause as a paramater
Something like this

CREATE procedure dbo.mytry
@funk varchar(1000)
as

Select * from Customers where exists(
@funk
)

GO

So I would execute something like so

exec mytry @funk='Select ID From Customers where ID < 100'

Any Ideas, I have tried LOTS of things but I can actually get it to
work.

I need to use it conjunction with a 3rd party product that can only
select from a Stored Procedure, and I can only pass paramaters to the
SP.

Any suggestions ?

Thanks

Chris

Erland's article on dynamic searching in TSQL might give you some ideas:

http://www.sommarskog.se/dyn-search.html

Simon

[WertmanTheMad]

Great article,

Unfortunatley unless I am missing something in it, it does not show how
to do what I need to do.

From what I saw he is building dynamic SQL From case and if statements, that wont work for me as there are too many possible queries to be
passed.

One time it might be Select * from Customers, another Select Sum(Sale)
as Total_sales from orders.

I wont know in advance what table or data, and UNFORTUNATLEY, it need
to be executed through an SP.

Im half thinking about CREATING the SP At run time.

Chris
Simon Hayes wrote: "WertmanTheMad" <cw******@webchamps.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Ok,

This sounds dangerous (and yes I know it is)
But its in a controlled enviroment and I really need to know how to do this.

How can I pass a Subquery for an Exist or In clause as a paramater
Something like this

CREATE procedure dbo.mytry
@funk varchar(1000)
as

Select * from Customers where exists(
@funk
)

GO

So I would execute something like so

exec mytry @funk='Select ID From Customers where ID < 100'

Any Ideas, I have tried LOTS of things but I can actually get it to
work.

I need to use it conjunction with a 3rd party product that can only
select from a Stored Procedure, and I can only pass paramaters to the SP.

Any suggestions ?

Thanks

Chris

Erland's article on dynamic searching in TSQL might give you some

ideas:
http://www.sommarskog.se/dyn-search.html

Simon

[Yury Jhol]

Try the following:

CREATE procedure dbo.mytry
@funk varchar(1000)
as
EXEC ('Select * from Northwind.dbo.Customers where exists('+@funk+')')

GO

Yury Jhol
"WertmanTheMad" <cw******@webchamps.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Ok,

This sounds dangerous (and yes I know it is)
But its in a controlled enviroment and I really need to know how to do
this.

How can I pass a Subquery for an Exist or In clause as a paramater
Something like this

CREATE procedure dbo.mytry
@funk varchar(1000)
as

Select * from Customers where exists(
@funk
)

GO

So I would execute something like so

exec mytry @funk='Select ID From Customers where ID < 100'

Any Ideas, I have tried LOTS of things but I can actually get it to
work.

I need to use it conjunction with a 3rd party product that can only
select from a Stored Procedure, and I can only pass paramaters to the
SP.

Any suggestions ?

Thanks

Chris

[WertmanTheMad]

Thanks,

I also got it in the other one, I just wasnt seeing it (In the
http://www.sommarskog.se/dyn-search.html arcticle that is)

Many thanks, this simple problem just opened me up to a whole new and
nearly unlimited way of using an already great product, (the 3rd part
one that is :)

CREATE procedure dbo.mytry
@funk nvarchar(4000)
as

print @funk

exec sp_executesql @sql = @funk

GO

[Erland Sommarskog]

WertmanTheMad (cw******@webchamps.com) writes:

I also got it in the other one, I just wasnt seeing it (In the
http://www.sommarskog.se/dyn-search.html arcticle that is)

Many thanks, this simple problem just opened me up to a whole new and
nearly unlimited way of using an already great product, (the 3rd part
one that is :)

CREATE procedure dbo.mytry
@funk nvarchar(4000)
as

print @funk

exec sp_executesql @sql = @funk

Now, if you instead read http://www.sommarskog.se/dynamic_sql.html,
you can see why this is a pretty useless stored procedure.

Yeah, I know that you said that your 3rd party product required you
to use stored procedure, but in that case you can call sp_executesql
directly. And then you can use parameters to it, so that you don't
expose yourself for SQL injection.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[WertmanTheMad]

Sort of

I could call it directly but a Line I omitted was a comment of the
actual fields I need returned.

While in design mode of the thrid party product it REQUIRES (as far as
I can tell) a valid dataset to be selected from the SP,

So if the param is blank I do something like
Select ID, FNAME, LNAME FROM CUSTOMERS WHERE SALE_AMT > 1000
Otherwise I exec the param

This way I can actually work with it in design mode.

I am actually parsing the SQL Out for injection and beyond that the
ONLY route to get it in there is behind the scenes.

The product ?
Reporting Services, As long as my query returns ID, FNAME, LNAME in it
I can pass ANY dataset to it I want from my Asp.net application.

I understand than in RS2005 this will be an available option (passing
it a custom select/dataset, but I need it now.

This works slick as can be.

And since my SQL is "Generated" from a Query Builder, the client at no
time has the ability to enter any ad-hoc sql to bang it up, the SQL is
checked for validity before it even get to RS

Thanks as Always

Chris
Erland Sommarskog wrote:

WertmanTheMad (cw******@webchamps.com) writes:

I also got it in the other one, I just wasnt seeing it (In the
http://www.sommarskog.se/dyn-search.html arcticle that is)

Many thanks, this simple problem just opened me up to a whole new and nearly unlimited way of using an already great product, (the 3rd part one that is :)

CREATE procedure dbo.mytry
@funk nvarchar(4000)
as

print @funk

exec sp_executesql @sql = @funk

Now, if you instead read http://www.sommarskog.se/dynamic_sql.html,
you can see why this is a pretty useless stored procedure.

Yeah, I know that you said that your 3rd party product required you
to use stored procedure, but in that case you can call sp_executesql
directly. And then you can use parameters to it, so that you don't
expose yourself for SQL injection.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp