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