Attached is my stored procedure. I've tried your suggestions but am having no luck.
I appreciate your help.
Thanks - Tom
-----Original Message-----
From: david.grant [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 19, 2001 5:38 PM
To: [EMAIL PROTECTED]
Subject: RE: stored procs
Ahh, gotcha. To pass a date from CF to an SP, you've got to do some
rejinkulous maneuvering.
Check it:
WHERE hours.theDate >= {d ''' + convert(varchar,@firstOfWeek) + '''}
AND hours.theDate <= {d ''' + convert(varchar,@lastOfWeek) + '''}
Pass the date in as a varchar with YYYY-MM-DD format then slap it into
the query as shown above, using the {d 'YYYY-MM-DD'} sytax.
David
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On
Behalf Of Schreck, Tom
Sent: Wednesday, December 19, 2001 5:18 PM
To: [EMAIL PROTECTED]
Subject: RE: stored procs
I have a specific date I need to pass into the stored proc and use as a
filter. I've tried createodbcdate, dateformat, as well as changing the
data type of variable in stored proced but the stored proc keeps
breaking.
Tom
-----Original Message-----
From: david.grant [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 19, 2001 5:24 PM
To: [EMAIL PROTECTED]
Subject: RE: stored procs
Use getDate() in the SQL.
david
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On
Behalf Of Schreck, Tom
Sent: Wednesday, December 19, 2001 5:10 PM
To: [EMAIL PROTECTED]
Subject: RE: stored procs
how do you pass a date into a stored proc?
Thanks - Tom
-----Original Message-----
From: BILLY CRAVENS [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, December 19, 2001 3:37 PM
To: [EMAIL PROTECTED]
Subject: Re: stored procs
Actually, I was answering Schreck's question about speed in inline SQL
vs.
stored procedures. The difference between the two is smaller on simple
queries than on complex ones.
I would agree that dynamically evaluating SQL is expensive - however,
there's situations where it's preferable to having many stored procs
(like a
search engine based on an infinite number of variable criteria).
---
Billy Cravens
----- Original Message -----
From: "Dave Cahall" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, December 19, 2001 2:51 PM
Subject: RE: stored procs
> I do not understand your first sentence. It seems to me that you are
saying
> simple queries take more time than complex ones and I do not think
that is
> what you mean.
>
> If you are saying that the simpler the query, the less performance
gain
for
> stored procedures versus sending the select statement. I would agree.
> However, any time you can call a simple query (stored procedure or
not)
the
> query will run faster and stored procedures (regardless of their
complexity)
> will always run faster than the same query if you send the SQL
statements
> via a string.
>
> My original statement was intended to mean that you can let your
ColdFusion
> determine which stored procedure to call and that would be faster than
> having the if logic inside a complex stored procedure. Again,
according to
> the book I read, decision logic in the database is not very efficient.
So
> calling one of several small stored procedures is more efficient than
> sending a bunch of parameters to a single stored procedure and having
to
> parse through the logic inside the stored procedure. The primary
advantage
> of stored procedures (according to my understanding) is to gain the
speed
of
> having queries precompiled. When the database has to parse the SQL and
make
> decisions it slows down the processor significantly (again according
to
what
> I have read).
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of BILLY CRAVENS
> Sent: Wednesday, December 19, 2001 2:28 PM
> To: [EMAIL PROTECTED]
> Subject: Re: stored procs
>
>
> I've found that the simpler the query, the less of a performance gain.
> Reason being is the determination of the execution plan - in SP's this
is
> pre-compiled, but in inline SQL, it has to be calculated each time
> (actually, ODBC connection pooling should keep this from happening,
but
> we'll assume that a query is executed each time it is called). On
queries
> with larger joins, the execution plan is more complex and takes more
time
to
> generate, thus the gain in speed.
>
> ---
> Billy Cravens
>
> ----- Original Message -----
> From: "Schreck, Tom" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Wednesday, December 19, 2001 1:40 PM
> Subject: RE: stored procs
>
>
> I found a solution on Google. Here's the url:
>
> http://www.sqlteam.com/item.asp?ItemID=2077
>
> There's a function called COALESCE which picks the first non-null
value in
a
> comma delimeted list. Check out the examples in the above link on how
to
> apply this for dynamic WHERE clauses. My biggest hangup to using
stored
> procs has been the inability to create dynamic WHERE clauses, so now
I'll
> start incorporating them into my development. Has anyone used
COALESCE
> function as described above? Will this degrade performance? I prefer
not
> to have several different stored procs versus putting the logic into 1
file.
>
> Does anyone know the performance gains from stored procs versus using
a CF
> query?
>
> Thanks for your help - Tom
>
> -----Original Message-----
> From: Hinojosa, Robert A [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, December 19, 2001 1:28 PM
> To: '[EMAIL PROTECTED]'
> Subject: RE: stored procs
>
>
> You may want to consider building the sql as a string and then use
exec to
> execute the statement. Something like this.
>
> SELECT @sqlstring = "SELECT * FROM <tablename> WHERE <fieldname> " +
> @dynamicWhereClause + " IN (" +@parameter = ")"
>
> EXEC(@sqlstring)
>
> HTH,
>
> Robert Hinojosa
> [EMAIL PROTECTED]
> 972.243.4343 x7446
>
>
> -----Original Message-----
> From: Schreck, Tom [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, December 19, 2001 11:58 AM
> To: [EMAIL PROTECTED]
> Subject: stored procs
>
>
> I'm trying to create a stored procedure that accepts any combination
of 5
> parameters and filters a recordset. I'm applying defaults to the
parameters
> so I know if one of the parameters matches the default then I do not
include
> that parameter as part of the filter. Here's a snippet:
>
> DECLARE @numContentTypeID int,@numWorkFlowStateID int,@numTaxonomyID
int,
> @dtStart datetime, @dtEnd datetime
>
> SELECT @numContentTypeID = 0
> SELECT @numWorkFlowStateID = 0
> SELECT @numTaxonomyID = 7
> SELECT @dtStart = getDate()
> SELECT @dtEnd = dateadd(month,1,getdate())
>
> --PRINT @dtStart
> --PRINT @dtEnd
>
> --set top 3 content
> SELECT TOP 3 numContentID, numHitCount
> FROM tblContent
> WHERE 0=0
> if @numContentTypeID > 0
> AND tblContent.numContentTypeID = @numContentTypeID
> if @numWorkFlowStateID > 0
> AND tblContent.numWorkFlowStateID = @numWorkFlowStateID
> if @numTaxonomyID > 0
> AND tblContent.numContentID IN(
> SELECT tblTaxonomyContent.numContentID
> FROM tblTaxonomyContent
> WHERE (tblTaxonomyContent.numTaxonomyID =
> @numTaxonomyID)
> )
> ORDER BY numHitCount DESC
>
> Am I missing the syntax on how to dynamically create the WHERE clause?
> Could it be that stored procedures can not create dynamic WHERE
clauses?
If
> so, then do you have to make up a huge if statement block to try to
> determine all possible permutations of 5 different parameters? If
stored
> procs do not allow for dynamic WHERE clauses, then this seems to be a
very
> serious week point for using stored procs. What are your thoughts?
>
> Thanks - Tom
>
>
------------------------------------------------------------------------
-
> This email server is running an evaluation copy of the MailShield
anti-
> spam software. Please contact your email administrator if you have any
> questions about this message. MailShield product info:
www.mailshield.com
>
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
>
>
------------------------------------------------------------------------
-
> This email server is running an evaluation copy of the MailShield
anti-
> spam software. Please contact your email administrator if you have any
> questions about this message. MailShield product info:
www.mailshield.com
>
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
>
>
------------------------------------------------------------------------
-
> This email server is running an evaluation copy of the MailShield
anti-
> spam software. Please contact your email administrator if you have any
> questions about this message. MailShield product info:
www.mailshield.com
>
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
>
>
>
------------------------------------------------------------------------
-
> This email server is running an evaluation copy of the MailShield
anti-
> spam software. Please contact your email administrator if you have any
> questions about this message. MailShield product info:
www.mailshield.com
>
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
>
>
>
------------------------------------------------------------------------
-
> This email server is running an evaluation copy of the MailShield
anti-
> spam software. Please contact your email administrator if you have any
> questions about this message. MailShield product info:
www.mailshield.com
>
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
>
------------------------------------------------------------------------
-
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info:
www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
------------------------------------------------------------------------
-
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info:
www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
------------------------------------------------------------------------
-
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info:
www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
------------------------------------------------------------------------
-
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info:
www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
/****** Object: Stored Procedure dbo.sp_Top3RandomContent Script Date: 12/18/2001
******/
/********************************************************************************************
Procedure Name: sp_Top3RandomContent
Created By: Tom Schreck
Date Created: 12/18/2001
Parameters:
numContentTypeID,numWorkFlowStateID,numTaxonomyID,dtStart,dtEnd
Abstract: Picks top 3 content based on hit count then randomizes rest
Revisions:
**********************************************************************************************/
CREATE Procedure sp_Top3RandomContent
@numContentTypeID int = 0
,@numWorkFlowStateID int = 0
,@numTaxonomyID int = 0
,@dtStart varchar(20) = 0
,@dtEnd varchar(20) = 0
AS
Set nocount on
--DECLARE @numContentTypeID int,@numWorkFlowStateID int,@numTaxonomyID int, @dtStart
varchar, @dtEnd varchar
DECLARE @SQL varchar(2000),@WHERECLAUSE varchar(2000),@SQL1
varchar(2000),@WHERECLAUSE1 varchar(2000)
--SELECT @numContentTypeID = 14
--SELECT @numWorkFlowStateID = 371
--SELECT @numTaxonomyID = 51
--SELECT @dtStart = 12/20/2001
--SELECT @dtEnd = 12/18/2001
CREATE TABLE #tempTableTop3
(
numContentID int
,numContentTypeID int
,txtContentType varchar(100)
,numWorkFlowStateID int
,txtContent text
,numSubmittedByID int
,numOwnerID int
,numManagerID int
,numLastModifiedByID int
,dtLastModified datetime
,numHitCount int
,Random uniqueidentifier
,ID int IDENTITY(1,1)
)
CREATE TABLE #tempTableRandom
(
numContentID int
,numContentTypeID int
,txtContentType varchar(100)
,numWorkFlowStateID int
,txtContent text
,numSubmittedByID int
,numOwnerID int
,numManagerID int
,numLastModifiedByID int
,dtLastModified datetime
,numHitCount int
,Random uniqueidentifier
,ID int IDENTITY(1,1)
)
--SELECT TOP 3 tblContent.numContentID, tblContent.numContentTypeID,
tblContentType.txtContentType, tblContent.numWorkFlowStateID, tblContent.txtContent,
tblContent.numSubmittedByID, tblContent.numOwnerID, Owner.numManagerID,
tblContent.numLastModifiedByID, tblContent.dtLastModified, tblContent.numHitCount,
NEWID() AS Random FROM tblContent INNER JOIN tblContentType ON
tblContent.numContentTypeID = tblContentType.numContentTypeID LEFT OUTER JOIN
tblPersonnel Owner ON tblContent.numOwnerID = Owner.numPersonnelID
--set top 3 content into temp table
SET @SQL = 'INSERT INTO #tempTableTop3(numContentID ,numContentTypeID ,txtContentType
,numWorkFlowStateID ,txtContent ,numSubmittedByID ,numOwnerID ,numManagerID
,numLastModifiedByID ,dtLastModified ,numHitCount, Random)
SELECT TOP 3 tblContent.numContentID, tblContent.numContentTypeID,
tblContentType.txtContentType, tblContent.numWorkFlowStateID, tblContent.txtContent,
tblContent.numSubmittedByID, tblContent.numOwnerID, Owner.numManagerID,
tblContent.numLastModifiedByID, tblContent.dtLastModified, tblContent.numHitCount,
NEWID() AS Random FROM tblContent INNER JOIN tblContentType ON
tblContent.numContentTypeID = tblContentType.numContentTypeID LEFT OUTER JOIN
tblPersonnel Owner ON tblContent.numOwnerID = Owner.numPersonnelID'
SET @WHERECLAUSE = ' WHERE 0=0 '
if @numContentTypeID > 0
SET @WHERECLAUSE = @WHERECLAUSE + ' AND tblContent.numContentTypeID = ' +
CAST(@numContentTypeID as varchar)
if @numWorkFlowStateID > 0
SET @WHERECLAUSE = @WHERECLAUSE + ' AND tblContent.numWorkFlowStateID = ' +
CAST(@numWorkFlowStateID as varchar)
if @numTaxonomyID > 0
SET @WHERECLAUSE = @WHERECLAUSE + ' AND tblContent.numContentID IN(
SELECT numContentID
FROM tblTaxonomyContent
WHERE (numTaxonomyID =' + CAST(@numTaxonomyID as varchar) +'))'
if @dtStart > 0
SET @WHERECLAUSE = @WHERECLAUSE + ' AND tblContent.dtStart < ' + CAST(@dtStart
as datetime)
if @dtEnd > 0
SET @WHERECLAUSE = @WHERECLAUSE + ' AND ((tblContent.dtEnd > '+CAST(@dtEnd as
datetime)+') OR (tblContent.dtEnd IS Null))'
SET @SQL = @SQL + '' + @WHERECLAUSE+ ' ORDER BY numHitCount DESC'
--PRINT @SQL
EXEC(@SQL)
--display results of top3
--SELECT * FROM #tempTableTop3
--setup a temp table with a GUID dynamically assigned to each row
SET @SQL1 = 'INSERT INTO #tempTableRandom(numContentID ,numContentTypeID
,txtContentType ,numWorkFlowStateID ,txtContent ,numSubmittedByID ,numOwnerID
,numManagerID ,numLastModifiedByID ,dtLastModified ,numHitCount, Random)
SELECT tblContent.numContentID, tblContent.numContentTypeID,
tblContentType.txtContentType, tblContent.numWorkFlowStateID, tblContent.txtContent,
tblContent.numSubmittedByID, tblContent.numOwnerID, Owner.numManagerID,
tblContent.numLastModifiedByID, tblContent.dtLastModified, tblContent.numHitCount,
NEWID() AS Random FROM tblContent INNER JOIN tblContentType ON
tblContent.numContentTypeID = tblContentType.numContentTypeID LEFT OUTER JOIN
tblPersonnel Owner ON tblContent.numOwnerID = Owner.numPersonnelID'
SET @WHERECLAUSE1 = ' WHERE 0=0 AND numContentID Not In(Select numContentID FROM
#tempTableTop3) '
if @numContentTypeID > 0
SET @WHERECLAUSE1 = @WHERECLAUSE1 + ' AND tblContent.numContentTypeID = ' +
CAST(@numContentTypeID as varchar)
if @numWorkFlowStateID > 0
SET @WHERECLAUSE1 = @WHERECLAUSE1 + ' AND tblContent.numWorkFlowStateID = ' +
CAST(@numWorkFlowStateID as varchar)
if @numTaxonomyID > 0
SET @WHERECLAUSE1 = @WHERECLAUSE1 + ' AND tblContent.numContentID IN(
SELECT numContentID
FROM tblTaxonomyContent
WHERE (numTaxonomyID =' + CAST(@numTaxonomyID as varchar) +'))'
if @dtStart > 0
SET @WHERECLAUSE1 = @WHERECLAUSE1 + ' AND tblContent.dtStart < ' +
CAST(@dtStart as datetime)
if @dtEnd > 0
SET @WHERECLAUSE1 = @WHERECLAUSE1 + ' AND ((tblContent.dtEnd > '+CAST(@dtEnd
as datetime)+') OR (tblContent.dtEnd IS Null))'
SET @SQL1 = @SQL1 + '' + @WHERECLAUSE1 + ' ORDER BY Random'
--PRINT @SQL1
EXEC(@SQL1)
--display results of Random
--SELECT * FROM #tempTableRandom
SELECT * FROM #tempTableTop3
UNION ALL
SELECT * FROM #tempTableRandom
--Cleanup
drop table #tempTableTop3
drop table #tempTableRandom
go
