How to write stored procedure in the below query in sql.

Question

1.29/5 (3 votes)

See more:

SQL

"select sal_det_id, comp_id, sal_id, sal_isdeduct, sal_det_desc, sal_det_hrs,sal_det_amt as sal_det_amt,sal_det_cpf from "+
       " salary_details where sal_det_deleted = 0 "+
      " and sal_isdeduct = 1 and sal_id IN ("+
      " select sal_id from salary_master where sal_deleted = 0 "+
      " and sal_Year = " + lngPreviousYear + " and comp_id = " + strCompanyid +
      " and emp_code = '" + Txt_Employee.Text + "' ";

Posted 27-Aug-14 20:29pm

Member 10928697

Add a Solution

Comments

Gihan Liyanage 28-Aug-14 2:38am

Why are you duplicating the same Question ???

Member 10928697 28-Aug-14 2:40am

same question but query was different sir.

Gihan Liyanage 28-Aug-14 2:43am

http://www.codeproject.com/Answers/812440/How-to-write-stored-procedure-in-the-below-query-i#answer1

Gihan Liyanage 28-Aug-14 2:44am

Cant you do the same, Because you got the solution for older one... Is it ?

Member 10928697 28-Aug-14 2:46am

yes

Member 10928697 28-Aug-14 2:52am

we can clearly observed above query sir.previous query, this query was was not same sir...feilds are different.if u understand tell me or live it.

Gihan Liyanage 28-Aug-14 2:49am

Ok dude, try and do , if you get an issue then you can provide it to community to solve the problem, any way I see some answers below for you with learning links, please follow up. Best of luck. Sorry for devoting.. ;)

Member 10928697 28-Aug-14 2:57am

its ok sir..im join in my company just 4mnts..problem is mine.its ok

Gihan Liyanage 28-Aug-14 3:02am

Yes I know, Any way you have answers below, I re voted your question. Ask any question when you want. Best of luck. :)

Member 10928697 28-Aug-14 4:35am

thank u sir.

2 solutions

Solution 2

CREATE PROCEDURE [dbo].[stp_WriteYourProcedureName]
@previousYear date,---lngPreviousYear
@companyId int,--strCompanyid
@employeecode nvarchar(10)--xt_Employee.Text
AS
DECLARE @strSQL nvarchar(500)
BEGIN

SET @strSQL='SELECT sal_det_id, comp_id,
sal_id, sal_isdeduct,
sal_det_desc, sal_det_hrs,
sal_det_amt as sal_det_amt,sal_det_cpf from
salary_details where sal_det_deleted = 0
and sal_isdeduct = 1 and sal_id IN ( select sal_id from salary_master where sal_deleted = 0
and sal_Year = @previousYear and comp_id =@companyId and emp_code = @employeecode'

Declare @parameters nvarchar(max) = N'@previousYear date,@@companyId int,@employeecode nvarchar(10)';

exec sp_executesql @strSQL, @previousYear, @companyId, @employeecode
END

Posted 27-Aug-14 20:51pm

MrGeek404

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)

vangapally Naveen Kumar · Accepted Answer · 2014-08-27T20:43:00

First you see these linka to get Idea to write stored procedure.

1.Sql Server - How to write a Stored procedure in Sql server[^]

2.http://www.mssqltips.com/sqlservertutorial/162/how-to-create-a-sql-server-stored-procedure-with-parameters/[^]

For Creating Stored procedure

step1:Create Procedure with name like

Create Procedure MyProc
step2: Declare if you have any input parameters /output parameters.
Create Procedure MyProc
(
Input parameters ,
Output Parameters (If required)
)
Step3: write
AS BEGIN statement
step4: write your query
step5:Write END

step6: To Excute Storedprocudure

EXEC 'MyProc'

After SP Looks like:

Create Procedure MyProc
(
@PreviousYear int,
@CompanyID int,
@EMPCode varchar(50)

)
As
Begin

SQL

select sal_det_id, comp_id, sal_id, sal_isdeduct, sal_det_desc, sal_det_hrs,sal_det_amt as sal_det_amt,sal_det_cpf from  salary_details where sal_det_deleted = 0 
and sal_isdeduct = 1 and sal_id IN ( select sal_id from salary_master where sal_deleted = 0  and sal_Year =@PreviousYear  and comp_id = @CompanyID  and emp_code =@EMPCode )

End

to execute above sp

exec MyProc 2013,1,'EMP1'