August 18, 2011 at 4:12 pm
I have a stored procedure that returns a recordset. Once and a while I would like to filter out records so in VB I construct a WHERE clause. I then want to send this pre-build WHERE clause back to my stored procedure, process it as a parameter and return the new, filtered recordset. I can do this by running a EXEC in VB at the client side but I am seeing if I can do it on the SQL server instead.
Here is the concept:
CREATE PROCEDURE YaddaYadda
@pWhereFilter VARCHAR(200) = NULL
AS
BEGIN
SELECT
X,
Y,
Z
FROM
tblA INNER JOIN
tblB ON tblA.X = tblB.XY
WHERE
(tblA.X = 0) @pWhereFilter
GROUP BY
X,
Y,
Z
END
If passed, @pWhereFilter would be something like: AND tblA.X = 1 AND tblA.Y = 'some string'
The WHERE clause always has the one filter but if I pass in the @pWhereFilter it will append to the existing and continue processing.
This seems to not be a valid operation. What is the correct way of doing something like this?
Thanks in advance.
AWS
August 18, 2011 at 4:21 pm
.... after a few more minutes on the topic I think I simply going to filter the ADO recordset. Seems to be the easiest and cleanest.
But I am curious about the above question still. I would like to know how this could be handled on SQL 2008 R2.
Thanks.
August 18, 2011 at 4:45 pm
Hi , try this link
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
August 18, 2011 at 5:05 pm
Thanks, that's what I was looking for.
AWS
August 20, 2011 at 11:41 am
You are just very lucky man that J.Celko is not your 'mon papa'. Otherwise you would be caned badly for behaving coding like that :hehe:
I'm lucky same as you 😀 And I feel pity for his descendants...
August 20, 2011 at 6:51 pm
Eugene Elutin (8/20/2011)
You are just very lucky man that J.Celko is not your 'mon papa'. Otherwise you would be caned badly for behaving coding like that :hehe:I'm lucky same as you 😀 And I feel pity for his descendants...
And I feel sorry for the customers of anyone who writes ridiculously open-to-injection stored procedures like the one Joe is complaining about here - you may not like his ton (I certainly don't) and you may not care about his beloved ISO standards (I don't care very much for them) but he does understand databases and SQL and general programming good practise, and on a point like this one you ignore him at your peril.
If you bother to look at it, you can see that Gail's blog entry referenced by Dave earlier in this topic doesn't use a character string that it incorporates into an sql string, but uses instead parametrised sql, precisely to avoid the problem that Joe pointed out.
Tom
August 21, 2011 at 3:06 am
Tom.Thomson (8/20/2011)
Eugene Elutin (8/20/2011)
You are just very lucky man that J.Celko is not your 'mon papa'. Otherwise you would be caned badly for behaving coding like that :hehe:I'm lucky same as you 😀 And I feel pity for his descendants...
And I feel sorry for the customers of anyone who writes ridiculously open-to-injection stored procedures like the one Joe is complaining about here - you may not like his ton (I certainly don't) and you may not care about his beloved ISO standards (I don't care very much for them) but he does understand databases and SQL and general programming good practise, and on a point like this one you ignore him at your peril.
If you bother to look at it, you can see that Gail's blog entry referenced by Dave earlier in this topic doesn't use a character string that it incorporates into an sql string, but uses instead parametrised sql, precisely to avoid the problem that Joe pointed out.
That is exactly it! The ton! ... and standards:-D
I've not criticised points he did make about opening query for sql-injection.
So, I'm happily reading Gail's and other blogs but never books advertised by, may be knowledgeable, but not very polite expert from Texas:hehe:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply