By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Stored Procedure Sebquery as a Paramater

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

"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
Jul 23 '05 #2

P: n/a
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


Jul 23 '05 #3

P: n/a
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

Jul 23 '05 #4

P: n/a
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

Jul 23 '05 #5

P: n/a
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
Jul 23 '05 #6

P: n/a
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


Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.