Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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