Regarding Stored procedure...

Question

5.00/5 (1 vote)

See more:

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

Posted 18-Jul-12 21:10pm

ramuAlla

Add a Solution

Comments

Kaushik Saha from Kolkata,India 19-Jul-12 3:13am

Give your these 3 table structure

ramuAlla 19-Jul-12 3:37am

Sorry Kaushik ..its confidential and thanks for reply

Sandeep Mewara 19-Jul-12 3:30am

And would you like to try anything by yourself too?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2012-07-18T21:11:00

Solution 1

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.

Posted 18-Jul-12 21:11pm

Christian Graus

Comments

ramuAlla 19-Jul-12 3:24am

Thanks For Reply Christian,
Can you please send me sample code in LINQ or Send me sample SP for using Orderby and groupby..

Christian Graus 19-Jul-12 3:27am

No. If you think I can based on what you've said ( which does not include your DB schema ), then you don't understand your own question. Also, this is the most basic SQL imaginable, you should not work in a database driven app if you cannot write it. If you read up on how to do it, and get stuck, by all means ask a specific question and I'll gladly help. It's physically impossible to create a copy and paste solution based on what you've said, and I prefer not to do that, because I prefer to help real programmers who, when told what to do, can do some basic research and try for themselves.

Arul R Ece · Answer 2 · 2012-07-18T21:28:00

Hi.....

Please see the below sample procedure.

SQL

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