在sql server中的另一列中查找整个列数据 [英] Lookup entire column data in another column in sql server

问题描述

我正在尝试在搜索表[NetCrawlerDemo]。[dbo]。[DA_TrackerInfo_SGA_ExactSearch]的另一列内搜索主表NetCrawlerDemo.dbo.Co_Industry_Mast的列值。我写了一个函数如下，但我得到的输出没有相关性。



函数代码：

  ALTER   FUNCTION  [dbo]。[SearchCompany]（ @Article   nvarchar （MAX））
  RETURNS   INT  
  AS  
  BEGIN  
  DECLARE   @ Found   INT ，< span class =code-sdkkeyword> @ Company   nvarchar （MAX）， @ last_cell   nvarchar （MAX）， @ RowNum   INT  ， @ CustId   INT  
  SELECT   @ CustId  = MAX（Id） FROM  NetCrawlerDemo.dbo.Co_Industry_Mast   -   以最高ID开头 
  SELECT   @ RowNum  = Count（*） FROM  NetCrawlerDemo.dbo.Co_Industry_Mast   -   获取记录总数 
    @ RowNum >  0    -   循环，直到没有更多记录 
  BEGIN  
  SELECT   @ Company  = Searchable_company_Name  FROM  NetCrawlerDemo.dbo.Co_Industry_Mast  WHERE  Id =  @ CustId    -   从该行获取其他信息 
 < span class =code-keyword> SET   @ Found  = CHARINDEX（ @ Company ，  @ Article ）  -   在文章详细信息中查找公司 
  WHILE   @ Found >  0    -   循环，直到没有更多记录 
  BEGIN  
  SET   @ Found  = CHARINDEX（ @ Company ， @ Article ）  -   在文章详细信息中查找公司 
  IF   @ Found >  0    -   条件为1或0  
  RETURN   1  
  ELSE  
  RETURN   0  
  END  
  SELECT   TOP   1  @CustId = Id  FROM  NetCrawlerDemo.dbo.Co_Industry_Mast  WHERE  Id<  @ CustID   ORDER   BY  Id  DESC    -   获取下一个 
  SET   @ RowNum  =  @ RowNum   -  < span class =code-digit> 1    -   减少计数 
  END  
  RETURN   @ Found  
  END  

存储过程我打电话给功能：

  ALTER  程序 [dbo ]。[ExactSearch] 
  AS  
  BEGIN  
  SET   NOCOUNT   ON  
 < span class =code-keyword> DECLARE   @ Command   NVARCHAR （MAX）
 
  SET   @ command  = '  
 UPDATE [NetCrawlerDemo]。[dbo]。[DA_TrackerInfo_SGA_ExactSearch] 
 SET [CompanySearch] = [dbo]。[SearchCompany]（[DA_ArticleDetails]） ' 
  BEGIN  TRY 
  EXEC （ @ Command ）
  PRINT  
 N '  CompanySearch列已更新...已成功。' 
  END  TRY 
  BEGIN  CATCH 
  PRINT  '  CompanySearch Column Error' 
  SELECT  
 ERROR_NUMBER（） AS  ErrorNumber 
，ERROR_MESSAGE（ ） AS  ErrorMessage; 
  END  CATCH 
 
  END  

请帮助我。

解决方案

根据我的理解，你想要更新 CompanySearch 字段 ID 来自 DA_ArticleDetails ，但是你没有指定什么关系在两个表之间。



可以使用单个UPDATE和SELECT语句更新数据，例如：

  USE  [NetCrawlerDemo]。[dbo]; 
 
  ALTER   PROCEDURE  ProcedureName 
  @ Article   INT    -   输入参数 
  AS  
  BEGIN  
 
 更新 t1  SET  [CompanySearch] = CHARINDEX（t2.Searchable_company_Name， @ Article ）
  FROM  [DA_TrackerInfo_SGA_ExactSearch]  AS  t1  INNER   JOIN  Co_Industry_Mast  AS  t2  ON  t1.PKey = t2.FKey   -   定义关系 
  WHERE  CHARINDEX（t2.Searchable_company_Name， @ Article ）> 0 
 
 返回  @@ ROWCOUNT  
 
  END  

Hi, I'm trying to search a column values of a Master Table "NetCrawlerDemo.dbo.Co_Industry_Mast" inside another column of Searching Table "[NetCrawlerDemo].[dbo].[DA_TrackerInfo_SGA_ExactSearch]". I wrote a function as below but the output which I'm getting has not relevance.

Function Code:

ALTER FUNCTION [dbo].[SearchCompany](@Article nvarchar(MAX)) 
RETURNS INT
AS 
BEGIN	
    DECLARE @Found INT,  @Company nvarchar(MAX), @last_cell nvarchar(MAX), @RowNum INT, @CustId INT    
	SELECT @CustId = MAX(Id) FROM NetCrawlerDemo.dbo.Co_Industry_Mast --start with the highest ID
	SELECT @RowNum = Count(*) FROM NetCrawlerDemo.dbo.Co_Industry_Mast --get total number of records	
	While @RowNum > 0 --loop until no more records
		BEGIN 			
		    SELECT @Company = Searchable_company_Name FROM NetCrawlerDemo.dbo.Co_Industry_Mast WHERE Id = @CustId  --get other info FROM that row		    
			SET @Found = CHARINDEX(@Company, @Article) --find the company in Article Details
				WHILE @Found > 0   --loop until no more records
				BEGIN
					SET @Found = CHARINDEX(@Company, @Article)--find the company in Article Details
					IF @Found > 0 --condition for 1 or 0
						RETURN 1
					ELSE
						RETURN 0										
				END
			SELECT TOP 1 @CustId= Id FROM NetCrawlerDemo.dbo.Co_Industry_Mast WHERE Id < @CustID ORDER BY Id DESC	--get the next one		
			SET @RowNum = @RowNum - 1 --decrease count	
	END    
	RETURN @Found 
END

Stored Procedure where I'm calling the Function:

ALTER PROCEDURE [dbo].[ExactSearch]
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Command NVARCHAR(MAX)	
		
	SET @command = '
			UPDATE [NetCrawlerDemo].[dbo].[DA_TrackerInfo_SGA_ExactSearch]
			SET [CompanySearch] = [dbo].[SearchCompany] ([DA_ArticleDetails])'
			BEGIN TRY
				EXEC(@Command)
				PRINT
					N'CompanySearch Column updated...Successfully.'
			END TRY
			BEGIN CATCH
				PRINT 'CompanySearch Column Error'
			    SELECT 
				ERROR_NUMBER() AS ErrorNumber
				,ERROR_MESSAGE() AS ErrorMessage;
			END CATCH	
				
END

Please help me.

解决方案

As per i understand you, you want to update CompanySearch field with ID from DA_ArticleDetails, but you did not specify what relationship becomes between both tables.

It is possible to update data with single UPDATE and SELECT statement, for example:

USE [NetCrawlerDemo].[dbo];

ALTER PROCEDURE ProcedureName
    @Article INT --input parameter
AS
BEGIN

UPDATE t1 SET [CompanySearch] = CHARINDEX(t2.Searchable_company_Name, @Article)
FROM [DA_TrackerInfo_SGA_ExactSearch] AS t1 INNER JOIN Co_Industry_Mast AS t2 ON t1.PKey = t2.FKey --define relationship
WHERE CHARINDEX(t2.Searchable_company_Name, @Article)>0

RETURN @@ROWCOUNT

END

这篇关于在sql server中的另一列中查找整个列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！