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

LVL 2
TadSterAsked:
Who is Participating?
 
TadSterConnect With a Mentor Author Commented:
Thanks for your help, I'll look into the Visual Studio end of it
0
 
Jesus RodriguezConnect With a Mentor IT 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
TadSterAuthor Commented:
K-designers: I tried If (@Preparer = 'All'); no difference

BrandonGalderisi: The only difference between the two queries is different criteria
0
 
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
 
BrandonGalderisiConnect With a Mentor Commented:
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.