April 2, 2009 at 5:08 am
I've got a stored procedure which does a select on a table whose columns contain values of 0 or 1. Not all of the columns have to be selected.
col1 1
col2 0
col3 1
col4 1
What I need to is select the columns supplied where the value is also supplied ie: select * from table where col1=1 and col2=0 and col4=1
I know I can specify a parameter per column and use the coalesce function, but what if there are 10 columns. Or 20. Or 50. I don't want to be passing millions of parameters and have a "where" clause that's half a mile long. Dynamic sql is out, since I don't want our DBA to shoot me.
Does anyone know of a way to sent the sproc a "list" of columns and their values and have the sql evaluate them in the "where".
Sorry if that description makes no sense.
Cheers.
April 2, 2009 at 5:25 am
How about passing a single XML parameter (as string) with one tag as column name and second as it's values and import into a temporary table inside the procedure..Using that temporary table further processes can be done.....
I donno if it will work the way you want..just trying to give some idea....
April 2, 2009 at 5:25 am
First why do you want to do this.
You would need to use dynamic queries to do this. You can pass the columns and their values as a string in a single parameter. Ex: 'col1 = 7 and col2 = 5' as single parameter.
Of course you will have to make up the 'col1 = 7 and col2 = 5' string first. Another option could be to send the list as XML.
Keep in mind that this can lead to inefficient execution plans.
"Keep Trying"
April 2, 2009 at 5:37 am
Chirag (4/2/2009)
First why do you want to do this.You would need to use dynamic queries to do this. You can pass the columns and their values as a string in a single parameter. Ex: 'col1 = 7 and col2 = 5' as single parameter.
Of course you will have to make up the 'col1 = 7 and col2 = 5' string first. Another option could be to send the list as XML.
Keep in mind that this can lead to inefficient execution plans.
Sorry, not making myself clear
The reason I'm doing it is that it's what the user wants. I'm writing an aspx web based report and one of the requirements is that they can tick a number of boxes (1 per column) and have the relevant data returned. I don't need to use a dynamic query - in fact, I'm not allowed to. As I said, I can do it by supplying a load of parameters bit I'm looking for a better way.
How about passing a single XML parameter (as string) with one tag as column name and second as it's values and import into a temporary table inside the procedure..Using that temporary table further processes can be done.....
I donno if it will work the way you want..just trying to give some idea....
XML - never used it, so wouldn't really know where to start. As I say, I've got an aspx web page with a checkboxlist as the front end, so I'm thinking about passing 2 comma delimited params (one of column names, 1 of values). Still not sure how to go about getting this into the "where" clause, though.
[EDIT]
got something similar where I pass a comma separated list like this:
select * from table
case @search_type
when 'parent_company' then
case @vendor_names
when '(All)' then 1
else
patindex('%' + LTRIM(RTRIM(vpc.parent_company)) + '%',@vendor_names)
end
else
case @vendor_names
when '(All)' then 1
else
patindex('%' + LTRIM(RTRIM(vrd.vendor_name)) + '%',@vendor_names)
end
end > 0
which picks up values based on the list, but in this case I also want to select where colmn = specified value.
Cheers.
April 2, 2009 at 10:38 am
Why dont you construct the WHERE condition and pass this as a string to the stored procedure.
do you have any front-end application for this??
April 2, 2009 at 10:43 am
Maximum length of paramter is 128.
it looks like you are passing dynamic WHERE condition and you are going to HIT boundary on the length. So why dont you break you conditions and pass it smalll chunks like first 5 where conditions in 1st parameter and next 5 where conditions in 2nd parameter and so on.
April 2, 2009 at 1:02 pm
David,
This is an interesting challenge. I'd like to understand it better, so have two questions.
So you can see how I'm looking at it, code that I've used for optional parameters looks like this:
select*
fromTable_1
where (@select_1 is null or col_1 = @select_1)
and (@select_2 is null or col_2 = @select_2)
and (@select_3 is null or col_3 = @select_3)
and (@select_4 is null or col_4 = @select_4)
1. Do you want to have variable columns returned (your original post says "Not all of the columns have to be selected"), or do you just want to support a variable number of chosen selection criteria?
2. What is the argument against a compound Where clause that lists all the selection columns correlated to their tick-box parameters as in my example?
3. I'm curious about how you would have coded this with a coalesce function for each criterion. I would think that you'd want to avoid a function call of any sort in your where clause.
April 2, 2009 at 1:07 pm
David,
What about this:
SELECT *
FROM mytab
WHERE col1 = ISNULL(@col1, col1)
AND col2 = ISNULL(@col2, col2)
AND col3 = ISNULL(@col3, col3)
AND col4 = ISNULL(@col4, col4)
AND col5 = ISNULL(@col5, col5)
It's a little less than one million lines.
Greets
Flo
April 2, 2009 at 1:09 pm
Vijaya Kadiyala (4/2/2009)
Maximum length of paramter is 128.it looks like you are passing dynamic WHERE condition and you are going to HIT boundary on the length. So why dont you break you conditions and pass it smalll chunks like first 5 where conditions in 1st parameter and next 5 where conditions in 2nd parameter and so on.
Hi Vijaya
Building WHERE conditions dynamically in front end is always risky due to SQL injection in my opinion.
Greets
Flo
April 2, 2009 at 1:29 pm
Florian Reischl (4/2/2009)
Vijaya Kadiyala (4/2/2009)
Maximum length of paramter is 128.it looks like you are passing dynamic WHERE condition and you are going to HIT boundary on the length. So why dont you break you conditions and pass it smalll chunks like first 5 where conditions in 1st parameter and next 5 where conditions in 2nd parameter and so on.
Hi Vijaya
Building WHERE conditions dynamically in front end is always risky due to SQL injection in my opinion.
Greets
Flo
Not if you use QuoteName(@Variable,""), this is how we "Solved" the number of parameters. I had to code a "QuoteString" though, that works with varchar(max) not only 128 characters.
That can be interesting to do this?
Cheers,
J-F
April 2, 2009 at 2:02 pm
Hi J-F!
As first: Maybe I'm currently don't get it, so sorry if I'm just now start writing nonsense :-), but...
You are absolutely right, QUOTENAME is a nice feature to quote dynamic values. But it will not work for dynamic lists of parameters. As I understood the discussion was to build lists of parameters in a VARCHAR and use it in a dynamic SQL approach like this:
CREATE TABLE #t (id INT, txt VARCHAR(100))
INSERT INTO #t
SELECT 1, 'hello'
DECLARE @sql NVARCHAR(MAX)
SET @sql = QUOTENAME('id = 1 OR id = 2', '''')
EXECUTE ('SELECT * FROM #t WHERE ' + @sql)
Maybe I'm wrong but I think that's not possible.
Greets
Flo
April 2, 2009 at 6:16 pm
All things are possible if one only understands, Flo....
Now David, this is going to very likely produce a table scan execution plan, unless you have indexes on all of the columns you are testing. Talk to your DBA about the load this will put on the system before you roll it out. This approach puts a burden on YOU as well, to keep straight where in your "bitmap" string, the values for each column will go. But it is most definitely NOT dynamic SQL.
-- @options is a string to be built by the user interface and passed as a single parameter
declare @options varchar(max)
-- faking values that might be passed from the calling application
--set @options = '1___' -- column 1 must be 1, don't care about any other columns
--set @options = '___1' -- column 4 must be 1, don't care about any other columns
set @options = '11_0' -- columns 1 and 3 must be 1, column 4 must be zero, 2... who cares?
Select @options as [@options]
;with
tbl1 (Name, col1,col2,col3,col4) as
(select 'Andy',0,0,0,0 union all
select 'Ben',0,0,0,1 union all
select 'Casey',0,0,1,0 union all
select 'Denise',0,0,1,1 union all
select 'Edgar',0,1,0,0 union all
select 'Fran',0,1,0,1 union all
select 'Garth',0,1,1,0 union all
select 'Harry',0,1,1,1 union all
select 'Inigo',1,0,0,0 union all
select 'Jasper',1,0,0,1 union all
select 'Kaspar',1,0,1,0 union all
select 'Lewis',1,0,1,1 union all
select 'Matt',1,1,0,0
)
select *,@options as [@options] from tbl1
where col1 like substring(@options,1,1)
and col2 like substring(@options,2,1)
and col3 like substring(@options,3,1)
and col4 like substring(@options,4,1)
Edited to add: I can even think of a way to do this with values other than 1s and 0s, but I don't understand that you are asking for that.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 3, 2009 at 1:13 am
Hi
You could do this in a number of ways but the query plans would be inefficient most of the times.
Dynamic sql may be a better solution to this. Check out this link.
http://www.sommarskog.se/dyn-search-2005.html
"Keep Trying"
April 3, 2009 at 8:23 am
Florian Reischl (4/2/2009)
Hi J-F!As first: Maybe I'm currently don't get it, so sorry if I'm just now start writing nonsense :-), but...
You are absolutely right, QUOTENAME is a nice feature to quote dynamic values. But it will not work for dynamic lists of parameters. As I understood the discussion was to build lists of parameters in a VARCHAR and use it in a dynamic SQL approach like this:
CREATE TABLE #t (id INT, txt VARCHAR(100))
INSERT INTO #t
SELECT 1, 'hello'
DECLARE @sql NVARCHAR(MAX)
SET @sql = QUOTENAME('id = 1 OR id = 2', '''')
EXECUTE ('SELECT * FROM #t WHERE ' + @sql)
Maybe I'm wrong but I think that's not possible.
Greets
Flo
You are right in that scenario Florian, I've double checked what I did, and it was for a BCP solution, so this is why i used QuoteName, or well, QuoteString, in that solution, to double my quotes for the second procedure. Here's the sample:
DROP PROC BcpProc , selectT
GO
CREATE PROC BcpProc
@Where VARCHAR(MAX)
AS
BEGIN
--
CREATE TABLE #t (
id INT,
txt VARCHAR(100))
INSERT INTO #t
SELECT 1,
'Hello,'
UNION ALL
SELECT 2,
'Mary'
UNION ALL
SELECT 3,
'Lou'
PRINT @Where
SET @Where = (SELECT Enterprise.dbo.QuoteString(@Where))
PRINT @Where
DECLARE @Worked BIT
DECLARE @SQLToBCP VARCHAR(8000)
SET @SQLToBCP = 'bcp "Exec selectT '
+ @Where
+ '" queryout '
+ 'C:\Test.txt'
+ ' -c -t"\t" -T -S'
+ @@SERVERNAME
EXEC @Worked = MASTER..xp_cmdshell
@SQLToBCP
DROP TABLE #t
END
GO
CREATE PROC selectT
@Where AS VARCHAR(MAX)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = ' Select * from #t where '
+ @Where
EXECUTE( @sql)
END
GO
EXEC BcpProc
'txt = ''Mary'' or txt = ''Hello'''
You see I needed to keep the double quotes around the data so I could pass it to another procedure for querying the data, and still being able to query from the Cmd line.
My bad, I did not use that for a long time, it got me saying stupid stuff again!
Thanks for the feedback,
Cheers,
J-F
April 3, 2009 at 8:31 am
Hi Flo
If you use QuoteName(@Variable,""), then you can avoid SQL Injection.
FYI: http://msdn.microsoft.com/en-us/library/ms161953.aspx
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply