• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

SQL IIF - setting a variable

In My trigger i have this

Select @ipkDriverID = SELECT IIF(@vDriverID IS NOT NULL OR @vDriverID <> '' 
					, Select @ipkDriverID = Select ipkDriverID from tblAssets_Driver
					, NULL) 

Open in new window


But it doesn't work

I basically want to set the variable @ipkDriverID if @vDriverID has a value

i'm sure this is really easy but cant do it - sql 2014
0
websss
Asked:
websss
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
Harish VargheseProject LeaderCommented:
Simply use an IF condition as below:
IF ISNULL (@vDriverID, '') <> ''
      SELECT @ipkDriverID = ipkDriverID from tblAssets_Driver
ELSE
      SELECT @ipkDriverID = NULL

Open in new window

0
 
Harish VargheseProject LeaderCommented:
And if you want to use IIF only, then here is the syntax:
Select @ipkDriverID = IIF (@vDriverID IS NOT NULL AND @vDriverID <> '' 
					, ipkDriverID 
					, NULL)
From tblAssets_Driver

Open in new window

0
 
Randy PooleCommented:
The problem I see is that you have no where clause in your select statement which is an issue.  You will always just select the first record value so it will always be the same.

Select @ipkDriverID=case when Len(IsNull(@vDriverID,''))>0 then @ipkDriverID else ipkDriverID end from tblAssets_Driver where somecolumn=somevalue

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ste5anSenior DeveloperCommented:
E.g.  
DECLARE @tblAssets_Driver TABLE ( ID INT, ipkDriverID INT );
INSERT INTO @tblAssets_Driver
VALUES	( 1, 10 ), ( 2, 20 );

DECLARE @ipkDriverID INT = -1;
DECLARE @vDriverID VARCHAR(255) = NULL;

SELECT	@ipkDriverID = IIF(COALESCE( @vDriverID, '' ) != '', ipkDriverID, NULL) 
FROM	@tblAssets_Driver;

SELECT	@vDriverID, @ipkDriverID;

SET @ipkDriverID = -1
SET @vDriverID = 'NOT NULL OR EMPTY';

SELECT	@ipkDriverID = IIF(COALESCE( @vDriverID, '' ) != '', ipkDriverID, NULL) 
FROM	@tblAssets_Driver;

SELECT	@vDriverID, @ipkDriverID;       
		
SET @ipkDriverID = -1
SET @vDriverID = 'NOT NULL OR EMPTY';
DELETE FROM @tblAssets_Driver;

SELECT	@ipkDriverID = IIF(COALESCE( @vDriverID, '' ) != '', ipkDriverID, NULL) 
FROM	@tblAssets_Driver;

SELECT	@vDriverID, @ipkDriverID;         		                           

Open in new window


But take Randy's advice seriously. It will return the value from the "first" row. Cause you don't have specified an ORDER BY clause with a TOP 1 the returned value is unpredictable (it depends on the optimizer and excution path).
0
 
Randy PooleCommented:
Also, I see my code is back words!
Select @ipkDriverID=case when Len(IsNull(@vDriverID,''))=0 then @ipkDriverID else ipkDriverID end from tblAssets_Driver where somecolumn=somevalue

Open in new window

0
 
Alpesh PatelAssistant ConsultantCommented:
Select @ipkDriverID = IIF((@vDriverID IS NOT NULL OR @vDriverID <> '')
                              , (Select ipkDriverID from tblAssets_Driver)
                              , NULL)
0
 
ste5anSenior DeveloperCommented:
btw, as you said trigger: can you post the entire trigger?

Remember that a trigger is executed per statement, thus you need to handle sets (INSERTED and DELETED) as more than one row could be affected.
0
 
websssAuthor Commented:
Between you all I got there in the end
Each of you had a nugget that helped me nail it!
thanks
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now