SQL Server : variable with EXCEPT

Question

I'm stuck at a point with my queries.

I have a table with some project, first I'd like to check if All of my projects are in my table and store the result in a variable

So I have something like:

  SELECT [ProjectID]
  FROM [DB].[dbo].[Project]
  EXCEPT
  SELECT [ProjecID]
  FROM  [DB].[dbo].[CurrentProject]
  WHERE ResourceId = (SELECT ResourceId
                      FROM [DB].[dbo].[Timesheets]
                      WHERE @TimesheetUID = TimesheetId)

If I try to use

DECLARE @STOREVAR UNIQUEIDENTIFIER

@STOREVAR = SELECT [ProjectID]
            FROM [DB].[dbo].[Project]
            EXCEPT
            SELECT [ProjecID]
            FROM [DB].[dbo].[CurrentProject]
            WHERE ResourceId = (SELECT ResourceId
                                FROM [DB].[dbo].[Timesheets]
                                WHERE @TimesheetUID = TimesheetId)

I need to store in my variable because after that, I have to do a IF with @storevar

It's not working, have you any idea of how could I do that?

Do you mean select @StoreVar = ProjectId from ...? That will work if you are certain the the query will never return more than one value. Tip: "It's not working" isn't much to go on. If you read that would you ask "How does it fail?" "I get an error." "Are you at liberty to disclose it?" "Yes" ... — HABO
– HABO, Commented Aug 31, 2018 at 15:25

Yogesh Sharma · Accepted Answer · 2018-08-31 14:04:03Z

2

You need table variable instead :

DECLARE @STOREVAR TABLE ( [ProjectID]  UNIQUEIDENTIFIER)

INSERT INTO @STOREVAR ([ProjectID])
       SELECT [ProjectID]
       FROM [DB].[dbo].[Project]
       EXCEPT
       SELECT [ProjecID]
       FROM  [DB].[dbo].[CurrentProject]
       WHERE ResourceId = (SELECT ResourceId
                           FROM [DB].[dbo].[Timesheets]
                           WHERE @TimesheetUID=TimesheetId
                          );

answered Aug 31, 2018 at 14:04

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : variable with EXCEPT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related