关于存储过程... [英] Regarding Stored procedure...

问题描述

亲爱的朋友，

我正在做诸如naukri或Shine或TimesJobs之类的应用程序.
在我的应用程序中，我有3个表，如Fresher，Working Professional和Student.
每个表都有技术"，位置"和经验"之类的列.我想根据我的需求使用这3个字段对人员进行分类.

在这里，我的问题是如何对人进行分类?
1.使用存储过程以及如何?
2.使用代码以及如何使用?
谢谢朋友，

问候，
Ram

Dear Friends,

I am doing an application like naukri or Shine or TimesJobs.
In my application I have 3 tables like Fresher, Working Professional and Student.
Each and every table having column like Technology, Location and Experience. I want to sort out the people using these 3 fields a per my requirement.

Here my question is how can I sort people?
1. Using stored Procedure and how?
2. Using code and how?
THANK YOU Friends,

Regards,
Ram

推荐答案

在存储过程中，可以使用ORDER BY或GROUP BY语句对它们进行排序.在不理想的代码中，应该让数据库执行此操作，但是使用LINQ可以编写非常直观的代码.

In your stored procedure you sort them using ORDER BY or GROUP BY statements. In code is not ideal, you should let the DB do it, but using LINQ you can write code that sorts things very intuitively.

嗨.....

请参阅以下示例过程.

Hi.....

Please see the below sample procedure.

create  procedure [dbo].[PanIndia_Settled_PJ]
(
@Zone varchar(max),
@Branchcode varchar(max),

@LastDate DateTime 
)
AS 
BEGIN

if OBJECT_ID('tempdb..#Detail_PJ') is not null 
drop table #Detail_PJ
if OBJECT_ID('tempdb..#Summary_PJ') is not null 
drop table #Summary_PJ
IF OBJECT_ID('tempdb..#StateList') IS NOT NULL 
DROP TABLE #StateList
IF OBJECT_ID('tempdb..#BranchList') IS NOT NULL 
DROP TABLE #BranchList

Create table #StateList 
		(
			stateName VARCHAR(100) NOT NULL
		)
		INSERT INTO #StateList SELECT IntegerfromList as stateName  from udf_GetListAsTable (@Zone,',')

Create table #BranchList 
		(
			Branchcode VARCHAR(100) NOT NULL
		)
		INSERT INTO #BranchList SELECT IntegerfromList as Branchcode  from udf_GetListAsTable (@Branchcode,',')



select STATENAME ,[BRANCH CODE] ,PRODUCT ,[LOAN APPROVAL DATE] as Apprvddt,[SETTLED DATE] as SettleDate,LOAN_STATUS,
	[UNO LOAN NUMBER],[ADVANCE AMOUNT],NETLNAMT,CONT_IRR,TENOR,convert(Numeric(18,2),round(([ADVANCE AMOUNT] *CONT_IRR * TENOR),0),0) AS WIRR1,
	convert(Numeric(18,2),Round([ADVANCE AMOUNT]*TENOR,0),2)  AS WIRR2,
	round(CONVERT (decimal(12,2), case when datediff(dd,[LOAN APPROVAL DATE] ,[SETTLED DATE]) = 0 then 1 
	else datediff(dd,[LOAN APPROVAL DATE] ,[SETTLED DATE]) end /30.00,2),2) as LnTenor,
	round(CONVERT (decimal(12,2), case when datediff(dd,[LOAN APPROVAL DATE] ,[SETTLED DATE]) = 0 then 1 
	else datediff(dd,[LOAN APPROVAL DATE] ,[SETTLED DATE]) end /30.00,2),2)  as netTenor
 into #Detail_PJ
from CUTOFFMONTHLCC ..CutOffLCC_PJ where Loan_Status ='settled'
and STATENAME in (select stateName from #StateList) and [BRANCH CODE]in (select Branchcode from #BranchList)
 and [SETTLED DATE] <= @LastDate 

Alter table #Detail_PJ add  CustHldyAdvInstlmnt smallint

update #Detail_PJ set CustHldyAdvInstlmnt=b.CustHldyAdvInstlmnt from #Detail_PJ a
join LOln_LnDetails_h b  on a.[UNO LOAN NUMBER]=b.Lnno

-	  
	
select StateName,[Branch Code],Product,CONVERT(VARCHAR(6),Apprvddt,112 )as [Approved Month],Loan_Status,
	count([UNO LOAN NUMBER])As Nos,SUM([ADVANCE AMOUNT])As [ADVANCE AMOUNT],SUM(NETLNAMT) AS [NET LOAN AMOUNT],
	SUM(WIRR1) AS WIRR1,SUM(WIRR2) AS WIRR2,sum(convert(Numeric(18,2),round(Cont_Irr * [ADVANCE AMOUNT]* LnTenor,0),0)) as WGRTENOR1,sum(convert(Numeric(18,2),Cont_Irr*[ADVANCE AMOUNT],0)) as WGRTENOR2, 
  sum(convert(Numeric(18,2),round(Cont_Irr*NETLNAMT*netTenor-CustHldyAdvInstlmnt,0),0)) as WNETTENOR1,sum(convert(Numeric(18,2),Cont_Irr* NETLNAMT,0)) as WNETTENOR2 
	
    into #Summary_PJ
	from #Detail_PJ 
	GROUP BY StateName,[Branch Code],Product,CONVERT(VARCHAR(6),Apprvddt,112 ),Loan_Status
	Order BY StateName,[Branch Code],Product,CONVERT(VARCHAR(6),Apprvddt,112 ),Loan_Status

	select * from #Summary_PJ
End

这篇关于关于存储过程...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！