October 6, 2015 at 1:25 pm
/*-------------------------Drop temp tables-------------------------------------------*/
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
CREATE TABLE #test (TestID CHAR(5) NOT NULL PRIMARY KEY)
INSERT INTO #test
SELECT '1'
UNION ALL
SELECT '2'
UNION ALL
SELECT '15'
UNION ALL
SELECT 'ab1'
UNION ALL
SELECT 'bc1'
UNION ALL
SELECT 'cd1'
UNION ALL
SELECT 'uv5'
SELECT *
FROM #test
WHERE (-- below should be dynamic assigned to a variable
testid = '1'
OR testid = '2'
OR testid = '15'
OR testid = 'ab1'
OR testid = 'bc1'
OR testid = 'cd1'
OR testid = 'uv5'
)
i am trying to build a dynamic where "or" clause finding difficulties any help would be appreciated
October 6, 2015 at 1:39 pm
Rather than building up a static or dynamic list of OR conditions in WHERE clause, instead load your search keywords into a temp table or table variable, and then join.
select *
from TestTable
join @SearchKeywords S
on S.keyword = TestTable.keyword;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 6, 2015 at 5:26 pm
sorry i put question wrong way i need as below. when user chooses a field and condition i need to build string dynamically
drop table #temp
create table #temp
(
Col1 char(10) --colname
,Col2 char(1) -- left
,Col3 char(1)--write
,Col4 VARCHAR(72) -- uid
,Col5 char(2) -- conditional operator
)
insert into #temp
select 'col1', '(',')','pqr','or'
union all select 'col2', '(',')','xyz','or'
union all select 'col3', '(',')','cef','or'
union all select 'col4', '(',')','pqr','or'
select * from #temp
O/p should be of where clause
where (col1 = 'pqr')
or (col2 = 'xyz')
or (col2 = 'cef')
or (col2 = 'pqr')
October 6, 2015 at 6:40 pm
Sounds like you may be talking about optional parameters...
DECLARE
@BusinessEntityID INT = NULL,
@FirstName VARCHAR(50) = 'John',
@LastName VARCHAR(50) = NULL;
SELECT
p.BusinessEntityID,
p.PersonType,
p.NameStyle,
p.Title,
p.FirstName,
p.MiddleName,
p.LastName
FROM
Person.Person p
WHERE
(p.BusinessEntityID = @BusinessEntityID OR @BusinessEntityID IS NULL)
AND (p.FirstName = @FirstName OR @FirstName IS NULL)
AND (p.LastName = @LastName OR @LastName IS NULL)
OPTION(RECOMPILE)
;
October 6, 2015 at 9:00 pm
thanks but optional parameters are choose by user we need to build where clause based on user selection for each report
October 6, 2015 at 9:28 pm
http://www.sqlservercentral.com/articles/Tally+Table/72993/
Pass the values in as a comma separated string, and join to or use where exists to the above referenced function.
FROM Table X
INNER JOIN DelimitedSplit8k('parameter values', ',') S ON X.field = S.Item
OR
FROM Table X
WHERE EXISTS (SELECT S.Item FROM DelimitedSplit8k('parameter values', ',') S WHERE S.Item = X.field
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 7, 2015 at 6:57 am
mxy (10/6/2015)
thanks but optional parameters are choose by user we need to build where clause based on user selection for each report
You haven't told us how you plan to have the users choose the parameters they want to use.
The code I provided in my last post, creates a parameter for each of the available options and the user fills in values for the ones they want to use and leave the rest null (not used).
October 7, 2015 at 7:27 am
This has the trademark appearance of a catch-all query. You need to read this article about this type of query so you understand some options for performance. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply