Avatar of Steve Bohler
Steve BohlerFlag for United States of America asked on

TSQL if...then

Hello,

I want to modify a stored procedure so that, if a parameter passed in has a value, then an additional "AND (...)" is included in the query.

How can this be done?

Thanks in advance.
Microsoft SQL Server

Avatar of undefined
Last Comment
BrandonGalderisi

8/22/2022 - Mon
andy232

Do you want the variable to have a default value?

CREATE PROC test @var int = 0
AS
BEGIN
IF @var <> 0
BEGIN
   SELECT CAST (@var as varchar) + ' has value other than 0'
END

.....
ASKER CERTIFIED SOLUTION
BrandonGalderisi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Steve Bohler

Thanks.

The syntax checks, but the logic isn't working.

I've attached the code snippet. The parameter being passed in is @LastName.
If a blank last name is passed in, I don't want the last condition to be included.

With the last condition out, I get 10 rows (one of which includes a last name, say, "Jones"). But, with the condition in (and the last name passed in), it returns 0 rows.

Anything obvious I'm doing wrong?

Thanks again.
CREATE PROCEDURE qryBBHistory
(
	@StartDate datetime,
	@EndDate datetime,
	@AccountID integer,
	@ExcludeInternal integer,
	@IsParent integer,
	@LastName varchar
)
 AS
SET @EndDate = DATEADD(day,1,@EndDate)
SELECT DISTINCT
	UserName, 
	UserFirstName, 
	UserLastName, 
	AccountName,
UserEmail,
	DateStamp,
	(SELECT COUNT (*) FROM tblTrack WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate) AS Logins,
	(SELECT TOP 1 Track_StartDate FROM tblTrack WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate ORDER BY Track_StartDate DESC) AS LastLogin,
	(SELECT SUM(TrackDetail_Time) FROM tblTrack INNER JOIN tblTrackDetail ON tblTrack.Track_ID = tblTrackDetail.Track_ID WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate) AS TotalTime,
(SELECT count(*) FROM tblTrack INNER JOIN tblTrackDetail ON tblTrack.Track_ID = tblTrackDetail.Track_ID WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate AND 
(tblTrackDetail.TrackDetail_page like '/tools/%' OR tblTrackDetail.TrackDetail_page like '/overviews/%' OR
tblTrackDetail.TrackDetail_page like '/research/%' OR tblTrackDetail.TrackDetail_page like '/resources/%' OR tblTrackDetail.TrackDetail_page like '/sadvisor/%')) AS ResourcesAccessed
FROM tblUser
	INNER JOIN tblAccount ON tblUser.AccountID = tblAccount.AccountID
	INNER JOIN tblTrack ON tblUser.UserID = tblTrack.UserID
WHERE Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate 
AND ((@IsParent = 0 and tblUser.AccountID = case When @AccountID <> 0 Then @AccountID else.tblUser.AccountID End)
OR (@IsParent = 1 and tblUser.AccountID in (Select childaccountid from tblParentAccounts where ParentAccountID = @AccountID)))
AND (@ExcludeInternal = 0 OR tblUser.AccountID <> 40 and tblUser.AccountID <> 36)
AND (@LastName = '' or (tbluser.userlastname = @LastName )) 
ORDER BY   AccountName, Logins DESC , LastLogin DESC, UserLastName
GO

Open in new window

BrandonGalderisi

Are you SURE @LastName is ''?  You really should change it to allow NULL to be passed in.

Also, you should define the size of @lastname to the size of your field.  If varchar is specified and not a length, it will default to varchar(30).
CREATE PROCEDURE qryBBHistory
(
        @StartDate datetime,
        @EndDate datetime,
        @AccountID integer,
        @ExcludeInternal integer,
        @IsParent integer,
        @LastName varchar=null
)
 AS
if len(@LastName)=0
   set @LastName = NULL
 
SET @EndDate = DATEADD(day,1,@EndDate)
SELECT DISTINCT
        UserName, 
        UserFirstName, 
        UserLastName, 
        AccountName,
UserEmail,
        DateStamp,
        (SELECT COUNT (*) FROM tblTrack WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate) AS Logins,
        (SELECT TOP 1 Track_StartDate FROM tblTrack WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate ORDER BY Track_StartDate DESC) AS LastLogin,
        (SELECT SUM(TrackDetail_Time) FROM tblTrack INNER JOIN tblTrackDetail ON tblTrack.Track_ID = tblTrackDetail.Track_ID WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate) AS TotalTime,
(SELECT count(*) FROM tblTrack INNER JOIN tblTrackDetail ON tblTrack.Track_ID = tblTrackDetail.Track_ID WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate AND 
(tblTrackDetail.TrackDetail_page like '/tools/%' OR tblTrackDetail.TrackDetail_page like '/overviews/%' OR
tblTrackDetail.TrackDetail_page like '/research/%' OR tblTrackDetail.TrackDetail_page like '/resources/%' OR tblTrackDetail.TrackDetail_page like '/sadvisor/%')) AS ResourcesAccessed
FROM tblUser
        INNER JOIN tblAccount ON tblUser.AccountID = tblAccount.AccountID
        INNER JOIN tblTrack ON tblUser.UserID = tblTrack.UserID
WHERE Track_StartDate >= @StartDate AND Track_StartDate <= @EndDate 
AND ((@IsParent = 0 and tblUser.AccountID = case When @AccountID <> 0 Then @AccountID else.tblUser.AccountID End)
OR (@IsParent = 1 and tblUser.AccountID in (Select childaccountid from tblParentAccounts where ParentAccountID = @AccountID)))
AND (@ExcludeInternal = 0 OR tblUser.AccountID <> 40 and tblUser.AccountID <> 36)
AND (@LastName is null or (tbluser.userlastname = @LastName )) 
ORDER BY   AccountName, Logins DESC , LastLogin DESC, UserLastName
GO

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
andy232

or you can dynamic sql the logic
DECLARE @sql varchar(max)
 
 
SET @EndDate = DATEADD(day,1,@EndDate)
 
SET @sql = 'SELECT DISTINCT UserName, UserFirstName, UserLastName, AccountName,UserEmail,	DateStamp, '+
		'	(SELECT COUNT (*) FROM tblTrack WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= '''+ CAST(@StartDate as varchar)+ ''' AND Track_StartDate <= '''+ CAST(@EndDate as varchar) + ''' ) AS Logins, '+
	' (SELECT TOP 1 Track_StartDate FROM tblTrack WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= '''+ CAST(@StartDate as varchar)+ ''' AND Track_StartDate <= '''+ CAST(@EndDate as varchar) + '''ORDER BY Track_StartDate DESC) AS LastLogin, '+
	' (SELECT SUM(TrackDetail_Time) FROM tblTrack INNER JOIN tblTrackDetail ON tblTrack.Track_ID = tblTrackDetail.Track_ID WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= '''+ CAST(@StartDate as varchar)+ '''AND Track_StartDate <= '''+ CAST(@EndDate as varchar) + '''AS TotalTime, '+
'(SELECT count(*) FROM tblTrack INNER JOIN tblTrackDetail ON tblTrack.Track_ID = tblTrackDetail.Track_ID WHERE tblTrack.UserID = tblUser.UserID AND Track_StartDate >= '''+ CAST(@StartDate as varchar)+ '''AND Track_StartDate <= '''+ CAST(@EndDate as varchar) + '''AND  '+
'(tblTrackDetail.TrackDetail_page like ''/tools/%'' OR tblTrackDetail.TrackDetail_page like ''/overviews/%'' OR '+
'tblTrackDetail.TrackDetail_page like ''/research/%'' OR tblTrackDetail.TrackDetail_page like ''/resources/%'' OR tblTrackDetail.TrackDetail_page like ''/sadvisor/%'')) AS ResourcesAccessed '+
'FROM tblUser '+
'	INNER JOIN tblAccount ON tblUser.AccountID = tblAccount.AccountID '+
'	INNER JOIN tblTrack ON tblUser.UserID = tblTrack.UserID '+
'WHERE Track_StartDate >= '''+ CAST(@StartDate as varchar)+ '''AND Track_StartDate <= '''+ CAST(@EndDate as varchar) + ''' '+
'AND (('+ CAST ( @IsParent as varchar) +'  = 0 and tblUser.AccountID = case When ' + CAST(@AccountID as varchar) + ' <> 0 Then ' + CAST(@AccountID as varchar) + ' else.tblUser.AccountID End) '+
'OR ('+ CAST ( @IsParent as varchar) +' = 1 and tblUser.AccountID in (Select childaccountid from tblParentAccounts where ParentAccountID = ' + CAST(@AccountID as varchar) + '))) '+
'AND ('+CAST(@ExcludeInternal as varchar)+ ' = 0 OR tblUser.AccountID <> 40 and tblUser.AccountID <> 36) ' 
 
 
IF @lastname is not null
	SET @sql = SQL + 'AND (  or (tbluser.userlastname ='''+@lastname+'''  ))  '
 
 
 
SET @sql = N'ORDER BY   AccountName, Logins DESC , LastLogin DESC, UserLastName '
 
 
exec (@sql)

Open in new window

BrandonGalderisi

That's not a good idea.  Why would you want to add dynamic SQL to handle an optional parameter.
ASKER
Steve Bohler

When I put...

@LastName varchar=null

... it tells me that @LastName isn't declared properly.

Is there a way in the SP to declare a parameter as optional (it doesn't have to be passed in necessarily)?

Steve
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Steve Bohler

OK, in the code, if I hard-code an appropriate last name such as:

AND (@LastName is Null or (tbluser.userlastname = 'SNELLING' ))

The query works fine.

If I set @Lastname='SNELLING'  above the code, and leave the variable as...

AND (@LastName is Null or (tbluser.userlastname = @LastName ))

... then it doesn't return any rows.

Any advice?

Thanks again!
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
andy232

it should be

CREATE proc  <procname> @lastname varchar(255) = null

or whatever size @lastname should be
ASKER
Steve Bohler

That got it working Anydy232. Thanks!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
BrandonGalderisi

FYI... The assisted solution's comments were covered in posting # http:#23481470