Custom Table Type – sql server

Posted: May 26, 2017 in SQLServer, Uncategorized
Tags: , ,
IFNOTEXISTS(SELECT*FROM sys.types st JOIN sys.schemas ss ON st.schema_id= ss.schema_id WHERE st.name =N’IdVal’ AND ss.name =N’dbo’)
CREATE TYPE [dbo].[IdVal] AS TABLE (
      [Id] [int] NOT NULL,
      [Val] [nvarchar] (100) NOT NULL
)
GO
/*
declare @Table dbo.IdVal
insert into @Table
      select 1,’val1′
            union
      select 2,’val2′
select * from @Table
exec somesp @TableInput=@Table
CREATE PROCEDURE somesp   
@IdVal IdVal READONLY
AS
……
 
*/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s