Solved

stored procedure error

Posted on 2011-02-21
7
265 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

911 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now