?
Solved

stored procedure error

Posted on 2011-02-21
7
Medium Priority
?
272 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:tagomtech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 34943382
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
 

Author Comment

by:tagomtech
ID: 34943394
that is done as well as @orderby
EXEC (@report+@orderby)
0
 

Author Comment

by:tagomtech
ID: 34943453
I am not married to the IF statement if there is a better way to do this.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:rushShah
ID: 34943898
print your variable and check what sql statement, you are executing..

try Print (@report)  and post your query..
0
 
LVL 18

Accepted Solution

by:
deighton earned 1500 total points
ID: 34944872
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
 

Author Comment

by:tagomtech
ID: 34946080
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
 

Author Closing Comment

by:tagomtech
ID: 34947442
did not answer it exactly but query works
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question