Query suggestions 1

[wes987654]

I have the query below that tells will get an item number where a order exists. I pass in a string of 'SBCORD10949415', 'SBCORD10953377' as a parameter. I need to be able to tell if any ordernumbers passed into the parameter do not exist so I can flag them in the UI. Any ideas on how to go about that? I know I could probably parse the string and loop a query but that doesnt sound like a great way to do that... Thanks a lot for any info!

SELECT Distinct GPItemNumber FROM UserTemplate WHERE OrderNumber in ('SBCORD10949415', 'SBCORD10953377')

Returns:
GPItemNumber
SBC-MIF1-001-1109
SBC-PST2-001-1109
SBC-SNB1-001-CORE

Need another recordset that returns:
NotInDB
SBCORD10953377

 

[markros]

1. Split this string into a table using any of the splitting functions available.

;with cte as (select * from dbo.fnSplit(@OrdersList,','))

select cte.*, case when T.OrderNumber IS NULL then 'Not exists' else 'Exists' end from cte LEFT JOIN UserTemplate T on cte.Value = T.OrderNumber

PluralSight Learning Library

 

[wes987654]

I have this only thing I need is to give the exists column a name how do I do that? Thanks so much for your help!

Declare
@OrdersList varchar(50)
set @OrdersList = replace('SBCORD10949415, SBCORD10953377',' ','')

SELECT Distinct GPItemNumber FROM UserTemplate WHERE OrderNumber in (@OrdersList)

with cte as (select * from fn_Split(@OrdersList,','))

select Distinct T.GPItemNumber, cte.*, case when T.OrderNumber IS NULL then 'Not exists' else 'Exists' end from cte LEFT JOIN UserTemplate T on cte.cValue = T.OrderNumber

 

[markros]

Declare @OrdersList varchar(50)
set @OrdersList = replace('SBCORD10949415, SBCORD10953377',' ','')

SELECT Distinct GPItemNumber FROM UserTemplate WHERE OrderNumber in (@OrdersList)

with cte as (select * from fn_Split(@OrdersList,','))

select  Distinct T.GPItemNumber, cte.*, case when T.OrderNumber IS NULL then 'Not exists' else 'Exists' end [COLOR=red]AS TestField[/color]
from cte LEFT JOIN UserTemplate T on cte.cValue = T.OrderNumber

PluralSight Learning Library