question

nachoshaw-9496 avatar image
0 Votes"
nachoshaw-9496 asked nachoshaw-9496 commented

SQL Server - Stored Procedure - Return on 2 identical parameters

Hi

Cant seem to figure this out.. In my table i have a range of finishes which all have different finish types (Paint, Lacquer, Clear, Mill). There are also 3 other finishes that need to be in the list no matter what finish_id is selected. These are finish_id = 1

in a single stored procedure, how can i return data based on both types?

Here is what i have tried
WHERE (dbo.TBL_FinishList.TypeID = 1 AND TypeID = @TypeID OR @TypeID IS NULL AND (dbo.TBL_FinishList.active = '1'))

I need to return 2 finish types-

TypeID = 1 which is to be shown with all returns
TypeID = 15 which returns the required Finish Type (15 is the example)

Thanks

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe you need this:

where ( TypeID = 1 or @TypeID is null or TypeID = @TypeID ) and active = 1

It assumes that you only need the active items.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered nachoshaw-9496 commented

The following code returns the value of
dbo.TBL_FinishList.TypeID = 1,
TypeID = @TypeID and dbo.TBL_FinishList.active = '1',
@TypeID IS NULL and dbo.TBL_FinishList.active = '1'.

 WHERE dbo.TBL_FinishList.TypeID = 1 OR 
 ((TypeID = @TypeID OR @TypeID IS NULL) AND (dbo.TBL_FinishList.active = '1'))

The following code returns the value of
dbo.TBL_FinishList.TypeID = 1,
@TypeID IS NULL,
TypeID = @TypeID and dbo.TBL_FinishList.active = '1'.

 WHERE dbo.TBL_FinishList.TypeID = 1 OR 
 @TypeID IS NULL  OR (TypeID = @TypeID AND dbo.TBL_FinishList.active = '1')

If you want to return the value of
dbo.TBL_FinishList.TypeID = 1,
TypeID = @TypeID,
@TypeID IS NULL and dbo.TBL_FinishList.active = '1'. Please refer to Guoxiong's second code.

You may be a little confused about the execution order of operators:
Operator Precedence (Transact-SQL)


If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Much appreciated everyone. Yes i didnt realise there was a specific order of precedence but upon reading it makes sense. The big thing i noticed was that i was not using the OR operator and instead was using the AND.

Thanks

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

If all returned types must be active, try this:

WHERE dbo.TBL_FinishList.active = 1 AND (dbo.TBL_FinishList.TypeID = 1 OR dbo.TBL_FinishList.TypeID = @TypeID)

It seems you also want the types to be returned when @TypeID is NULL, try this:

WHERE dbo.TBL_FinishList.TypeID = 1 OR dbo.TBL_FinishList.TypeID = @TypeID OR (dbo.TBL_FinishList.TypeID IS NULL AND dbo.TBL_FinishList.active = 1)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.