ALTER PROCEDURE [dbo].[sp_get_farmerregistration_by_origin_approveheadid]
@originname varchar(100),
@approveheadid varchar(100),
@mastertype varchar(100),
@farmerctscode varchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlQuery VARCHAR(MAX)
DECLARE @masterYear VARCHAR(100)
SELECT @masterYear = farmermasteryear FROM tbl_originmanagement WHERE LOWER(REPLACE(@originname,' ','')) = @originname
IF @mastertype = 'farmerdata'
BEGIN
SET @sqlQuery = 'SELECT A.*,A.firstname + '' '' + A.lastname + '' '' + A.surname as farmername,B.villagename,
C.subdistrictname,D.departmentname as districtname,G.zonename,H.name as zonalheadname,M.sectionname,
I.statusname as zonalheadapprovestatus,J.statusname as regionalheadapprovestatus,
K.name as zonalheadapprovename,L.name as regionalheadapprovename,
E.regionname,F.name as regionalheadname
FROM dbo.tbl_farmerregistration_' + @originname + ' A
INNER JOIN dbo.tbl_villagemaster_' + @originname + ' B ON B.villageid = A.village
INNER JOIN dbo.tbl_subdistrictmaster_' + @originname + ' C ON C.subdistrictid = B.subdistrictid
INNER JOIN dbo.tbl_districtmaster_' + @originname + ' D ON D.districtid = C.districtid
INNER JOIN dbo.tbl_regionmaster_' + @originname + ' E ON E.regionid = D.regionid
INNER JOIN dbo.tbl_registration F ON F.regid = E.regionalhead
INNER JOIN dbo.tbl_sectionmaster_' + @originname + ' M ON M.fieldstaffid = a.createdby
INNER JOIN dbo.tbl_zonemaster_' + @originname + ' G ON G.zoneid = M.zoneid
INNER JOIN dbo.tbl_registration H ON H.regid = G.zoneheadid
INNER JOIN dbo.tbl_approvestatusmaster I ON I.approvestatusid = A.firstlevelapprovalstatus
INNER JOIN dbo.tbl_approvestatusmaster J ON J.approvestatusid = A.secondlevelapprovalstatus
LEFT JOIN dbo.tbl_registration K ON K.regid = A.firstlevelapprovedby
LEFT JOIN dbo.tbl_registration L ON L.regid = A.secondlevelapprovedby
WHERE H.regid = ''' + @approveheadid + ''' AND A.farmerseason = ''' + @masterYear + ''' order by farmerctscode'
END
ELSE IF(( @mastertype = 'farmerdata' ) and ( @farmerctscode = '' ))
BEGIN
SET @sqlQuery = 'SELECT A.*,A.firstname + '' '' + A.lastname + '' '' + A.surname as farmername,B.villagename,
C.subdistrictname,D.departmentname as districtname,G.zonename,H.name as zonalheadname,M.sectionname,
I.statusname as zonalheadapprovestatus,J.statusname as regionalheadapprovestatus,
K.name as zonalheadapprovename,L.name as regionalheadapprovename,
E.regionname,F.name as regionalheadname
FROM dbo.tbl_farmerregistration_' + @originname + ' A
INNER JOIN dbo.tbl_villagemaster_' + @originname + ' B ON B.villageid = A.village
INNER JOIN dbo.tbl_subdistrictmaster_' + @originname + ' C ON C.subdistrictid = B.subdistrictid
INNER JOIN dbo.tbl_districtmaster_' + @originname + ' D ON D.districtid = C.districtid
INNER JOIN dbo.tbl_regionmaster_' + @originname + ' E ON E.regionid = D.regionid
INNER JOIN dbo.tbl_registration F ON F.regid = E.regionalhead
INNER JOIN dbo.tbl_sectionmaster_' + @originname + ' M ON M.fieldstaffid = a.createdby
INNER JOIN dbo.tbl_zonemaster_' + @originname + ' G ON G.zoneid = M.zoneid
INNER JOIN dbo.tbl_registration H ON H.regid = G.zoneheadid
INNER JOIN dbo.tbl_approvestatusmaster I ON I.approvestatusid = A.firstlevelapprovalstatus
INNER JOIN dbo.tbl_approvestatusmaster J ON J.approvestatusid = A.secondlevelapprovalstatus
LEFT JOIN dbo.tbl_registration K ON K.regid = A.firstlevelapprovedby
LEFT JOIN dbo.tbl_registration L ON L.regid = A.secondlevelapprovedby
WHERE H.regid = ''' + @approveheadid + ''' AND A.farmerseason = ''' + @masterYear + ''' order by farmerctscode'
END
print(@sqlQuery)
EXEC(@sqlQuery)
END
when i run the above store procedure output i get as follows
exec sp_get_farmerregistration_by_origin_approveheadid 'ivorycoast','','farmerdata',''
1875 2019 2010 Ramesh Babu - MALE 31-05-1985 Chennai Suresh
1975 2019 2010 Suresh Babu - MALE 01-05-1995 Chennai Sathish
1775 2019 2010 Sashi Sashi - MALE 11-08-1995 Chennai Sathish
1675 2019 2010 Srir Sashi - MALE 01-12-1999 Chennai Sathish
1795 2019 2010 Arjun Sashi - MALE 01-07-1989 Chennai Sathish
Bu when i pass the farmerctscode in the procedure i get the same output as follows
exec sp_get_farmerregistration_by_origin_approveheadid 'ivorycoast','','farmerdata','1875'
1875 2019 2010 Ramesh Babu - MALE 31-05-1985 Chennai Suresh
1975 2019 2010 Suresh Babu - MALE 01-05-1995 Chennai Sathish
1775 2019 2010 Sashi Sashi - MALE 11-08-1995 Chennai Sathish
1675 2019 2010 Srir Sashi - MALE 01-12-1999 Chennai Sathish
1795 2019 2010 Arjun Sashi - MALE 01-07-1989 Chennai Sathish
when i pass farmerctscode in the stored procedure i want the below output
exec sp_get_farmerregistration_by_origin_approveheadid 'ivorycoast','','farmerdata','1875'
1875 2019 2010 Ramesh Babu - MALE 31-05-1985 Chennai Suresh
from my above store procedure what is the mistake.
What I have tried:
refer to describe section.