Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Compare with Table Type

Author  Topic 

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-01-28 : 13:38:55
I have a user defined table type:

CREATE TYPE [ENUM].[str_list_tbltype] AS TABLE(
[STR_NAME] [varchar](100) NOT NULL
)
GO


This comes into the stored procedure as the parameter:

@STR_IDs dbo.str_list_tbltype READONLY,


There is a table, call it idTable and it's data is:

PK Co IDStr
1 4 A3
2 8 B5
3 7 4X
4 8 G4
5 8 B4

Here is a simple select for that table:

SELECT * FROM idTable WHERE Co = 8


Let's say @STR_IDs contains B5 and B4

What I need is to compare using @STR_IDs

Without using a cursor, is there an easier way to do this?

Find out if each and every string in @STR_IDs exists in idTable
Where Co = 8

Something like:

IF EXISTS(SELECT * FROM idTable WHERE Co = 8 AND IDStr IN (SELECT STR_NAME FROM @STR_IDs)


Thanks,

Zath


sqlbay
Starting Member

12 Posts

Posted - 2013-01-29 : 03:05:44
If @STR_IDs is your master table:


IF(SELECT COUNT(*) FROM @STR_IDs T1
WHERE T1.STR_NAME NOT IN (SELECT IDStr FROM idTable T2 WHERE Co = 8)
)=0

--QUERY RETURNS 0 IF ALL 'STR_NAME' ARE PRESENT IN idTable

SQL Server Professional http://sqlbay.blogspot.in
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 03:05:46
you can do like

SELECT COUNT(*)
FROm @STRIDS s
LEFT JOIN idTable t
ON t.IDStr = s.Field
AND t.Co = 8
WHERE t.IDStr IS NULL



this will give details of missing records if any
if count is 0 then all records exist in idtable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -