需要获取SQL查询 [英] Need to get SQL query
问题描述
嗨朋友们,
我有一个SQL查询,在where子句中有4个参数。
Patient_No(=)
Last_Name(开头)
First_Name(开头)
DOB(=)
这些4的值由用户从UI提供。现在用户可能输入也可能不输入所有值。
如果用户输入Patient_No,它应该完全匹配(=)。
如果他输入姓氏,它应该搜索开始(如'pete%')。
如果他输入名字,它应该搜索开始(如'pete %')。
如果他输入DOB,它应该完全匹配。
如果用户输入所有值正确,查询工作完美。但如果他输入任何一个值,那么查询就不起作用。
这是我的疑问:
SELECT [Last_Name],[First_Name],[middle_name],[Patient_No],[gender],[Date_of_Birth]
FROM [受众特征]
其中
([Patient_No] = {MRN} 或 [Patient_No] = ' ')和
([Last_Name] 喜欢 {姓名} + ' %' 或 [Last_Name] = ' ')和
([First_Name] 喜欢 {名字} + ' %' 或 [First_Name] = ' ')< span class =code-keyword>和
([Date_of_Birth] = {日期 of 出生} 或 [Date_of_Birth] = ' ')
其中{}中的值是用户提供的。
让我只用其中一个表达式引起你对你的错误的注意,但都是一样的:
[Last_Name]如{Last Name} +'%'或 [ Last_Name] =''
[Last_Name]是字段名称,mot是参数。您应该使用此isntead:
[Last_Name],如{Last Name} +'%'或 {Last Name} =''
但要注意真正传递空字符串没有输入任何内容,而不是null或空格。
尝试
Decalre @ MRN varchar (< span class =code-digit> 50 ) - 参数
Decalre @ FName varchar ( 50 )< span class =code-comment> - 参数
Decalre @ LName varchar ( 50 ) - < span class =code-comment>参数
Decalre @ DOB varchar ( 50 ) - 参数
SELECT [Last_Name],[First_Name],[middle_name],[Patient_No],[gender] ,[Date_of_Birth]
FROM [受众特征]
其中 Patient_No =
案例
当 @ MRN <> ' ' 然后
@ MRN
其他
Patient_No
结束
和 First_Name 喜欢
案例
何时 @ FName <> ' ' 然后
@FName + ' %'
否则
' %'
结束
和 Last_Name 喜欢
案例
当 @LName<> ' ' 然后
@LName + ' %'
其他
' %'
结束
和 Date_of_Birth =
案例
当 @ DOB <> ' ' 然后
@DOB
其他
Date_of_Birth
结束
参考
https://msdn.microsoft.com/en-us/library/ms181765.aspx [ ^ ]
你遇到的主要问题是测试Column =''而不是Zoltan指向的变量out,但你也不会为空值而服务。
以下解决方案将满足任何列为null和/或任何变量为null
- 变量
DECLARE @ MRN VARCHAR ( 3 )
DECLARE @ FN varchar ( 30 )
DECLARE @ LN varchar ( 30 )
DECLARE @ DOB 日期
- 测试数据(更改)
SET @ MRN = ' '
SET @ FN = NULL
SET @ LN = ' Smith'
SET @ DOB = NULL
SELECT [Last_Name],[First_Name],[middle_name],[Patient_No],[性别],[Date_of_Birth]
FROM [受众特征]
其中
ISNULL([Patient_No],' ') LIKE COALESCE ( @ MRN + ' <温泉n class =code-string>%',Patient_No,' %')
AND
ISNULL([Last_Name],' ') LIKE COALESCE ( @LN + ' %',Last_Name,' %')
AND
ISNULL([ First_Name],' ') LIKE < span class =code-keyword> COALESCE ( @ FN + ' %',First_Name,' %')
AND
ISNULL (Date_of_Birth,' 14-OCT-1066')= COALESCE ( @ DOB ,Date_of_Birth,' 14-OCT-1066')
几个兴趣点:
- 我使用了14-OCT-1066处理无效的出生日期,主要是因为没有人可能有出生日期 - 您可能需要根据您的情况更改此日期。 (旁白 - 如果你感兴趣的话,这是黑斯廷斯战役的日期)
- 我用LIKE
对于Patient_No而不是=
来简化代码 - 即避免使用case语句。否则第一个条件就是ISNULL([Patient_No],'')= CASE WHEN @ MRN =''那么ISNULL(Patient_No,'')ELSE COALESCE(@ MRN,Patient_No,'')END
Hi Friends,
I have a SQL query that has 4 parameters in where clause.
Patient_No (=)
Last_Name (begin with)
First_Name (begin with)
DOB (=)
The values for these 4 are supplied by user from UI. Now user may or may not enter all the values.
SO if user enters Patient_No, it should match exactly (=).
If he enters Last name, it should search begin with (like 'pete%').
If he enters First name, it should search begin with (like 'pete%').
If he enters DOB, it should match exactly.
If user enters all the values right, the query works perfect. But if he enters any of only one value, then the query doesn't work.
Here is my query:
SELECT [Last_Name],[First_Name], [middle_name],[Patient_No],[gender],[Date_of_Birth]
FROM [Demographics]
where
([Patient_No] = {MRN} or [Patient_No] = '') and
( [Last_Name] like {Last Name}+'%' or [Last_Name] = '') and
( [First_Name] like {First Name}+'%' or [First_Name] = '') and
( [Date_of_Birth] = {Date of Birth} or [Date_of_Birth] = '' )
Where the values in { } are user provided.
Let me draw your attention on your mistake with just one of the expressions, but all are the same:
[Last_Name] like {Last Name}+'%' or [Last_Name] = ''
[Last_Name] is the field name, mot the parameter. You should use this isntead:
[Last_Name] like {Last Name}+'%' or {Last Name} = ''
But be aware to really pass empty string nothing is entered, and not null or whitespaces.
try
Decalre @MRN varchar(50) --parameter Decalre @FName varchar(50)--parameter Decalre @LName varchar(50)--parameter Decalre @DOB varchar(50)--parameter SELECT [Last_Name],[First_Name], [middle_name],[Patient_No],[gender],[Date_of_Birth] FROM [Demographics] where Patient_No= Case When @MRN <>'' Then @MRN Else Patient_No End And First_Name like Case When @FName <>'' Then @FName+'%' Else '%' End And Last_Name like Case When @LName<>'' Then @LName+'%' Else '%' End And Date_of_Birth= Case When @DOB <>'' Then @DOB Else Date_of_Birth End
Ref.
https://msdn.microsoft.com/en-us/library/ms181765.aspx[^]
The main problem you have is testing for the Column = '' rather than the variable as Zoltan has pointed out, but you're also not catering for null values.
The following solution will cater for any of the columns being null AND/OR any of the variables being null
-- Variables DECLARE @MRN VARCHAR(3) DECLARE @FN varchar(30) DECLARE @LN varchar(30) DECLARE @DOB Date -- Test data (changes) SET @MRN = '' SET @FN = NULL SET @LN = 'Smith' SET @DOB = NULL SELECT [Last_Name],[First_Name], [middle_name],[Patient_No],[gender],[Date_of_Birth] FROM [Demographics] where ISNULL([Patient_No],'') LIKE COALESCE(@MRN + '%',Patient_No,'%') AND ISNULL([Last_Name],'') LIKE COALESCE(@LN + '%', Last_Name, '%') AND ISNULL([First_Name],'') LIKE COALESCE(@FN + '%', First_Name, '%') AND ISNULL(Date_of_Birth,'14-OCT-1066') = COALESCE(@DOB, Date_of_Birth, '14-OCT-1066')
A couple of points of interest:
- I used 14-OCT-1066 for handling null dates of birth, largely because no-one is likely to have that date of birth - you might need to change this depending on your context. (Aside - it's the date of the Battle of Hastings if you're interested)
- I usedLIKE
for the Patient_No instead of=
to simplify the code - i.e. to avoid having to use a case statement. Otherwise that first condition would have beenISNULL([Patient_No],'') = CASE WHEN @MRN='' THEN ISNULL(Patient_No,'') ELSE COALESCE(@MRN,Patient_No,'') END
这篇关于需要获取SQL查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!