T-SQL Stored Procedure Example

USE [LDSJournal]
GO
/****** Object:  StoredProcedure [dbo].[jrnl_LoadSubscriptionsPaged]    Script Date: 03/08/2012 01:04:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Joel>
-- Create date: <Create Date,,10232011>
-- Description:	<Description,,Load paged subscriptions>
-- =============================================
ALTER PROCEDURE [dbo].[jrnl_LoadSubscriptionsPaged]
	 @UserObjId uniqueidentifier,
	 @EntryObjIdCsv varchar(max),	 
	 @JournalObjIdCSV varchar(max),
	 @StartDate datetime,
	 @EndDate datetime,
	 @Culture varchar(5),
	 @SortOrder int,
	 @PageSize int,
	 @PageNum int,
	 @ResultsCount bigint out
AS
BEGIN
 
/* Change log */
	-- 08122011 joel - Modified to return postLastModified
	-- 08212011 joel - postLastModified/jpt table does NOT have rows for all jpl rows!
	-- 08212011 joel - Added author name to output; fixed type in JPT
	-- 09022011 joel - Modified to remove DRAFT status entries
	-- 10042011 joel - Modified to accept guid jobjid input. Commented where clause, seem redundant
	-- 10142011 joel - Ignore potsWordCount to display posts with attachments only.
	-- 10152011 joel - AuthorObjCsv filter, journalObjCsv filter, sorting refactoring, added paging
	-- 10162011 joel - Optimization. Filters broken - only supports author OR journal, not both! see below.
	-- 10222011 joel - Adding support for subscriptions
	-- 10302011 joel - Adding support for @entryobjidcsv which overrides the subscription
	-- 11182011 joel - Adding support for toggling subscription data on/off. Removed authorcsv. Added modes.
	-- 11282011 joel - Refactored to work with jrnl_entries_to_users permissions table. Only shows rows with view permission.
	-- 11292011 joel - Adding support for subscribed entries with permissions to show in subscriptions mode
	-- 11292011 joel - Changed sort order for subscriptions to postCreated. Jrnl/Single mode is postTime
	-- 11292011 joel - Fixed sorting bug by ordering by RowNum
	-- 12142011 joel - postCreated is stored as a UTC value. When we return it, we must convert back to user's local time.
	-- 12142011 joel - Home mode should not return entries that you authored.
	-- 02032012 joel - Return NumLikes, NumComments, AttachmentType, AttachmentCount
 
	/*
	-- Input parameters
	declare @UserObjId uniqueidentifier;select @UserObjId='F3C95EA5-2E5A-4207-A8C3-4776D363E568';
	declare @EntryObjIdCSV varchar(max);select @EntryObjIdCSV='';--30c0b614-9c9a-45f8-8827-d1a2df08fcf8';		
	declare @JournalObjIdCSV varchar(max); select @JournalObjIdCSV='Fa2af6b0-026c-4e44-aa68-95fb095a7c8b';
	declare @StartDate datetime;select @StartDate = '2011-01-01 0:0:0';
	declare @EndDate datetime; select @EndDate = '2012-1-1';
	declare @Culture varchar(5);select @Culture='en-us';
	declare @SortOrder int;select @SortOrder = 2; --1=asc, 2=desc
	declare @PageSize int;select @PageSize=500;
	declare @PageNum int;select @PageNum=1;
	declare @ResultsCount bigint; -- out;
	*/
 
	-- Internal vars
	declare @FirstRecNum int;		set @FirstRecNum=0;
	declare @LastRecNum int;		set @LastRecNum=0;	
	declare @useFilterJournal bit;	set @useFilterJournal=Len(@JournalObjIdCsv);	
	declare @useFilterEntry bit;	select @useFilterEntry=Len(@EntryObjIdCSV);
	declare @useFilterSubscriptions bit; select @useFilterSubscriptions=~ @useFilterEntry; -- toggle: !@filterEntry
	declare @userTzOffset int;select @userTzOffset = 0; -- minutes todo: pull from profile
 
	-- Attempt to load this user's tzoffset from profile	
	if (@UserObjId<>convert(uniqueidentifier,convert(binary,0))) begin
		select @userTzOffset=timeoffset from aspnet_sqlprofileoptions where userid=@UserObjId		
	end				
 
	-- 3 operation modes, automatically chosen based on input params	
	declare @mode int;select @mode =3;	
 
	-- Constants		
	declare @subtypeAuthor int;		set @subtypeAuthor=1;
	declare @subtypeJournal int;	set @subtypeJournal=2;
	declare @subtypeEntry int;		set @subtypeEntry=4;
	declare @permissionFlagView int;	set @permissionFlagView=1;
	declare @permissionFlagDefault int;	set @permissionFlagDefault=64;
 
	-- Subscriptions
	declare @filter_subscriptions table (obj uniqueidentifier, typeid int);
	insert into @filter_subscriptions
		select SubscriptionEntityObjId,SubscriptionEntityTypeId 
			from jrnl_subscriptions sub with(nolock) 
			where UserObjId=@UserObjId
 
 
	-- Fill the journal filter
	declare @filter_journals table (obj uniqueidentifier);
	if (@useFilterJournal=1) begin
		insert into @filter_journals	
		select convert(uniqueidentifier,Item) from split(@JournalObjIdCSV,',')
 
	end;
 
	-- Fill the entry filter
	declare @filter_entry table (obj uniqueidentifier);
	if (@useFilterEntry=1) begin
		insert into @filter_entry	
		select convert(uniqueidentifier,Item) from split(@EntryObjIdCSV,',')
	end;
 
	-- Cleanup paging params
	SET @PageSize = CASE WHEN @PageSize IS NULL THEN 10 WHEN @PageSize < 1 THEN 10 ELSE @PageSize END;
	SET @PageNum = CASE WHEN @PageNum IS NULL THEN 1 WHEN @PageNum < 1 THEN 1 ELSE @PageNum END;
 
	-- Configure paging
	SET @FirstRecNum = ((@PageNum - 1) * @PageSize) + 1;
	SET @LastRecNum = @FirstRecNum + @PageSize - 1;		
 
	-- Fill the staging join table
	declare @staging table (journalId bigint, postId uniqueidentifier, postTime datetime, RowNum int, 
	anonPermissionFlags int, userPermissionFlags int, journalDefaultPermissionFlags int);	
 
	if (@mode=3) begin
		-- single journal: sort by entry date (postTime)				
		insert into @staging						
		select userid,postid,posttime,
		ROW_NUMBER() OVER (
			ORDER BY 
				CASE WHEN @SortOrder = 1 THEN postTime END ASC,
				CASE WHEN @SortOrder = 2 THEN postTime END DESC			
		) AS RowNum,anon_permissionflags,user_permissionflags,default_PermissionFlags
		from
			(SELECT userId, postId, postTime,
						isnull(jetu_anon.OwnershipFlags,@permissionFlagDefault) as [anon_PermissionFlags],
						isnull(jetu_user.OwnershipFlags,@permissionFlagDefault) as [user_PermissionFlags],
						isnull(jj.DefaultEntryPermissionFlags,@permissionFlagDefault) as [default_PermissionFlags]
			FROM journal_post_listing jpl WITH (nolock) 				
				inner join @filter_journals fj on fj.obj=jpl.postjournalobjid
				left outer join jrnl_entries_to_users jetu_anon with(nolock) on jetu_anon.EntryObjId=jpl.postid and jetu_anon.UserObjId=convert(uniqueidentifier,convert(binary,0))
				left outer join jrnl_entries_to_users jetu_user with(nolock) on jetu_user.EntryObjId=jpl.postid and jetu_user.UserObjId=@userobjid
				left outer join journal_journals jj with(nolock) on jj.JournalObjectId=jpl.postjournalobjid				
			WHERE 1=1			
				and (jpl.postTime between @StartDate and @EndDate) 		
				and ((jpl.postStatus|16)<>jpl.postStatus)	-- 16 is DELETED status
				and ((jpl.postStatus|32)<>jpl.postStatus)	-- 32 is DRAFT status
			) tmp
		where 1=1
 
			and (
					((tmp.anon_permissionflags|@permissionFlagView)=tmp.anon_permissionflags)
					or
					((tmp.user_permissionflags|@permissionFlagView)=tmp.user_permissionflags)
					or
					((tmp.default_permissionflags|@permissionFlagView)=tmp.default_permissionflags)
				);								
	end							
 
	-- Debug
	--select * from @staging
 
	-- Num Likes
	declare @tbl_Likes table (PostObjId uniqueidentifier, NumLikes int);		
	insert into @tbl_Likes	
	select s.postId,count(*) as [NumLikes]		
	from jrnl_likes jl with(nolock)
	inner join @staging s on jl.TargetEntityObjId=s.postId
	group by s.postId
 
	-- Num Comments
	declare @tbl_Comments table (PostObjId uniqueidentifier, NumComments int);		
	insert into @tbl_Comments			
	select s.postId,count(*) as [NumComments]		
	from journal_post_comments jpc with(nolock)
	inner join @staging s on jpc.EntryObjectId=s.PostId
	where jpc.CommentStatus=1 --16 is deleted
	group by s.postId			
 
	-- AttachmentType
	declare @tbl_Attachments table (PostObjId uniqueidentifier, AttachmentType int, AttachmentCount int);
	insert into @tbl_Attachments
	select jpf.postId, jpf.footnoteType,count(*) as AttachmentCount
	from journal_post_footnotes jpf with(nolock)
	inner join @staging s on jpf.postId=s.postId
	group by jpf.postId, jpf.footnoteType
 
 
	-- Return the posts matching filter, within the paging range
	SELECT @ResultsCount = COALESCE(MAX(RowNum), 0) FROM @staging;
	SELECT jpl.userId, jpl.postId, jpl.postJournalObjId,
		ISNULL(jpl.postSubject, N'') AS postSubject, isnull(s.postTime,getdate()) as [postTime], postCreated, isnull(jpt.postLastModified,jpl.postCreated) as [postLastModified],postStatus, postFlags, 
		ISNULL(postSummary, N'') AS postSummary,  isnull(postText,N'') as [postText], isnull(postTextPlain,N'') as [postTextPlain],
		isnull(postMood,0) as [postMood], isnull(mood_key, 'none') as postMoodKey, isnull(Mood_Name,'') as postMoodName,
		isnull(postAuthorObjId,convert(uniqueidentifier,convert(binary,0))) as [postAuthorObjId], 
		spo.Firstname as [AuthorFirstName],spo.LastName as [AuthorLastName],
		j.JournalTitle,j.JournalTypeFlags, s.anonPermissionFlags,s.userPermissionFlags, s.journalDefaultPermissionFlags,
		isnull(nl.NumLikes,0) as [LikesCount], isnull(nc.NumComments,0) as [CommentsCount], isnull(at.AttachmentType,0) as [AttachmentType], isnull(at.AttachmentCount,0) as [AttachmentCount]
	from @staging s
		inner join journal_post_listing jpl WITH (nolock) on  jpl.postId=s.postId
		inner join journal_posts jp with(nolock) on jpl.postId=jp.postId
		inner join aspnet_SQLProfileOptions spo with(nolock) on spo.userid=jpl.postAuthorObjId
		inner join journal_journals j with(nolock) on jpl.postJournalObjId=j.JournalObjectId
		left outer join journal_post_times jpt with(nolock) on jpl.postId=jpt.postId
		left outer join journal_moods WITH (nolock) on journal_moods.mood_id = postMood and mood_lang = @Culture				
		left outer join @tbl_Likes nl on nl.postObjId = s.postId
		left outer join @tbl_Comments nc on nc.postObjId=s.postId
		left outer join @tbl_Attachments at on at.PostObjId=s.PostId
	Where 1=1
		and s.RowNum between @FirstRecNum and @LastRecNum
	Order by s.RowNum	
EnD