用户输入名称的文本框，可以是名字或姓氏或两者，然后记录数据显示在屏幕上。存储过程逻辑失败 [英] Text box where user enters name,can be first name or last name or both then record data is displayed on to screen.Stored procedure logic fails

问题描述

有一个文本框，用户输入名称可以是名字，姓氏或两者。这一切都输入一个用户输入文本框。然后sql存储过程应该搜索名称（名字，姓名，姓氏）甚至可以是多个记录，用户可以从弹出窗口或简单表格中的重复名称中选择特定名称。我在sql server中没有太多技能。我在解决逻辑问题时遇到了问题。



到目前为止的代码如下 -

 创建 程序 [dbo]。[getfullnamereturnTest] 
  @ NameSearch   nvarchar （最大值
  AS  
 
 开始 
 
 如果 存在（ select  * < span class =code-keyword>来自 testingtable 其中 firstname = '  @ NameSearch'）
 开始 
 选择  distinct  testingtable.firstname，testingtable.las tname，testingtable.country 来自 testingtable  inner   join  
（
 选择 testingtable.firstname，COUNT（*） as  repitingnamesno 
 来自 testingtable 
 其中 testingtable.firstname = '  @ NameSearch' 
  group   by  testingtable.firstname 
   count（testingtable.firstname）> 1）
 subq  ON  testingtable.firstname = subq.firstname 
  end  
  else  如果 存在（ select  *  from  testingtable 其中​​ lastname = '  @ NameSearch'）
 开始 
 选择 < span class =code-keyword> distinct  testingtable.firstname，testingtable.lastname，testingtable.country 来自 testingtable 内部  join  
（
 选择 testingtable.lastname，COUNT （*） as  repitingnamesno 
  from  testingtable 
  where  testingtable.lastname = '  @ NameSearch' 
  group   by  testingtable.lastname 
  伯爵（t estingtable.lastname）> 1）
 subq  ON  testingtable.lastname = subq.lastname 
  end  
 
 其他 
 开始 
 选择  distinct  testingtable.firstname，testingtable.lastname，testingtable.country  from  testingtable  inner   join  
（
 选择 testingtable.firstname，testingtable.lastname，COUNT（*） as  repitingnamesno 
 来自 testingtable 
 其中​​ testingtable.firstname + testingtable.lastname = '  @ NameSearch' 
  group   b y  testingtable.firstname，testingtable.lastname 
   count（testingtable.firstname + testingtable.lastname）> 1）
 subq  ON  testingtable.firstname = subq.firstname  AND  testingtable.lastname = subq.lastname 
  end  
  end  
  go  

使用'reddy'搜索姓氏

  exec  getfullnamereturnTest @ NameSearch = '  reddy' 

存储过程返回空表。即使它在名称上包含多个记录。但是当我只是执行没有输入参数的查询，如下所示，它工作正常。

 如果 存在（选择 * 来自 testingtable 其中 firstname = '  reddy'）
 < span class =code-keyword> begin  
 选择  distinct  testingtable。 firstname，testingtable.lastname，testingtable.country 来自 testingtable  inner  加入 
（
 选择 testingtable.firstname，COUNT（*）为 repitingnamesno 
 来自 testingtable 
 其中 testingtable.firstname =   reddy' 
  group   b y  testingtable.firstname 
   count（testingtable.firstname）> 1）
 subq  ON  testingtable.firstname = subq.firstname 
  end  
  else  如果 存在（选择 * < span class =code-keyword>来自 testingtable 其中 lastname = '  reddy'）
 开始 
 选择  distinct  testingtable.firstname，testingtable.lastname，testingtable.country 来自 testingtable  inner   join  
（
 选择测试表。姓氏，COUNT（* ） as  repitingnamesno 
  from  testingtable 
 其中 testingtable.lastname = '  reddy' 
  group   by  testingtable.lastname 
   count（testingtable） .lastname）> 1）
 subq  ON  testingtable.lastname = subq.lastname 
  end  
 
  else  
  begin  
 < span class =code-keyword> select   distinct  testingtable.firstname，testingtable.lastname，testingtable.country 来自 testingtable 内部 加入 
（
 选择 testingtable.firstname，testingtable.lastname，COUNT（*） as  repitingnamesno 
 来自 testingtable 
 其中 testingtable.firstname + testingtable.lastname = '  reddy' 
  group   by  testingtable.firstname，testingtable.lastname 
   count（testingtable.firstname + testingtable.lastname）> 1）
 subq  ON  testingtable.firstname = subq.firstname  AND  testingtable.lastname = subq.lastname 
  end  

它返回如下输出：

 firstname lastname country 
 amulya Chdary Uk 
 amulya reddy BZ 

该表由foll组成欠价值。

名字姓氏国家
 sruthi reddy IND 
 sruthi chlka US 
 amulya Chdary Uk 
 sruthi K AUS 
 amulya reddy BZ 
 avasya Koneru US 
 sruthi reddy AUS 
 amulya reddy BZ 
 avasya Koneru US 
 avasya koneru AUS 

表数据

  CREATE   TABLE  [dbo]。[testingtable]（[firstname] [ nvarchar ]（ 50 ） NULL ，[lastname] [ nvarchar ]（ 50 ） NULL ，[country] [ nvarchar ]（max） NULL ）  ON  [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ] 
 
  GO  
 
  INSERT   INTO  testingtable（名字，姓氏，国家）
  VALUES （'   sruthi'，'  reddy'，'  IND'），
（'  sruthi'，'  chlka' ，'  US'），
（'  amulya'，' 雷迪'，'  BZ'），
（'  amulya'，'  Chdary' ，'  Uk'），
（'  avasya'，'  Koneru'，'  US'），
（'  avasya'，'  Koneru'，'  AUS'），
（'  amulya'，'  reddy'，'  BZ'），
（'  avasya'，'  Koneru'，'   US'），
（'  sruthi'，'  K'，'  AUS'），
（'  sruthi'，'  reddy'，'  AUS'）; 

解决方案

尝试使用以下代码：



使用以下查询更改StoredProc。

  SELECT   DISTINCT 名字，姓氏，国家
  FROM  testingtable 
  WHERE  firstname =  @ NameSearch   OR  lastname =  @ NameSearch  

但是我不确定您的查询中的group by的业务逻辑。如果你愿意，你可以合并这个查询。

从参数周围删除单引号。



而不是：

 其中 firstname = '  @ NameSearch' 

使用：

 其中 firstname = @ NameSearch 

前者正在寻找名字所在的记录等于文字字符串'@ NameSearch';后者正在查找第一个名称等于 @NameSearch 参数中的值的记录。



您还应该阅读 Falsehoods Programmers Believe about Names [ ^ ]。 ：）

have a text box where user enters name can be first name,last name or both.This all is entered in a single user input text box.Then the sql stored procedure should search for name(first,name,last name) can even be multiple records where the user can select a specific name from repetitive name in a popup or a simple table. I am don't have much skills in sql server.I am having problems getting the logic right.

The code till now is as follows-

create PROCEDURE [dbo].[getfullnamereturnTest]
@NameSearch nvarchar(max)
AS

begin

   if exists (select * from testingtable where firstname='@NameSearch') 
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.firstname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.firstname ='@NameSearch'
group by testingtable.firstname
having count(testingtable.firstname)>1)
subq ON testingtable.firstname = subq.firstname
end
else if exists (select * from testingtable where lastname='@NameSearch')
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.lastname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.lastname ='@NameSearch'
group by testingtable.lastname
having count (testingtable.lastname)>1 )
subq ON testingtable.lastname = subq.lastname
end

else
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.firstname,testingtable.lastname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.firstname+testingtable.lastname ='@NameSearch'
group by testingtable.firstname,testingtable.lastname
having count (testingtable.firstname+testingtable.lastname)>1 ) 
subq ON testingtable.firstname = subq.firstname AND testingtable.lastname = subq.lastname
end
end
go

Searching for last name with 'reddy'

exec getfullnamereturnTest @NameSearch='reddy'

The stored procedure returns empty table.Even when it consists of multiple records on name.But when i just executed the query without input parameter as below and it works fine.

 if exists (select * from testingtable where firstname='reddy') 
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.firstname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.firstname ='reddy'
group by testingtable.firstname
having count(testingtable.firstname)>1)
subq ON testingtable.firstname = subq.firstname
end
else if exists (select * from testingtable where lastname='reddy')
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.lastname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.lastname ='reddy'
group by testingtable.lastname
having count (testingtable.lastname)>1 )
subq ON testingtable.lastname = subq.lastname
end

else
begin
select distinct testingtable.firstname,testingtable.lastname,testingtable.country  from testingtable inner join
(
select testingtable.firstname,testingtable.lastname,COUNT(*) as repitingnamesno
from testingtable
where testingtable.firstname+testingtable.lastname ='reddy'
group by testingtable.firstname,testingtable.lastname
having count (testingtable.firstname+testingtable.lastname)>1 ) 
subq ON testingtable.firstname = subq.firstname AND testingtable.lastname = subq.lastname
end

It returns output as below:

firstname	  lastname	country
    amulya	  Chdary	Uk
    amulya	  reddy	        BZ

The table consists of the following values.

firstname	lastname	country
sruthi       	 reddy	         IND
sruthi	        chlka	        US
amulya	        Chdary	        Uk
sruthi	        K	        AUS
amulya	        reddy	        BZ
avasya          Koneru	        US
sruthi	        reddy	      AUS
amulya	        reddy	        BZ
avasya	        Koneru	        US
avasya	        koneru	        AUS

Table data

CREATE TABLE [dbo].[testingtable](  [firstname] [nvarchar](50) NULL,    [lastname] [nvarchar](50) NULL,     [country] [nvarchar](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO testingtable (firstname, lastname, country)
   VALUES ('sruthi','reddy' , 'IND'),
          ('sruthi','chlka' , 'US'),
          ('amulya', 'reddy', 'BZ'),
          ('amulya', 'Chdary', 'Uk'),
          ('avasya', 'Koneru', 'US'),
           ('avasya', 'Koneru', 'AUS'),
          ('amulya', 'reddy', 'BZ'),
           ('avasya', 'Koneru', 'US'),
           ('sruthi','K' , 'AUS'),
           ('sruthi','reddy' , 'AUS');

解决方案

Try with below code:

Alter your StoredProc with following query.

SELECT DISTINCT firstname, lastname, country
FROM testingtable
WHERE firstname = @NameSearch OR lastname = @NameSearch

But I am not sure about your business logic with group by in your query. If you want then you can merge with this query.

Remove the single quotes from around the parameters.

Instead of:

where firstname='@NameSearch'

use:

where firstname=@NameSearch

The former is looking for records where the first name is equal to the literal string '@NameSearch'; the latter is looking for records where the first name is equal to the value in the @NameSearch parameter.

You should also read Falsehoods Programmers Believe About Names[^]. :)

这篇关于用户输入名称的文本框，可以是名字或姓氏或两者，然后记录数据显示在屏幕上。存储过程逻辑失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！