I’m sure its saving the Unicorn somewhere…

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[GetAllCandidateTagFormatNormalizedTagFormats]’) AND ObjectProperty(id, N’IsProcedure’) = 1)
DROP PROCEDURE [dbo].[GetAllCandidateTagFormatNormalizedTagFormats]
GO

CREATE PROCEDURE dbo.GetAllCandidateTagFormatNormalizedTagFormats
@Skip int,
@Take int,
@OrderBy nvarchar(Max),
@Direction nvarchar(4),
@Filter nvarchar(Max)

AS
/*
*******************************************************************************
<Name>
GetAllCandidateTagFormatNormalizedTagFormats
</Name>

<Purpose>
Retrieves all CandidateTagFormat_NormalizedTagFormats inside the skip/take parameters order and filtered by the adequate paramters
it belongs to.
</Purpose>

<Notes>
</Notes>

<OutsideRef>
Called From: _1004_NormalizedTagService, NormalizingService
</OutsideRef>

<ChangeLog>
Change No: Date: Author: Description:
_________ ___________ __________ _____________________________________
001 05.03.2014 AH Created.
</ChangeLog>
*******************************************************************************
*/
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT OFF; — Allow procedure to continue after error

— *****************************************
— Parameter string. Used for error handling
— *****************************************
DECLARE @SQL NVARCHAR(MAX)
,@ErrorNumber INT
,@ErrorMessage VARCHAR(400)
,@ErrorSeverity INT
,@ErrorState INT
,@ErrorLine INT
,@ErrorProcedure VARCHAR(128)
,@ErrorMsg VARCHAR(2000)
,@Params NVARCHAR(MAX)
,@OrderByString NVARCHAR(MAX)
,@FromAndTo NVARCHAR (255)
,@FilterString NVARCHAR (MAX) = ‘ ‘;

DECLARE @Temp TABLE (
Guid uniqueidentifier
,NoOfEqualFormats int
,NoOfCandidateReferences int
,CandidateTagFormatGuid uniqueidentifier
,NormalizedTagFormatGuid uniqueidentifier
,NewFormat NVARCHAR(255)
,NewObjectTypeGuid uniqueidentifier
,OldObjectTypeGuid uniqueidentifier
,NewClassGroupGuid uniqueidentifier
,Action char(1)
,RangeTo NVARCHAR(255)
,RangeFrom NVARCHAR(255)
,Status NVARCHAR(255)
,Remarks NVARCHAR(255)
,ResultingFormat NVARCHAR(255)
,CreationDateTime dateTime
,NewObjectTypeName NVARCHAR(255)
,OldObjectTypeName NVARCHAR(255)
,NewClassGroupName NVARCHAR(255)
,ImportFileName NVARCHAR(100)
,OldFormat NVARCHAR(255)
,ImportConfigurationImportFileGuid uniqueidentifier
)

SET @Params = ”
+ CHAR(13) + ‘@Skip = ‘ + COALESCE(CONVERT(VARCHAR(36), @Skip), ‘NULL’)
+ CHAR(13) + ‘@Take = ‘ + COALESCE(CONVERT(VARCHAR(36), @Take), ‘NULL’)
+ CHAR(13) + ‘@OrderBy = ‘ + COALESCE(CONVERT(VARCHAR(36), @OrderBy), ‘NULL’)
+ CHAR(13) + ‘@Direction = ‘ + COALESCE(CONVERT(VARCHAR(36), @Direction), ‘NULL’)
+ CHAR(13) + ‘@Filter = ‘ + COALESCE(CONVERT(VARCHAR(36), @Filter), ‘NULL’)

BEGIN TRY

SELECT @OrderByString = ‘Order By ‘ + @OrderBy + ‘ ‘ + @Direction;
SELECT @FromAndTo = ‘ ‘ + (SELECT CONVERT(varchar(10),@Skip)) + ‘ AND ‘ + (SELECT CONVERT(varchar(10),@Skip + @Take)) + ‘ ‘;
IF @Filter IS NOT NULL AND @Filter <> ” SELECT @FilterString = ‘ AND ‘ + @Filter + ‘ ‘;

–SELECT STRING BUILD START
SELECT @SQL = ‘

With NormalizedDataTable AS (

SELECT CandidateTagFormat_NormalizedTagFormat.Guid
,CandidateTagFormat_NormalizedTagFormat.CandidateTagFormatGuid
,CandidateTagFormat_NormalizedTagFormat.NormalizedTagFormatGuid
,CandidateTagFormat_NormalizedTagFormat.NewFormat
,CandidateTagFormat_NormalizedTagFormat.NewObjectTypeGuid
,CandidateTagFormat_NormalizedTagFormat.NewClassGroupGuid
,CandidateTagFormat_NormalizedTagFormat.Action
,CandidateTagFormat_NormalizedTagFormat.RangeTo
,CandidateTagFormat_NormalizedTagFormat.RangeFrom
,CandidateTagFormat_NormalizedTagFormat.Status
,CandidateTagFormat_NormalizedTagFormat.Remarks
,CandidateTagFormat_NormalizedTagFormat.ResultingFormat
,CandidateTagFormat_NormalizedTagFormat.CreationDateTime
,ObjectType.Type As NewObjectTypeName
,OOT.Guid As OldObjectTypeGuid
,OOT.Type As OldObjectTypeName
,ClassGroup.Name As NewClassGroupName
,ImportFile.Name As ImportFileName
,CandidateTagFormat.Format As OldFormat
,CandidateTagFormat.ImportConfigurationImportFileGuid
,(Select count(*) from CandidateTagData Where CandidateTagFormatGuid = CandidateTagFormat_NormalizedTagFormat.CandidateTagFormatGuid) AS NoOfEqualFormats
,(Select count(*) from CandidateTagFormat_NormalizedTagFormat Where CandidateTagFormat_NormalizedTagFormat.CandidateTagFormatGuid = CandidateTagFormat.Guid) AS NoOfCandidateReferences
,ROW_NUMBER() OVER (ORDER BY CandidateTagFormat_NormalizedTagFormat.CreationDateTime) AS RowNumber
FROM CandidateTagFormat_NormalizedTagFormat
Left outer join CandidateTagFormat ON
CandidateTagFormat_NormalizedTagFormat.CandidateTagFormatGuid = CandidateTagFormat.Guid
Left outer join ImportConfiguration_ImportFile ON
CandidateTagFormat.ImportConfigurationImportFileGuid = ImportConfiguration_ImportFile.Guid
Left outer join ImportFile ON
ImportConfiguration_ImportFile.ImportFileGuid = ImportFile.Guid
Left outer join ObjectType ON
CandidateTagFormat_NormalizedTagFormat.NewObjectTypeGuid = ObjectType.Guid
Left outer join ObjectType AS OOT ON
CandidateTagFormat.ObjectTypeGuid = OOT.Guid
Left outer join ClassGroup ON
CandidateTagFormat_NormalizedTagFormat.NewClassGroupGuid = ClassGroup.Guid
)
SELECT
Guid
,NoOfEqualFormats
,NoOfCandidateReferences
,CandidateTagFormatGuid
,NormalizedTagFormatGuid
,NewFormat
,NewObjectTypeGuid
,OldObjectTypeGuid
,NewClassGroupGuid
,Action
,RangeTo
,RangeFrom
,Status
,Remarks
,ResultingFormat
,CreationDateTime
,NewObjectTypeName
,OldObjectTypeName
,NewClassGroupName
,ImportFileName
,OldFormat
,ImportConfigurationImportFileGuid
FROM
NormalizedDataTable
WHERE
RowNumber BETWEEN
‘
+ @FromAndTo
+ @FilterString
+ @OrderByString

–SELECT STRING BUILD END

–Have to insert result from execute into a temp table as else Entitiy Framework can’t build the output type.
INSERT INTO @Temp
EXEC SYS.SP_EXECUTESQL @SQL

SELECT * FROM @Temp
END TRY

BEGIN CATCH

— Execute the error retrieval routine.
SELECT 9
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE();

SET @ErrorMsg = ‘Error Number : ‘ + CAST(@ErrorNumber AS VARCHAR(5)) + CHAR(13)
+ ‘Procedure Name : ‘ + @ErrorProcedure + CHAR(13)
+ ‘Procedure Line : ‘ + CAST(@ErrorLine AS VARCHAR(5)) + CHAR(13)
+ ‘Error Message : ‘ + @ErrorMessage + CHAR(13)
+ ‘Parameters : ‘ + CHAR(13) + @Params + CHAR(13);

–Raise the exception.
RAISERROR (@ErrorMsg, @ErrorSeverity, @ErrorState);
END CATCH
END
GO RIDE A BIKE

that’ll be one of those IT jokes then.

Hilllll – Arious

Indeed, and for a double whammy, its an IT joke in German…

Apparently its a real bit of code that she is getting into a lather about…

She can’t spell prams.

award for most boring post ?

Cool.
Story.
Bro.

award for most boring post ?

I’m expecting a SQL tomorrow.

Can you see what I did there?!!??!!! igmc.

You should ask her how she was really getting her jollies today….. 😈

MoreCashThanDash – Member

You should ask her how she was really getting her jollies today…..

There’ve been a few inner joins going on, that’s for sure.

I’m expecting a SQL tomorrow.

*applause*

Wow and I though my day was tedious.

Also, embedding a change log right into the code?

1982 called; they want their coding standards back.

More clever sql: