Make one stored procedure and call your sql script using stored procedure in ASP.Net Application.

SQL

CREATE PROCEDURE YouProcedureName
AS
BEGIN
      SET NOCOUNT ON;
      -- Your SQL Statements
END
GO

Also refer the below Links for your reference how to create Store Procedure and how to call it in ASP.Net Application.

Using Stored Procedures in SQL Server Database

Select SQL Server Stored Procedures using ASP.Net Example

hello Kalpesh ..

Thank you for replying..

But the problem is that there is no data showing on grid while using procedure.

its showing message that "No Record found."

If you help in this it will much fruitful for me.

Thanking you

This is my procedure script

CREATE procedure [dbo].[usp_promotion_eligibility]
AS 
SELECT a.emp_no,a.emp_name,a.grade,a.designation,a.department,a.caste,yearofassessment,ratings,'marks'=(case when a.grade IN ('W0','W1','W2') then marks_upto_w2 
     when a.grade IN ('W3','W4','W5','W6','W7') then marks_upto_w7
	 when a.grade IN ('W8','W9','W10','W11') then marks_upto_SG else 0 end ),
	 'MARKS_SENIORITY'=(case when a.grade='W2' then 10+(DATEDIFF(YY,Promotion_Date,'2017/07/31')-2)*5 
	 when a.grade='W6' then 10+(DATEDIFF(YY,Promotion_Date,'2017/07/31')-3)*5 else 0 end ),marks_tradetest,marks_interview,vigilance_enquiry ,Promotion_Date,Remarks
	into   #temp_ratings 
 FROM Employee_Master a ,employee_rating b,rating_master c
Where a.emp_no=b.emp_no 
and b.Ratings=c.Rating
and 1=(CASE WHEN DATEDIFF(YY,Promotion_Date,'2017/07/31') >=3  AND  a.grade IN ('W0','W1') THEN 1
 WHEN DATEDIFF(YY,Promotion_Date,'2017/07/31')  >=5  AND a.grade ='W2' THEN 1
WHEN DATEDIFF(YY,PROMOTION_DATE,'2017/07/31')  >=4 AND  a.grade IN ('W3','W4','W5', 'W6','W7')  THEN 1
WHEN DATEDIFF(YY,Promotion_Date,'2017/07/31')  >=3  AND  a.grade IN ('W8','W9','W10','W11' ) THEN 1
ELSE 0 END )
and 1= ( case when a.grade in ('W0','W1', 'W2')  and  yearofassessment between datepart(yyyy,dateadd(yy,-3,getdate()))  and  datepart(yyyy,getdate())  then 1 
when a.grade in ('W4','W5', 'W6','W7','W3')  and  yearofassessment between datepart(yyyy,dateadd(yy,-4,getdate()))  and datepart(yyyy,getdate())  then 1 
when a.grade in ('W8','W9', 'W10','W10','W12')  and  yearofassessment between datepart(yyyy,dateadd(yy,-2,getdate()))  AND  datepart(yyyy,getdate())  then 1 
else 0 end) 
go

--insert into  #temp_ratings_opt
select a.emp_no,a.emp_name,a.grade,a.designation,a.department,a.caste,a.ratings as 'Y0','NA' as 'Y1','NA' as 'Y2','NA' as 'Y3' ,'NA' as 'Y4',
marks as 'Y0_mark',0 as 'Y1_mark',0 as 'Y2_mark',0 as 'Y3_mark' ,0 as 'Y4_mark',MARKS_SENIORITY,marks_tradetest,marks_interview,vigilance_enquiry ,promotion_date,'Eligibility'='NA',Remarks
into  #temp_ratings_opt 
from #temp_ratings a where yearofassessment=datepart(yyyy,getdate())


insert into  #temp_ratings_opt
select a.emp_no,a.emp_name,a.grade,a.designation,a.department,a.caste,'NA' as 'Y0',ratings as 'Y1','NA' as 'Y2','NA' as 'Y3' ,'NA' as 'Y4',
0 as 'Y0_mark',marks as 'Y1_mark',0 as 'Y2_mark',0 as 'Y3_mark' ,0 as 'Y4_mark',0,0,0,'NA','','Eligibility'='NA',Remarks
	from #temp_ratings a where yearofassessment=datepart(yyyy,getdate())-1 and not exists (select * from #temp_ratings_opt c where c.emp_no =a.emp_no )
	go

insert into  #temp_ratings_opt
select a.emp_no,a.emp_name,a.grade,a.designation,a.department,a.caste,'NA' as 'Y0','NA' as 'Y1',ratings as 'Y2','NA' as 'Y3' ,'NA' as 'Y4',
0 as 'Y0_mark',0 as 'Y1_mark',marks as 'Y2_mark',0 as 'Y3_mark' ,0 as 'Y4_mark',0,0,0,'NA','','Eligibility'='NA',Remarks
	from #temp_ratings a where yearofassessment=datepart(yyyy,getdate())-2 and not exists (select * from #temp_ratings_opt c where c.emp_no =a.emp_no )
	go

insert into  #temp_ratings_opt
select a.emp_no,a.emp_name,a.grade,a.designation,a.department,a.caste,'NA' as 'Y0','NA' as 'Y1','NA' as 'Y2',ratings as 'Y3' ,'NA' as 'Y4',
0 as 'Y0_mark',0 as 'Y1_mark',0 as 'Y2_mark',marks as 'Y3_mark' ,0 as 'Y4_mark',0,0,0,'NA','','Eligibility'='NA',Remarks
	from #temp_ratings a where yearofassessment=datepart(yyyy,getdate())-3 and not exists (select * from #temp_ratings_opt c where c.emp_no =a.emp_no )
go

insert into  #temp_ratings_opt
select a.emp_no,a.emp_name,a.grade,a.designation,a.department,a.caste,'NA' as 'Y0','NA' as 'Y1','NA' as 'Y2','NA' as 'Y3' ,ratings as 'Y4',
marks as 'Y0_mark',0 as 'Y1_mark',0 as 'Y2_mark',0 as 'Y3_mark' ,marks as 'Y4_mark',0,0,0,'NA','','Eligibility'='NA',Remarks
	from #temp_ratings a where yearofassessment=datepart(yyyy,getdate())-4 and not exists (select * from #temp_ratings_opt c where c.emp_no =a.emp_no )
	go



update a
set Y1=ratings,Y1_mark=marks
from  #temp_ratings_opt a,  #temp_ratings b
where a.emp_no=b.emp_no
and yearofassessment=datepart(yyyy,getdate())-1
and y1='NA'
go

update a
set Y2=ratings,Y2_mark=marks
from  #temp_ratings_opt a,  #temp_ratings b
where a.emp_no=b.emp_no
and yearofassessment=datepart(yyyy,getdate())-2
and y2='NA'
go



update a
set Y3=ratings,y3_mark=marks
from  #temp_ratings_opt a,  #temp_ratings b
where a.emp_no=b.emp_no
and yearofassessment=datepart(yyyy,getdate())-3
and y3='NA'
go


update a
set Y4=ratings,y4_mark=marks
from  #temp_ratings_opt a,  #temp_ratings b
where a.emp_no=b.emp_no
and yearofassessment=datepart(yyyy,getdate())-4
and y4='NA'
go

update a
set eligibility='Y'
from  #temp_ratings_opt a,  marks_master b
where a.grade=b.grade
and Y0_mark+y1_mark+y2_mark+y3_mark+y4_mark >=b.marks
--and vigilance_enquiry='NA'
go

update a
set eligibility='NO'
from  #temp_ratings_opt a
where grade in ('W2','W6')
and eligibility='Y' and 
1=(case when vigilance_enquiry='Y' then 1
        when left(caste,2)='GE' and Y0_mark+y1_mark+y2_mark+y3_mark+y4_mark<20 then 1
		when left(caste,2)='GE' and marks_tradetest<10 then 1
		when left(caste,2)='GE' and marks_interview<10 then 1
		when left(caste,2) in ('SC','ST','OB') and Y0_mark+y1_mark+y2_mark+y3_mark+y4_mark<17 then 1
		when left(caste,2) in ('SC','ST','OB') and marks_tradetest<8 then 1
		when left(caste,2) in ('SC','ST','OB') and marks_interview<8 then 1
		else 0 end)

go


select a.emp_no,a.emp_name,a.grade,a.designation,a.department,a.caste,promotion_date,Y0 ,Y1,Y2,Y3,Y4,Y0_mark,y1_mark,y2_mark,y3_mark,y4_mark,Y0_mark+y1_mark+y2_mark+y3_mark+y4_mark  as marks,MARKS_SENIORITY,marks_tradetest,marks_interview,vigilance_enquiry ,eligibility ,Remarks
from #temp_ratings_opt a --where eligibility in ('Y'
order by 1

 VB Code...

    Private Function BindData() As DataTable
        Dim constr2 As String = ConfigurationManager.ConnectionStrings("cmdpns").ConnectionString
        Using conn As New SqlConnection(constr2)
            ' Using cmd2 As New SqlCommand("select * from record_table where user_id=@user_id order by id desc")
            'Using cmd2 As New SqlCommand("select * from record_table where user_id=@user_id order by ORDER BY {0}")
            'Dim con As New SqlConnection(strConnString)
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_promotion_eligibility"
            cmd.Connection = conn
            Try
                conn.Open()
                'GridView1.EmptyDataText = "No Records Found"
                GridView1.DataSource = cmd.ExecuteReader()
                GridView1.DataBind()
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
                conn.Dispose()
            End Try
        End Using
    End Function

1) First Execute Sp like this in SQL Query Window check whether it returning any result.

EXEC usp_promotion_eligibility

2) Check Connection string details In Web config for cmdpns its same as Your database information.

3) here you are written Function As Private Function BindData() As DataTable which says its returning the DataTable as return value but in fuction you are not returning anything change the Fuction as Private Sub BindData(). and call it in Page load.