stored procedure error

I have written a stored procedure that when executed - compiles when you pass parameters through it says executed but does not return anything
purpose:
pull reports that will fall between two variables
challenge - todays report may have began before today or may not end today
this is true for each time frame except week, month, year


SET @start = CAST(@startDate AS VARCHAR(100))
SET @end = CAST(@endDate AS VARCHAR(100))
DECLARE @report VARCHAR(4000),@orderby VARCHAR(100)

IF @start >= 'cMinStart' AND @end <= 'cMaxEnd'  -- All Dates
BEGIN
	SET @report = 'select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as ''Methods'',cMinStart,cMaxEnd,
	Active = case cPause when 1 then ''No'' when 0 then ''Yes'' end  ,csLabel,ctLabel
	from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
	WHERE cMinStart >= '''+ @start +''' AND  cMaxEnd <= '''+@end+''' AND cUtility = ''' + @utilityKey 
	SET @orderby = ' ORDER BY sDescription, cLabel,Active'
END
IF @start <= 'cMinStart' AND @end = 'cMaxEnd' OR @start = 'cMinStart' AND @end >= 'cMaxEnd' -- Campaings that either start today and continue past today or start earlier and end today 
BEGIN
	SET @report = 'select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as ''Methods'',cMinStart,cMaxEnd,
	Active = case cPause when 1 then ''No'' when 0 then ''Yes'' end  ,csLabel,ctLabel
	from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
	WHERE (cMinStart <= '''+ @start +''' AND  cMaxEnd = '''+@end+''') OR (cMinStart = '''+ @start +''' AND  cMaxEnd >= '''+@end+''') AND cUtility = ''' + @utilityKey 
	SET @orderby = ' ORDER BY sDescription, cLabel,Active'
END
IF @start = 'cMinStart' AND @end = 'cMaxEnd' -- campaigns start and end on exact dates such as week, 
BEGIN
	SET @report = 'select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as ''Methods'',cMinStart,cMaxEnd,
	Active = case cPause when 1 then ''No'' when 0 then ''Yes'' end  ,csLabel,ctLabel
	from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
	WHERE cMinStart cMinStart = '''+ @start +''' AND   cMaxEnd = '''+@end+''' AND cUtility = ''' + @utilityKey
	SET @orderby = ' ORDER BY sDescription, cLabel,Active'
END

Open in new window

tagomtechAsked:
Who is Participating?
 
deightonConnect With a Mentor progCommented:
you might be able to reduce the need for IF statements in the stored procedure by getting some of the logic into the WHERE clauses if you can

Also as far as I can see it is not necessary to build a SQL string as you are doing, it is nearly always preferable to execute a SQL command directly

e.g.

IF @start >= 'cMinStart' AND @end <= 'cMaxEnd'  -- All Dates
BEGIN
      select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as 'Methods',cMinStart,cMaxEnd,
      Active = case cPause when 1 then 'No' when 0 then 'Yes' end  ,csLabel,ctLabel
      from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
      WHERE cMinStart >=  @start  AND  cMaxEnd=  @end  AND cUtility =  @utilityKey
       ORDER BY sDescription, cLabel,Active
END


that sort of idea,
0
 
deightonprogCommented:
finish it up with

EXEC(@report)

e.g. end of query

      Active = case cPause when 1 then ''No'' when 0 then ''Yes'' end  ,csLabel,ctLabel
      from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
      WHERE cMinStart cMinStart = '''+ @start +''' AND   cMaxEnd = '''+@end+''' AND cUtility = ''' + @utilityKey
      SET @orderby = ' ORDER BY sDescription, cLabel,Active'
END
EXEC(@report)
0
 
tagomtechAuthor Commented:
that is done as well as @orderby
EXEC (@report+@orderby)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
tagomtechAuthor Commented:
I am not married to the IF statement if there is a better way to do this.
0
 
rushShahCommented:
print your variable and check what sql statement, you are executing..

try Print (@report)  and post your query..
0
 
tagomtechAuthor Commented:
This might help explain a little better - additionally I would like to do this with OR and/or NOT instead of IF or CASE

Possible scenarios
see attached image

query using
select DISTINCT cKey,sDescription,cApplication,cUtility,cPriority,cContactSequence,cLabel,cTemplate,dbo.concatContactMethodNames(ctEnabledMethods) as ''Methods'',cMinStart,cMaxEnd,
      Active = case cPause when 1 then ''No'' when 0 then ''Yes'' end  ,csLabel,ctLabel
      from campaign inner join contactSequence on cContactSequence = csKey left outer join campaignTemplate on cTemplate = ctKey inner join suite on ctSuite = sEnum
      WHERE cMinStart = '''+ @start +''' AND cMinStart <= '''+ @end +''' AND cMaxEnd <= '''+@end+'''
      OR cMinStart = '''+ @start +''' AND cMinStart <= '''+ @end +''' AND cMaxEnd >= '''+@end+'''
      OR cMinStart <= '''+ @start +''' AND cMinStart <= '''+ @end +''' AND cMaxEnd >= '''+@end+'''
      OR cMinStart <= '''+ @start +''' AND cMinStart <= '''+ @end +''' AND cMaxEnd <= '''+@end+'''
      OR cMinStart  >= '''+ @start +''' AND cMaxEnd <= '''+@end+'''
possiblescenarios.PNG
0
 
tagomtechAuthor Commented:
did not answer it exactly but query works
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.