Pass where clause to stored procedure

I'm trying to creat a stored procedure to wich I ca pass a long string that is a WHERE clause, e.g., "dbo.tbl_PS_Scorecard.PricingReceiptDate>='01/01/2013' And dbo.tbl_PS_Scorecard.PricingReceiptDate<='10/13/2013'" which would the value of the variable @WHERE_Date@.

When I run CREATE PROCEDURE I get an error:
An expression of non-boolean type specified in a context where a condition is expected

How do I pass that lon string to the query code?

Here's the code:

CREATE PROCEDURE [dbo].[sp_PS_Load_EvaluationPerformanceReportTmptest]
      -- Add the parameters for the stored procedure here
      @WHERE_Date@ AS nvarchar(200)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      --SET NOCOUNT ON;
DELETE dbo.tbl_PS_EvaluationPerformanceReportTmp
FROM dbo.tbl_PS_EvaluationPerformanceReportTmp
;      
WITH ReportData (Associate, NumberOfEvaluations, Score)
AS

(SELECT [Last] + ', ' + [First] AS Associate,

ISNull((SELECT Count(AssociateID)
FROM dbo.tbl_PS_Scorecard
GROUP BY dbo.tbl_PS_Scorecard.AssociateID
HAVING dbo.tbl_PS_Scorecard.AssociateID=a.AssociateID),0) AS NumbrOfEvaluations,

ISNull((SELECT
 Sum(dbo.tbl_PS_TransactionLevelComboRowSources.Points)
FROM dbo.tbl_PS_Associates
LEFT JOIN ((dbo.tbl_PS_TransactionLevel
LEFT JOIN dbo.tbl_PS_TransactionLevelComboRowSources
ON dbo.tbl_PS_TransactionLevel.TransactionLevelAnswerID
= dbo.tbl_PS_TransactionLevelComboRowSources.TransactionLevelAnswerID)
LEFT JOIN dbo.tbl_PS_Scorecard
ON dbo.tbl_PS_TransactionLevel.ScoreCardID = dbo.tbl_PS_Scorecard.ScorecardID)
ON dbo.tbl_PS_Associates.AssociateID = dbo.tbl_PS_Scorecard.AssociateID
WHERE dbo.tbl_PS_Scorecard.AssociateID=a.AssociateID AND @WHERE_Date@
GROUP BY dbo.tbl_PS_Associates.AssociateID),0) AS Score

FROM dbo.tbl_PS_Associates a)

INSERT INTO dbo.tbl_PS_EvaluationPerformanceReportTmp
( Associate, NumberOfEvaluations)

SELECT Associate, NumberOfEvaluations FROM ReportData

END



GO
paulmcneilAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
Why don't you just use 2 date parameters:

CREATE PROCEDURE [dbo].[Sp_ps_load_evaluationperformancereporttmptest] 
  -- Add the parameters for the stored procedure here 
 @startdate varchar(25)=null,
 @enddate varchar(25)=null
AS 
  BEGIN 
      -- SET NOCOUNT ON added to prevent extra result sets from 
      -- interfering with SELECT statements. 
      --SET NOCOUNT ON; 
      DELETE dbo.tbl_ps_evaluationperformancereporttmp 
      FROM   dbo.tbl_ps_evaluationperformancereporttmp; 

      WITH reportdata (associate, numberofevaluations, score) 
           AS (SELECT [last] + ', ' + [first] 
                      AS 
                         Associate, 
                      Isnull((SELECT Count(associateid) 
                              FROM   dbo.tbl_ps_scorecard 
                              GROUP  BY dbo.tbl_ps_scorecard.associateid 
                              HAVING 
                      dbo.tbl_ps_scorecard.associateid = a.associateid), 0) 
                      AS 
                      NumbrOfEvaluations, 
                      Isnull((SELECT Sum( 
                             dbo.tbl_ps_transactionlevelcomborowsources.points) 
                              FROM   dbo.tbl_ps_associates 
                                     LEFT JOIN ((dbo.tbl_ps_transactionlevel 
                      LEFT JOIN dbo.tbl_ps_transactionlevelcomborowsources 
                             ON 
                      dbo.tbl_ps_transactionlevel.transactionlevelanswerid 
                      = 
           dbo.tbl_ps_transactionlevelcomborowsources.transactionlevelanswerid) 
                      LEFT JOIN dbo.tbl_ps_scorecard 
                             ON dbo.tbl_ps_transactionlevel.scorecardid = 
                                dbo.tbl_ps_scorecard.scorecardid) 
                  ON dbo.tbl_ps_associates.associateid = 
                     dbo.tbl_ps_scorecard.associateid 
                   WHERE  dbo.tbl_ps_scorecard.associateid = a.associateid 
                          AND
						  (dbo.tbl_PS_Scorecard.PricingReceiptDate>=@startdate
							and dbo.tbl_PS_Scorecard.PricingReceiptDate<=@enddate
							OR 
							@startdate is null and @enddate is null)

                   GROUP  BY dbo.tbl_ps_associates.associateid), 0) 
           AS 
              Score 
               FROM   dbo.tbl_ps_associates a) 
      INSERT INTO dbo.tbl_ps_evaluationperformancereporttmp 
                  (associate, 
                   numberofevaluations) 
      SELECT associate, 
             numberofevaluations 
      FROM   reportdata 
  END 

Open in new window

0
 
Brian CroweDatabase AdministratorCommented:
You would need to use dynamic SQL or change your approach entirely.

DECLARE @SQL NVARCHAR(4000);

SET @SQL = 'SELECT [Last] + '', '' + [First] AS Associate,

ISNull((SELECT Count(AssociateID)
FROM dbo.tbl_PS_Scorecard
GROUP BY dbo.tbl_PS_Scorecard.AssociateID
HAVING dbo.tbl_PS_Scorecard.AssociateID=a.AssociateID),0) AS NumbrOfEvaluations,

ISNull((SELECT
 Sum(dbo.tbl_PS_TransactionLevelComboRowSources.Points)
FROM dbo.tbl_PS_Associates
LEFT JOIN ((dbo.tbl_PS_TransactionLevel
LEFT JOIN dbo.tbl_PS_TransactionLevelComboRowSources
ON dbo.tbl_PS_TransactionLevel.TransactionLevelAnswerID
= dbo.tbl_PS_TransactionLevelComboRowSources.TransactionLevelAnswerID)
LEFT JOIN dbo.tbl_PS_Scorecard
ON dbo.tbl_PS_TransactionLevel.ScoreCardID = dbo.tbl_PS_Scorecard.ScorecardID)
ON dbo.tbl_PS_Associates.AssociateID = dbo.tbl_PS_Scorecard.AssociateID
WHERE dbo.tbl_PS_Scorecard.AssociateID=a.AssociateID AND ' + @WHERE_Date@ +  ' GROUP BY dbo.tbl_PS_Associates.AssociateID),0) AS Score
FROM dbo.tbl_PS_Associates a'

EXECUTE sp_executesql @SQL
0
 
paulmcneilAuthor Commented:
Thanks BriCrowe. My stored procedure needs to do 2 things:

1. DELETE all the records from  dbo.tbl_PS_EvaluationPerformanceReportTmp
2. INSERT INTO dbo.tbl_PS_EvaluationPerformanceReportTmp by selecting from the CTE

WITH ReportData (Associate, NumberOfEvaluations, Score)
AS

(SELECT [Last] + ', ' + [First] AS Associate,

ISNull((SELECT Count(AssociateID)
FROM dbo.tbl_PS_Scorecard
GROUP BY dbo.tbl_PS_Scorecard.AssociateID
HAVING dbo.tbl_PS_Scorecard.AssociateID=a.AssociateID),0) AS NumbrOfEvaluations,

ISNull((SELECT
 Sum(dbo.tbl_PS_TransactionLevelComboRowSources.Points)
FROM dbo.tbl_PS_Associates
LEFT JOIN ((dbo.tbl_PS_TransactionLevel
LEFT JOIN dbo.tbl_PS_TransactionLevelComboRowSources
ON dbo.tbl_PS_TransactionLevel.TransactionLevelAnswerID
= dbo.tbl_PS_TransactionLevelComboRowSources.TransactionLevelAnswerID)
LEFT JOIN dbo.tbl_PS_Scorecard
ON dbo.tbl_PS_TransactionLevel.ScoreCardID = dbo.tbl_PS_Scorecard.ScorecardID)
ON dbo.tbl_PS_Associates.AssociateID = dbo.tbl_PS_Scorecard.AssociateID
WHERE dbo.tbl_PS_Scorecard.AssociateID=a.AssociateID AND @WHERE_Date@
GROUP BY dbo.tbl_PS_Associates.AssociateID),0) AS Score

FROM dbo.tbl_PS_Associates a)

Your code does not include the CTE which I use to do the INSERT with the final lines:

INSERT INTO dbo.tbl_PS_EvaluationPerformanceReportTmp
( Associate, NumberOfEvaluations)

SELECT Associate, NumberOfEvaluations FROM ReportData
0
 
Brian CroweDatabase AdministratorCommented:
The code was so strangely formatted that I completely missed the cte.  The concept still holds though...

DELETE dbo.tbl_PS_EvaluationPerformanceReportTmp 
FROM dbo.tbl_PS_EvaluationPerformanceReportTmp;

DECLARE @SQL NVARCHAR(4000);

SET @SQL = '
WITH ReportData (Associate, NumberOfEvaluations, Score)
AS
(
	SELECT [Last] + '', '' + [First] AS Associate,
		ISNull((
			SELECT Count(AssociateID)
			FROM dbo.tbl_PS_Scorecard
			GROUP BY dbo.tbl_PS_Scorecard.AssociateID
			HAVING dbo.tbl_PS_Scorecard.AssociateID = a.AssociateID), 0) AS NumbrOfEvaluations,
		ISNull((
			SELECT Sum(dbo.tbl_PS_TransactionLevelComboRowSources.Points)
			FROM dbo.tbl_PS_Associates 
			LEFT JOIN ((dbo.tbl_PS_TransactionLevel 
				LEFT JOIN dbo.tbl_PS_TransactionLevelComboRowSources 
					ON dbo.tbl_PS_TransactionLevel.TransactionLevelAnswerID = dbo.tbl_PS_TransactionLevelComboRowSources.TransactionLevelAnswerID) 
				LEFT JOIN dbo.tbl_PS_Scorecard
					ON dbo.tbl_PS_TransactionLevel.ScoreCardID = dbo.tbl_PS_Scorecard.ScorecardID) 
				ON dbo.tbl_PS_Associates.AssociateID = dbo.tbl_PS_Scorecard.AssociateID
			WHERE dbo.tbl_PS_Scorecard.AssociateID=a.AssociateID AND ' + @WHERE_Date@ +
			'GROUP BY dbo.tbl_PS_Associates.AssociateID), 0) AS Score
	FROM dbo.tbl_PS_Associates AS a
)
INSERT INTO dbo.tbl_PS_EvaluationPerformanceReportTmp
(
	Associate,
	NumberOfEvaluations
)
SELECT Associate,
	NumberOfEvaluations
FROM ReportData'

EXECUTE sp_executeSQL @SQL

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.