Solved

stored procedure error

Posted on 2011-02-21
7
268 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

713 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