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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
 Password:
Save Password
Forgot your Password?

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

Zath
Constraint Violating Yak Guru

USA
298 Posts
Posted - 01/28/2013 :  13:38:55  Show Profile  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
52326 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  
 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.03 seconds. Powered By: Snitz Forums 2000