Pass entire where sentence to a store procedure
-
Is it possible to pass entire where sentence to a store procedure?
From app, I'll generate a where sentence like below:
where orderID = '123' and orderCidy='London'
I created a store procedure like below but got an error said that
An expression of non-boolean type specified in a context where a condition is expected, near 'END'
CREATE PROCEDURE getorder
@mywhere VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
select * from order @mywhere
END
-
Yes, but it requires dynamic SQL which means you're at risk of SQL Injection attacks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
adonetok (9/8/2014)
Is it possible to pass entire where sentence to a store procedure?From app, I'll generate a where sentence like below:
where orderID = '123' and orderCidy='London'
I created a store procedure like below but got an error said that
An expression of non-boolean type specified in a context where a condition is expected, near 'END'
CREATE PROCEDURE getorder
@mywhere VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
select * from order @mywhere
END
Gail missed a great chance to toot her own horn. Take a look at the following article. The method Gail uses not only allows for the dynamic SQL that you're looking for but is also great for "catch all queries" (very similar in nature to your query) and it's all done without any chance of SQL Injection.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (9/8/2014)
adonetok (9/8/2014)
Is it possible to pass entire where sentence to a store procedure?From app, I'll generate a where sentence like below:
where orderID = '123' and orderCidy='London'
I created a store procedure like below but got an error said that
An expression of non-boolean type specified in a context where a condition is expected, near 'END'
CREATE PROCEDURE getorder
@mywhere VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
select * from order @mywhere
END
Gail missed a great chance to toot her own horn. Take a look at the following article. The method Gail uses not only allows for the dynamic SQL that you're looking for but is also great for "catch all queries" (very similar in nature to your query) and it's all done without any chance of SQL Injection.
Giving Jeff the assist (the link to the article):
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
As mentioned before, this can be an injection attack surface if not done properly, normally far more fail there than succeed. Look first at other alternatives like Gail's "catch all", if that's not what you need then please come back and we'll advice further.
😎
-
Matt Miller (#4) (9/8/2014)
Jeff Moden (9/8/2014)
adonetok (9/8/2014)
Is it possible to pass entire where sentence to a store procedure?From app, I'll generate a where sentence like below:
where orderID = '123' and orderCidy='London'
I created a store procedure like below but got an error said that
An expression of non-boolean type specified in a context where a condition is expected, near 'END'
CREATE PROCEDURE getorder
@mywhere VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
select * from order @mywhere
END
Gail missed a great chance to toot her own horn. Take a look at the following article. The method Gail uses not only allows for the dynamic SQL that you're looking for but is also great for "catch all queries" (very similar in nature to your query) and it's all done without any chance of SQL Injection.
Giving Jeff the assist (the link to the article):
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
Ah, thanks for the cover, Matt. I was posting in a hurry and flat left out the links. I appreciate it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sean Pearce (9/9/2014)
Without trying to sound anal, it is a Where Clause and not a Sentence.Makes no difference in terms of SQL injection.
😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply