December 1, 2016 at 3:26 am
[PLEASE NOTE: I HAVE ALREADY POSTED THIS QUESTION IN THE SQL 2016 SECTION, HOWEVER TSQL HAS ROUGHLY 60 TIMES THE NUMBER OF HITS. APOLOGIES FOR THE ERROR.]
Hello all,
After searching for possible solutions, I haven't found anything that provides me with what I'm after. I'm fairly new to SQL, so apologies for the newbie question. I have been given the task of providing a search tool (via SSRS) within a dataset that is comprised of a number of fields. The search tool itself will be based on four parameters: identifier, forename, surname and Date. Please see the original search code below, which is too restrictive. What I need, is a search query that is based on ALL the combinations possible from the four search parameters.
How many possible combinations do you think there will be? Am I right in saying 15?
Is it possible to rewrite the code that encapsulates all these combinations in a concise way?
Any help would be greatly appreciated.
ORIGINAL QUERY:
SELECT Source_System, Identifier, Forename, Surname, Date etc....
FROM tblTable
WHERE
(Identifier = @identifier
OR Date = @date
OR Forename LIKE '%'+@for+'%'
OR Surname LIKE '%'+@sur+'%')
SAMPLE DATASET
USE [DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AAATempTable1](
[Identifier] [INT] NOT NULL,
[Source_System] [VARCHAR](30) NOT NULL,
[Date] [DATETIME] NOT NULL,
[Forename] [VARCHAR](30) NOT NULL,
[surname] [VARCHAR](30) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Identifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [DATABASE]
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (1111, N'A', CAST(N'1798-02-01T00:00:00.000' AS DateTime), N'Dummy', N'Test')
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (2222, N'A', CAST(N'1799-02-01T00:00:00.000' AS DateTime), N'Dummy1', N'Test1')
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (3333, N'A', CAST(N'1800-02-01T00:00:00.000' AS DateTime), N'Dummy2', N'Test2')
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (4444, N'B', CAST(N'1801-02-01T00:00:00.000' AS DateTime), N'Dummy', N'Test')
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (5555, N'B', CAST(N'1799-02-01T00:00:00.000' AS DateTime), N'Dummy3', N'Test3')
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (6666, N'B', CAST(N'1799-02-01T00:00:00.000' AS DateTime), N'Dummy4', N'Test4')
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (7777, N'C', CAST(N'1804-02-01T00:00:00.000' AS DateTime), N'Dummy', N'Test')
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (8888, N'C', CAST(N'1798-02-01T00:00:00.000' AS DateTime), N'Dummy5', N'Test5')
GO
INSERT [dbo].[AAATempTable1] ([Identifier], [Source_System], [Date], [Forename], [surname]) VALUES (9999, N'C', CAST(N'1798-02-01T00:00:00.000' AS DateTime), N'Dummy6', N'Test6')
GO
December 1, 2016 at 5:48 am
But if you are asking about a SQL Server 2016 instance in the 2008 forum, if people here are not running 2016, as many are not, you're not going to get good 2016 answers. The languages and options and capabilities have changed quite a bit from 2008.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply