Link to home
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.
Avatar of andy232
andy232
Flag of United States of America image

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
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Steve Bohler

ASKER

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

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

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

That's not a good idea.  Why would you want to add dynamic SQL to handle an optional parameter.
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
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
it should be

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

or whatever size @lastname should be
That got it working Anydy232. Thanks!
FYI... The assisted solution's comments were covered in posting # http:#23481470