****************************************************************************
A Simple Insert If Not Exists Query
****************************************************************************
IF NOT EXISTS (select null from _Media_List WITH (NOLOCK)
where MediaURL = '#trim(listfirst(Arguments.MediaURL,"&"))#')
BEGIN
INSERT INTO _Media_List
(UserID, Channel, Title, MediaURL,MediaType, NextMedia, Favorite, PublishedAt, Duration, thumbUrl, thumbWidth, thumbHeight, Displayname)
SELECT
#Arguments.UserID#, '#trim(Arguments.Channel)#', '#trim(Arguments.Title)#', '#trim(listfirst(Arguments.MediaURL,"&"))#',
'#Arguments.MediaType#', '#Arguments.NextMedia#', '#Arguments.Favorite#',
'#DateFormat(Arguments.PublishedAt)# #TimeFormat(Arguments.PublishedAt)#', '#Arguments.Duration#',
'#trim(Arguments.thumbUrl)#', #trim(Arguments.thumbWidth)#, #trim(Arguments.thumbHeight)#, Displayname
FROM _WebsiteUsers
WHERE UserID = #Arguments.UserID#

SELECT MediaID, MediaType, Title, Channel, 'success' as Status
FROM _Media_List WITH (NOLOCK)
WHERE MediaID = Scope_Identity()
END
ELSE
BEGIN
SELECT MediaID, MediaType, 'That Media URL already exists' as Status
FROM _Media_List WITH (NOLOCK)
WHERE MediaURL = '#trim(listfirst(Arguments.MediaURL,"&"))#'
END

****************************************************************************
A Pagination Query
****************************************************************************

SET NOCOUNT ON;
DECLARE @Page INT = #Arguments.Page#, @Size INT = #Arguments.Size#, @MediaType = '#Arguments.MediaType#';
WITH cte (MediaID,Count)
AS (
SELECT MediaID, count(MediaID) Over()
FROM _Media_List WITH (NOLOCK)
WHERE (MediaType = @MediaType) AND (EmailDate IS NULL)
ORDER BY NextMedia desc, MediaID DESC OFFSET ((@Page - 1) * @Size) ROWS FETCH NEXT @Size ROWS ONLY
)
SELECT m.MediaID, m.Channel, m.Title, m.MediaURL, m.MediaType, m.Duration, m.PublishedAt,
m.NextMedia, m.ActiveMedia, m.EmailDate, m.UserID, m.DateAdded, c2.Count
FROM _Media_List m WITH (NOLOCK)
INNER JOIN cte AS c2 ON c2.MediaID = m.MediaID;

****************************************************************************
A Query That Inserts Events Into A Calendar Table And Also Merges Recurring Events Into Related Table
****************************************************************************
DECLARE @cID INT, @rID INT;
SET NOCOUNT ON;

BEGIN TRANSACTION
INSERT INTO _Calendar_Events
(eventName,eColor,tColor,UserID,eventText,startDate,endDate,RecurUnit,RecurEvery,PickDays,WeekInMonth,eventType,private)
VALUES
(
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(Arguments.eventname)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(Arguments.eColor)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#trim(Arguments.tColor)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.UserID#">,
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#trim(Arguments.eventText)#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DateFormat(Arguments.StartDate)# #Arguments.StartTime#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#DateFormat(Arguments.EndDate)# #Arguments.EndTime#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.recurUnit#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.recurEvery#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.pickDays#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.WeekinMonth#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.eventType#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.private#">
)

SELECT @cID = SCOPE_IDENTITY();

If(OBJECT_ID('tempdb..##SRCCTE') Is Not Null)
Begin
Drop Table ##SRCCTE
End
CREATE TABLE ##SRCCTE(
[cID] [int] NOT NULL,
[eventName] [varchar](150) NOT NULL,
[eColor] [varchar] (10) NOT NULL,
[tColor] [varchar] (10) NOT NULL,
[eventText] [varchar](max) NOT NULL,
[eventEnd] [datetime] NOT NULL,
[eventStart] [datetime] NOT NULL
);

WITH SRCcte AS
(
<cfif Arguments.eventType is 0>
SELECT cID, eventName, eColor, tColor, eventText, endDate as eventEnd, startDate as eventStart
FROM dbo._Calendar_Events WITH (NOLOCK)
WHERE cID = @cID

UNION ALL

SELECT i.cID, i.eventName, i.eColor, i.tColor, i.eventText, i.eventEnd, R.eventStart
FROM SRCcte AS i
CROSS APPLY
(
SELECT
CASE e.recurUnit
WHEN 'Day' THEN DATEADD(DAY, e.recurEvery, i.eventStart)
WHEN 'Week' THEN DATEADD(WEEK, e.recurEvery, i.eventStart)
WHEN 'Month' THEN DATEADD(MONTH, e.recurEvery, i.eventStart)
WHEN 'Year' THEN DATEADD(YEAR, e.recurEvery, i.eventStart)
END AS eventStart
FROM dbo._Calendar_Events e WITH (NOLOCK)
WHERE cID = i.cID
) AS R
WHERE R.eventStart <= i.eventEnd
<cfelse>
SELECT e.cid, e.eventname, e.eColor, e.tColor, e.eventText, d.cDate as eventEnd, d.cDate as eventStart
FROM _Calendar_Events e WITH (NOLOCK)
inner join _Calendar_Dates d WITH (NOLOCK) on d.cDate BETWEEN Convert(date,e.startDate) AND Convert(date,e.endDate)
AND (
(e.WeekInMonth = '') or (e.WeekInMonth != '' and d.week_in_month IN (Select value from dbo.ParmsToList(e.WeekInMonth,',')))
OR
(CHARINDEX ('5',e.WeekInMonth) > 0 and d.last_week_in_month = 1)
)
AND (
(e.PickDays = '') or (e.PickDays != '' and d.day_in_week IN (Select value from dbo.ParmsToList(e.PickDays,',')))
)
WHERE e.cID = @cID
</cfif>
)

INSERT INTO ##SRCCTE
(cID, eventName, eColor, tColor, eventText, eventStart, eventEnd)
SELECT Top (200) cID, eventName, eColor, tColor, eventText,
CONVERT(datetime, CONVERT(varchar(10),CONVERT(date,eventStart)) + ' #Arguments.StartTime#'),
CONVERT(datetime, CONVERT(varchar(10),CONVERT(date,eventStart)) + ' #Arguments.EndTime#')
FROM SRCcte
option (MaxRecursion 200)

;MERGE INTO _Calendar_Instance AS TGTtbl
USING ##SRCcte ON
TGTtbl.cID = ##SRCcte.cID
and TGTtbl.eventStart = ##SRCcte.eventStart
WHEN NOT MATCHED BY TARGET THEN
INSERT (cID, eventName, eColor, tColor, eventText, eventStart, eventEnd)
VALUES (cID, eventName, eColor, tColor, eventText, eventStart, eventEnd);

INSERT INTO _Calendar_Recipients
(cID, eventname, email)
SELECT cID, eventname, <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#Arguments.email#">
FROM _Calendar_Events WITH (NOLOCK)
WHERE cID = @cID

SELECT @rID = SCOPE_IDENTITY();

INSERT INTO _Calendar_Remval
(cID, remval, rID)
VALUES
(@cID, 0, @rID)

If(OBJECT_ID('tempdb..##SRCCTE') Is Not Null)
Begin
Drop Table ##SRCCTE
End

COMMIT TRANSACTION

*************************************************************
A Parent / Child Query
*************************************************************
;WITH catrecurse(CatID,ParentID,Catname,Sort,lev,Orderby)
as
(
SELECT CatID,ParentID,Catname,Sort,0 as lev,
case when len(Sort) = 1 then '0' else '' end + convert(varchar(max),sort) as Orderby
FROM BizCats WITH (NOLOCK)
WHERE ParentID = 0
AND (Sort > 0 or 1 = @editmode)

UNION ALL

SELECT c.CatID,c.ParentID,c.Catname,c.Sort,r.lev + 1,
convert(varchar(max),r.Orderby)
+
case when len(c.Sort) = 1 then '0' else '' end
+
convert(varchar(max),c.Sort) as Orderby
FROM BizCats c WITH (NOLOCK)
inner join catrecurse r on r.CatID = c.ParentID
WHERE (c.Sort > 0 OR 1 = @editmode)
AND (c.CatID IN
(
Select CatID
From BizDirCats bdc WITH (NOLOCK)
inner join BizDir bd WITH (NOLOCK) on bd.BizID = bdc.BizID
inner join BizCoverage bc WITH (NOLOCK) on bc.BizID = bd.BizID
Where bd.isPublished = 1
AND
(
len(@usercity) > 0 and bc.UserCity = @usercity
or
len(@usercity) = 0 and 1 = 1
)
AND
(
len(@state) > 0 and bc.State = @state
or
len(@state) = 0 and 1 = 1
)
AND
(
len(@country) > 0 and bc.Country = @country
or
len(@country) = 0 and 1 = 1
)
)
OR 1 = @editmode
)
)

Select CatID,ParentID,Catname,Sort,lev,Orderby
From catrecurse WITH (NOLOCK)
Where
(
ParentID IN (Select CatID from catrecurse)
or
CatID in (Select ParentID from catrecurse)
)
OR 1 = @editmode

Order by Orderby

****************************************************************************
An Auto Complete function using Sql Server and ColdFusion
****************************************************************************

<cfset variables.term="">
<cfloop list="#Arguments.Term#" index="ii" delimiters=", ">
<cfif len(ii) gte 3>
<cfset variables.Term=ListAppend(variables.Term,ii)>
</cfif>
</cfloop>
<cfset cnt=0>

<cfif listlen(variables.Term)>
<cfset zArray = ArrayNew(1) />
<cfquery name="x">
SET NOCOUNT ON;
Select Top (30)
MediaID, Channel, Title, MediaURL
From _Media_List WITH (NOLOCK)
WHERE
<cfloop list="#variables.Term#" index="ii">
<cfset cnt++>
CHARINDEX('#ii#', Channel) > 0
OR
CHARINDEX('#ii#', Title) > 0
OR
CHARINDEX('#ii#', MediaURL) > 0
<cfif variables.cnt lt listlen(variables.Term)>
OR
</cfif>
</cfloop>
ORDER BY Channel,Title
</cfquery>
<cfoutput query="x">
<cfset zStruct = StructNew() />
<cfset zStruct["Title"] = x.Title />
<cfset zStruct["MediaID"] = x.MediaID />
<cfset zStruct["Channel"] = x.Channel />
<cfset ArrayAppend(zArray,zStruct) />
</cfoutput>
<cfreturn serializeJSON(zArray) />
</cfif>

****************************************************************************
ColdFusion Alphabetized Product List
****************************************************************************

Here is a functionality to display the alphabet [ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z ] and link each letter to products that start with that letter.
Letters will only be linked if there is DB content that starts with that letter. If no content then letter is small and greyed out.
When you click a letter you are shown related content.

Note that behind the scenes is a cached cfc object (Application.miscel) and we query it's method getItems for our content.
We are not ever touching the database during this process and that is why we code in this fashion.
When the Application objects are created is the only time the database is accessed. It's important to understand this.

Note that this is just kindof an experiment and can be viewed from the PRODUCT LIST example on the top menu.

<cfset itemholder = structnew() />
<cfloop from="65" to="90" index="ii">
<cfset slet = Chr( ii ) />
<cfset itemholder[variables.slet]=Application.misc.getItems(slet=variables.slet,CatID=Application.CatID) />
<cfif itemholder[variables.slet].recordcount>
<a href="#variables.slet#" class="noanchor"><b>#variables.slet#</b></a>
<cfelse>
#variables.slet#
</cfif>
  
</cfloop>

<cfloop from="65" to="90" index="ii">
<cfset slet = Chr( ii ) />
<cfif itemholder[variables.slet].recordcount>
<div id="#variables.slet#" class="w500">
<b class="noanchor">#variables.slet#</b>
<br />
<cfset Q=itemholder[variables.slet]>
<cfloop query="Q">
<a href="" class="slicker" elem="Q#Q.CatID##Q.ItemID#">#Q.Catname# - #Q.Itemname#</a><br />
</cfloop>
</div>
</cfif>
</cfloop>