?
Solved

stored procedure error

Posted on 2011-02-21
7
Medium Priority
?
274 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

809 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