asked on

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

SOLUTION

Jesus Rodriguez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

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?

TadSter

ASKER

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

BrandonGalderisi: The only difference between the two queries is different criteria

TadSter

ASKER

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

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TadSter

ASKER

BrandonGalderisi: Thanks for the performance tip; I'll use it.

TadSter

ASKER

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.

BrandonGalderisi

I don't know how it will interact with VS.

ASKER CERTIFIED SOLUTION

TadSter

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TadSter

ASKER

Your answers confirmed that I was doing it correctly. Thanks BrandonGalderisi for your additional help.

TadSter

ASKER

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.