Blog ala Vidar

SQL, AppFrame and other cool technologies

Revisions(Files)

For some weird reason, they’ve got a Revisions table with a field called DocRef here.
The DocRef field contains a ; seperated list of GUIDs. In the new model we’re implementing, we’ve got an own table for files, so I had to figure out something smart to get this ; seperated list to be records.

First I had to fix sfnc_System_SplitGuid. Just remove the PRIMARY KEY setting on the PrimKey field in the return @GuidTable table variable. This is of course fixed on our main database in Ølen now, so you don’t need to fix it after next build. After that I wrote the following query:

SELECT
  R.Domain, R.DocDwgID, R.Rev, S.PrimKey AS FileRef
	FROM atbl_DocCtrl_Revisions AS R WITH (NOLOCK)
	CROSS APPLY sfnc_System_SplitGuid(REPLACE(REPLACE(REPLACE(R.DocRef, ';', ','), '{', ''), '{', '')) AS S
	WHERE R.DocRef IS NOT NULL

The nice thing about this is that we’re using CROSS APPLY to “apply” a UDF to a recordset. Sweet thing!

BTW! Today’s song is: Backstreet Boys – I want it that way

Advertisements

Comments are closed.

%d bloggers like this: