SP从多个表中选择记录 [英] SP to select record from multiple table

问题描述

我必须从多个表中选择记录.参数表ID以，"(逗号)分隔，搜索条件也意味着文本也必须在另一个参数中以，"(逗号)分隔.

现在我必须创建SP，但是由于我是Database中的新手，我变得很困惑，如何创建SP.
所以请帮帮我
在此先感谢

Hi,
I have to select record from multiple table.there are parameter table ID separated by ","(comma) and also search criteria means the text has to select also in another parameter separated by "," (comma).

now I have to create SP but I am getting confused,how to create that SP because I am new in Database.
so please help me
Thanks in advance

DataTable objDT = new DataTable();
       try
       {
           SqlParameter[] param=new SqlParameter[2];
           param[0] = new SqlParameter("@DatasetID", datasetID);
           param[1] = new SqlParameter("@Tags", tags);
           objDT = dbOperations.selectData("",param);
       }
       catch (Exception e)
       {


       }
       return objDT;

数据集ID包含-"12,25,45,36,78"，这是表名称
标签包含要查找的"abc，sdf，edf"记录

datasetID contain- "12,25,45,36,78" which is table name
tags contain--"abc,sdf,edf" record which is to be find

推荐答案

CREATE PROCEDURE YourProcName
  @Param nvarchar(50) 
AS
    SET NOCOUNT ON;
    SELECT * FROM YourTable WHERE field1 = @Param; -- Your query here
GO

http://msdn.microsoft.com/en-us/library/ms190669.aspx [ ^ ]
http://msdn.microsoft.com/en-us/library/ms345415.aspx [ ^ ]

http://msdn.microsoft.com/en-us/library/ms190669.aspx[^]
http://msdn.microsoft.com/en-us/library/ms345415.aspx[^]

使用join选择记录，对于serach参数，可以使用``in''.下面是从多个记录中检索记录的示例，并且搜索参数以逗号分隔.

存储过程:

select the record by using join and for the serach parameter you can use ''in'' . Below is the example of retrieving the record from multiple records and the search paremeters are comma separated.

Stored procedure:

SELECT			
		ac.account_id												AS [TPCompanyId], 
		ac.account_name												AS [CompanyName], 
		[dbo].fn_get_address_forXML(ac.primary_address_id, @pi_user_id) as [address],
		CASE WHEN LEN(ac.main_phone_extension)>0 THEN
			CASE WHEN LEN(ac.main_phone) = 10 THEN 
				[dbo].fn_get_phone_format(ac.main_phone) +'' x ''+ ac.main_phone_extension ELSE 
				ac.main_phone + '' x '' + ac.main_phone_extension END 
		ELSE CASE WHEN len(ac.main_phone) = 10 THEN 
			[dbo].fn_get_phone_format(ac.main_phone) ELSE 
			ac.main_phone END end									AS [Phone],  
   		
	
		ac.email_id													AS [Email], 
		ac.created_date												as[CreatedDate]
	
	
	FROM 
		
		[dbo].account ac 
		inner join [dbo].ref_account_type at On ac.account_type_id = at.account_type_id 
										and at.account_type_id = 2
										and CASE WHEN @l_user_ch_provider_id is null THEN 1 ELSE ac.ch_provider_id END
										  = CASE WHEN @l_user_ch_provider_id is null THEN 1 ELSE @l_user_ch_provider_id END
										and CASE WHEN @l_user_account_type_id is null or @l_user_account_type_id = 1 THEN 1 ELSE ac.account_id END
										  = CASE WHEN @l_user_account_type_id is null or @l_user_account_type_id = 1 THEN 1 ELSE @l_user_account_id END
		LEFT JOIN [dbo].address ad ON ac.primary_address_id = ad.address_id 
		LEFT JOIN [dbo].contact ct ON ac.credit_processed_contact_id = ct.contact_id
		LEFT JOIN [dbo].ref_city ci ON ad.city_id = ci.city_id 
		LEFT JOIN [dbo].ref_state st ON ad.state = st.state_id 
		LEFT JOIN [dbo].ref_credit_status cs ON ac.credit_status_id = cs.credit_status_id 
		LEFT JOIN [dbo].location lo ON ac.account_location_id = lo.location_id 
		LEFT JOIN [dbo].ref_account_status ras ON ras.account_status_id = ac.account_status_id 
		LEFT JOIN [dbo].contact cn ON ac.account_manager_id = cn.contact_id
		WHERE ISNULL(ac.account_market_id, 0) IN (SELECT market_id FROM @tblMarkets) 

在上面的示例中，market_id是搜索参数.请仔细检查此存储过程.如果我正确理解了您的问题，那么它必须可以帮助您解决问题.

In the above example the market_id is the search parameter. Please go through this stored procedure carefully. If I understand your problem correctly then it must help you otherwise precise your problem.

您好，亲爱的，

您必须将分隔的表名称和ID存储在一个临时表中.然后遍历该表记录以获取所需的记录.临时表应包含minId和maxId以遍历记录.

While(MinId< MaxId)
开始
从表<临时表中的表名>中选择*.其中<您的条件>

结束

上面是简单的示例，无需任何连接即可获取所有记录.如果您想加入表格并获取记录，那将毫无用处，因此在这种情况下，您必须根据所有循环遍历每个表格的条件获取所有表格名称并加入它们.通过遍历记录的方式，您可以使用游标.但是它不像临时表那样可靠.使用临时表的存储过程的性能优于游标.

Hello dear,

You have to store the separated table names and ids in one temporary table. Then loop through that table records to get your desired records. The temporary table should contain minId and maxId to loop through the record.

While ( MinId<MaxId)
begin
select * from table <table name in the temporary table> where <Your condition>

end

above is the simple exmaple to get all the reocrds without using any join. If you want to join the tables and get record then it won''t be worthful.So in that case U have to get all the table names and join them according to your conditon wihtout looping through each table. By the way to loop through the record u can use cursor. But it is not reliable like temporary table. The performance of the stored procedure using temp table is better than cursor.

这篇关于SP从多个表中选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！