[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 983
  • Last Modified:

If statement with Begin ... End in Sql

This isn't working the way I expect. If I test the select statement by itself, it works fine; but when I use the If statement with Begin...End then I get

Type    Value
Int32          0

How do I set it up to return either the first or the second select statement.
ALTER PROCEDURE [dbo].[GetInterviewsByDate] 

	@cYear int = 2011, 
	@Preparer varchar = 'All'
AS
BEGIN
	SET NOCOUNT ON;

If @Preparer = 'All'
	Begin
	SELECT ...
	End
Else
	Begin
	SELECT ...
	End
END

Open in new window

0
TadSter
Asked:
TadSter
  • 7
  • 3
3 Solutions
 
Jesus RodriguezIT ManagerCommented:
The way that you do it is the correct one. Try to put the condition between parenthesis to see if something happened

If (@Preparer = 'All')

If not chechk against the query and the parameters coincidence to see
0
 
BrandonGalderisiCommented:
Depending on how you access it, if you do something like SET FMT_ONLY it will not be able to distinguish the different selects.  Are you selecting from different tables or just with different criteria?
0
 
TadSterAuthor Commented:
K-designers: I tried If (@Preparer = 'All'); no difference

BrandonGalderisi: The only difference between the two queries is different criteria
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
TadSterAuthor Commented:
Here is the entire procedure if it helps

USE [Folder Tracking]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[GetInterviewsByDate] 

	@cYear int = 2011, 
	@Preparer varchar = 'All'
AS
BEGIN

	SET NOCOUNT ON;

If (@Preparer = 'All')
  Begin
	SELECT    IntDates.InterviewDate, COUNT(IntDates.InterviewDate) AS TRCount
	FROM (SELECT DATEADD(dd, ((DATEDIFF(dd, 0, DateStamp) / 7) * 7 + 5), 0) AS InterviewDate
		FROM   Tracking
		WHERE  (YEAR(DateStamp) = @cYear)
		AND (Status = 'Interview Date')
		AND (MONTH(DateStamp) <= 4)
		) AS IntDates
	GROUP BY InterviewDate
	ORDER BY InterviewDate
  End
Else
  Begin
	SELECT    IntDates.InterviewDate, COUNT(IntDates.InterviewDate) AS TRCount
	FROM (SELECT DATEADD(dd, ((DATEDIFF(dd, 0, DateStamp) / 7) * 7 + 5), 0) AS InterviewDate
		FROM     Tracking
		WHERE    (YEAR(DateStamp) = @cYear) 
		AND (Status = 'Interview Date') 
		AND (MONTH(DateStamp) <= 4)
		AND (Location = @Preparer)
		) AS IntDates
	GROUP BY InterviewDate
	ORDER BY InterviewDate	
  End
END

Open in new window

0
 
BrandonGalderisiCommented:
Try:


      SELECT    IntDates.InterviewDate, COUNT(IntDates.InterviewDate) AS TRCount
      FROM (SELECT DATEADD(dd, ((DATEDIFF(dd, 0, DateStamp) / 7) * 7 + 5), 0) AS InterviewDate
            FROM     Tracking
            WHERE    (YEAR(DateStamp) = @cYear)
            AND (Status = 'Interview Date')
            AND (MONTH(DateStamp) <= 4)
            AND (@Preparer = 'ALL' or Location = @Preparer)
            ) AS IntDates
      GROUP BY InterviewDate
      ORDER BY InterviewDate      


You should make some adjustments to your query regardless because doing year(DateStamp) and month(datestamp) will require that every row be evaluated.  Alternatively, try something like.

declare @dtStart datetime,@dtEnd datetime

set @dtStart = ltrim(str(@Year)) + '-01-01'
set @dtEnd = ltrim(str(@Year)) + '-05-01'

      SELECT    IntDates.InterviewDate, COUNT(IntDates.InterviewDate) AS TRCount
      FROM (SELECT DATEADD(dd, ((DATEDIFF(dd, 0, DateStamp) / 7) * 7 + 5), 0) AS InterviewDate
            FROM     Tracking
            WHERE   DateStamp >= @DtStart and DateStamp < @dtEnd
            AND (Status = 'Interview Date')
            AND (@Preparer = 'ALL' or Location = @Preparer)
            ) AS IntDates
      GROUP BY InterviewDate
      ORDER BY InterviewDate      
0
 
TadSterAuthor Commented:
BrandonGalderisi: Thanks for the performance tip; I'll use it.

0
 
TadSterAuthor Commented:
I've been doing my testing in Visual Studio 2008; maybe the problem is there.

When I look at the properties of the query of the tableAdapter, the parameters collection includes @returnValue. When I delete it, then I get no data, but the headings are correct.
0
 
BrandonGalderisiCommented:
I don't know how it will interact with VS.
0
 
TadSterAuthor Commented:
Thanks for your help, I'll look into the Visual Studio end of it
0
 
TadSterAuthor Commented:
Your answers confirmed that I was doing it correctly. Thanks BrandonGalderisi  for your additional help.
0
 
TadSterAuthor Commented:
In retrospect it would have been better to accept your answers as the solution. Thanks a lot for your help. It didn't immediately solve my problem, but it was the answer to my question.

My problem was in Visual Studio: The first output parameter (@return_value) was not what I wanted, so I had to delete it. Then my query worked as expected.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now