Application SQL

String formatted = String.Format("({0}) {1}-{2}", phoneNo.Substring(0, 3), phoneNo.Substring(3, 3), phoneNo.Substring(6, 4));




public IEnumerable<CcCensusUploadHistory> GetAllCensusUploadHistoryByDataTypeIDAllocation(int allocationNumber, int dataTypeID)
        {
            return base.UnitOfWork.Db.Fetch<CcCensusUploadHistory>(@"SELECT ccCensusUploadHistory.ccStatusID, ccCensusUploadHistory.censusUploadDate , ccCensusUploadHistory.censusfilePath, ccCensusUploadHistory.Version, ccCensusUploadHistory.Category,ccCensusUploadHistory.Description,ccCensusUploadHistory.censusFileName, ccCensusUploadHistory.FinalizedBy, ccStatus.PlanID, ccStatus.[allocationNumber]
  FROM(ccCensusUploadHistory INNER JOIN ccStatus ON ccCensusUploadHistory.ccStatusID = ccStatus.ccStatus_ID)  INNER JOIN AllocationDataRequests ON(ccStatus.allocationNumber = AllocationDataRequests.AllocationNumber) AND(ccStatus.planID = AllocationDataRequests.PlanNumber)
                                                                        WHERE ccStatus.[allocationNumber] = @0
                                                                        AND AllocationDataRequests.[DataTypeID] = @1", allocationNumber , dataTypeID);

        }    




ALTER PROCEDURE [dbo].[Pr_CaseDetails] @CaseId int  
AS

SELECT C.CaseID, P.PlanName
                FROM  tblCase C
LEFT JOIN PlanAccount P on P.PlanNumber =C.PlanNumber
WHERE C.CaseID = @CaseId



------------------------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[pr_un_project_GetAllocationDataItemList]
-- Add the parameters for the stored procedure here
  @ProjectID int,
  @userName nvarchar(50)
--, @max int

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF NOT EXISTS (SELECT * FROM AllocationDataRequests WHERE ProjectID = @ProjectID AND DataTypeID = 1)
BEGIN
INSERT Into AllocationDataRequests (AllocationNumber, PlanNumber, PlanClientNumber, ClientNumber, DataTypeID, DataRequestStatus,
ProjectTypeID, ProjectID, CreatedBy, DateCreated, PlanName, Recordkeeper)
SELECT Project.ProjectID, PlanAccount.PlanNumber, PlanAccount.PlanClientNumber, PlanAccount.ClientNumber, 1 AS DataItem,
Case when PlanAccount.[CensusRequired]=1  then 'Not Requested' else 'Not Required' end AS DataItemStatus, 4 as ProjectTypeID,
Project.ProjectID as ProjectID,
@userName, Getdate(), PlanAccount.PlanName, r.RecordkeeperProduct
FROM PlanAccount INNER JOIN Project ON PlanAccount.PlanNumber = Project.AccountID and Project.ProjectTypeID=4
Inner Join Recordkeeper r On PlanAccount.Recordkeeper=r.RecordkeeperNumber
WHERE Project.ProjectID = @ProjectID
END

Cursor with Function

DECLARE @result INT
     EXEC master.dbo.xp_fileexist '\\sharebear\client_project_folders\453064052\Allocations\Rony.txt', @result OUTPUT
Select @result

USE [TPAManager_be]
GO
/****** Object:  UserDefinedFunction [dbo].[fc_FileExists]    Script Date: 1/30/2017 05:40:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fc_FileExists](@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;


-----------------------CURSOR---ForEach Loop------------
DECLARE cur_savings CURSOR FOR
Select FilePathID,ProjectID,FilePath  from ProjectFile where AccountID=453064052 and ProjectID=50752 and WebAccess=1

DECLARE @ProjectID int,@AccountID int,@strSql VARCHAR(4000),@Path varchar(200),@cmd varchar(1000)
OPEN cur_savings

FETCH NEXT FROM cur_savings INTO @AccountID,@ProjectID,@Path

WHILE @@FETCH_STATUS = 0
BEGIN

--create table #File(AccountID Int,ProjectID Int,FileExist Bit)

insert #File
Select @AccountID,@ProjectID,dbo.[fc_FileExists] (@Path)

   FETCH NEXT FROM cur_savings  INTO @AccountID,@ProjectID,@Path
END

CLOSE cur_savings
DEALLOCATE cur_savings
GO


Select * from #File where FileExist=0

My Pitch! (Recommended)

 I am a Microsoft Certified Software Developer.  I have 8+ years of experience developing software using C#, MSSQL, and web technologies such as Asp.Net Web Form and Asp.Net MVC, Angular JS, JQuery, HTML, JavaScript, and CSS.

Strong Knowledge   of SDLC processes e.g.; Agile, Waterfall, SCRUM 

My Highest Degree:

M. Sc. in Management Information System (MIS),

 B.Sc. in Computer & Information System (CIS)

CQRS

http://www.cqrs.nu/Faq