SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Compare with Table Type
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
284 Posts

Posted - 01/28/2013 :  13:38:55  Show Profile  Visit Zath's Homepage  Reply with Quote
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



Edited by - Zath on 01/29/2013 14:39:08

sqlbay
Starting Member

12 Posts

Posted - 01/29/2013 :  03:05:44  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/29/2013 :  03:05:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000