September 17, 2015 at 10:56 am
I have three variables
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
They are sent to the sproc as null. Since they could be null I need to exclude them from posting to a temp table
Example
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
SET @CostID = NEWID()
SELECT @QuantityID as ID UNION ALL
SELECT @LengthID UNION ALL
SELECT @CostID
Two values are null. I want those excluded from this table
Here is the example of what I am trying to do:
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
DECLARE @Temp as Table (id uniqueidentifier NOT NULL Primary key)
SET @CostID = NEWID()
INSERT INTO @Temp
SELECT @QuantityID as ID UNION ALL
SELECT @LengthID UNION ALL
SELECT @CostID
How do I insert into @Temp only non null values?
September 17, 2015 at 11:01 am
INSERT INTO @Temp
SELECT * FROM (SELECT @QuantityID as ID UNION
SELECT @LengthID UNION
SELECT @CostID) Temp
WHERE id is not null
Better idea than this?
September 17, 2015 at 11:05 am
IF @QuantityID IS NOT NULL
INSERT INTO @Temp SELECT @QuantityID
IF @LengthID IS NOT NULL
INSERT INTO @Temp SELECT @LengthID
IF @CostID IS NOT NULL
INSERT INTO @Temp SELECT @CostID
Keep it simple?
September 17, 2015 at 11:18 am
JKSQL (9/17/2015)
INSERT INTO @Temp
SELECT * FROM (SELECT @QuantityID as ID UNION
SELECT @LengthID UNION
SELECT @CostID) Temp
WHERE id is not null
Better idea than this?
Using a table value constructor?
INSERT INTO @Temp
SELECT *
FROM (VALUES(@QuantityID),
(@LengthID),
(@CostID)) Temp(id)
WHERE id is not null
September 17, 2015 at 12:08 pm
That's interesting I did not know you could do that. Are they trying to get away from doing select and unions?
September 17, 2015 at 12:49 pm
It's not to move away from selects and unions as those will still be of use. It's more about keeping it simpler.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply