'IN' Keyword in User Defined Table Types

Question

I am using a user defined table type in one of my stored procedures sending a huge list of zipcodes. Following is my stored procedure

CREATE Procedure [PrdCtlg].[sp_GetZipByCodes](@dtZip PrdCtlg.ZipType ReadOnly)
as 
begin
Select z.pkZipId, z.ZipCode, z.fkCityId from prdctlg.zip z where zipcode in 
 (select zip from @dtZip )


end

But it returns nothing. Any help would be appreciated.

Answer 1

I think this code might helps you and we should not create stored procedure name prefixed with "Sp_" , because all System defined stored procedures start like that as per sql norms use Like "usp_ProcName"

IF EXISTS 
(SELECT 1

  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE SPECIFIC_NAME = 'sp_GetZipByCodes'
  AND ROUTINE_TYPE = 'PROCEDURE'
)
  DROP PROCEDURE sp_GetZipByCodes

CREATE PROCEDURE [PrdCtlg].[sp_GetZipByCodes] (@dtZip PrdCtlg.ZipType READONLY)
AS
BEGIN
  IF EXISTS (SELECT 1 FROM @dtZip)
  BEGIN
            SELECT
                  z.pkZipId,
                  z.ZipCode,
                  z.fkCityId
            FROM prdctlg.zip z
            WHERE zipcode IN (SELECT zip FROM @dtZip)

  END
END
  --Sample value passed to Execute Procedure
  DECLARE @dtZip PrdCtlg.ZipType

  INSERT INTO @dtZip (ColumnName)
    VALUES (1)
  INSERT INTO @dtZip (ColumnNAme)
    VALUES (1)

  EXEC [dbo].[usp_GetZipByCodes] @dtZip = @dtZip