Concatenating Rows to create WHERE clause.

Question

Post reply

Concatenating Rows to create WHERE clause.

DiegoPeña

Right there with Babe

Points: 749
More actions
November 27, 2012 at 8:49 am

#285446

Hi,
Im creating a dynamic where clause where the operators and conditions are stored in a table:
create table MyTable
(
columnName nvarchar(80),
Operator nvarchar(20),
Condition nvarchar(80)
)
INSERT INTO MyTable VALUES('columnA','LIKE','Audi')
INSERT INTO MyTable VALUES('columnA','NOT LIKE','GT')
INSERT INTO MyTable VALUES('columnB','<=','500')
I resolved this by creating some nested cursors (yeah I know, cursors... and nested :S ).
this the result:
'SELECT * FROM OtherTable WHERE columnA LIKE '%Audi%' AND ColumnA NOT LIKE '%GT%' OR columnB <= 500'
I don't want to use cursors so that is why Im posting this here.
Can anyone give me a hand on this please, I want to learn how to resolve this without using cursors.
Thank you.
PD: Sorry for my english, Im still learning.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323753 More actions · Answer 1

something like this?

i need an anchor for the outer query, so i used the columnName;

your real table might have a queryId or something, i would guess, if you are testing multipel columns in in the WHERE statement.

youll also have to un-escape the gt/lt stuff that got changed via xml.

/*

SELECT * FROM OtherTable WHERE 1 = 1 AND <= 500

SELECT * FROM OtherTable WHERE 1 = 1 AND LIKE %Audi% AND NOT LIKE %GT%

*/

create table MyTable

(

columnName nvarchar(80),

Operator nvarchar(20),

Condition nvarchar(80)

)

INSERT INTO MyTable VALUES('columnA','LIKE','Audi')

INSERT INTO MyTable VALUES('columnA','NOT LIKE','GT')

INSERT INTO MyTable VALUES('columnB','<=','500')

--'SELECT * FROM OtherTable WHERE columnA LIKE '%Audi%' AND ColumnA NOT LIKE '%GT%' OR columnB <= 500'

SELECT DISTINCT

' SELECT * FROM OtherTable WHERE 1 = 1 '

+ ( SELECT ' AND ' + columnName + ' '

+ CASE

WHEN Operator LIKE '%LIKE%'

THEN Operator + ' ' + '%' + Condition + '%' + ' '

ELSE Operator + ' ' + Condition + ' '

END

FROM MyTable T2

WHERE T1.columnName = T2.columnName

FOR XML PATH(''))

FROM MyTable T1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DiegoPeña Right there with Babe Points: 749 More actions · Answer 2

Thank you that was really fast.

I changed it to un-scape the gt/lt character.