CODEWITHSUNDEEP
sqlsql-server-2008
StatsViaCsh
StatsViaCsh

Reputation: 2640

What's the syntax to create a table type in a stored procedure (using VS2010)

I'm trying to create a table type within a stored procedure...my attempt does not compile:

CREATE PROCEDURE dbo.StoredProcedure1
    
AS
        
CREATE TYPE DailyPricingAndVolBySymbolType AS TABLE (TradeDate date, 
Symbol varchar(120), O smallmoney, H smallmoney, L smallmoney, C smallmoney, 
Vol big int)
    
GO

I just want to create the type, once, to be used in other stored procedures, I'm not looking for a "reuseable" sproc.

Upvotes: 1

Views: 11027

Answers (4)

Dinesh Vaitage
Dinesh Vaitage

Reputation: 3193

Use this to create "create type table". simple example for user

CREATE TYPE unit_list AS TABLE (
    ItemUnitId int,
    Amount float,
    IsPrimaryUnit bit
);

GO
 CREATE TYPE specification_list AS TABLE (
     ItemSpecificationMasterId int,
    ItemSpecificationMasterValue varchar(255)
);

GO
 declare @units unit_list;
 insert into @units (ItemUnitId, Amount, IsPrimaryUnit) 
  values(12,10.50, false), 120,100.50, false), (1200,500.50, true);

 declare @spec specification_list;
  insert into @spec (ItemSpecificationMasterId,temSpecificationMasterValue) 
   values (12,'test'), (124,'testing value');

 exec sp_add_item "mytests", false, @units, @spec


//Procedure definition
CREATE PROCEDURE sp_add_item
(   
    @Name nvarchar(50),
    @IsProduct bit=false,
    @UnitsArray unit_list READONLY,
    @SpecificationsArray specification_list READONLY
)
AS


BEGIN
    SET NOCOUNT OFF     

    print @Name;
    print @IsProduct;       
    select * from @UnitsArray;
    select * from @SpecificationsArray;
END

Upvotes: 1

John Dewey
John Dewey

Reputation: 7093

Create the type outside the procedure - it only needs to be created once, and used anytime thereafter:

CREATE TYPE dbo.DailyPricingAndVolBySymbolType AS TABLE (TradeDate date, 
Symbol varchar(120), O smallmoney, H smallmoney, L smallmoney, C smallmoney, 
Vol bigint)
GO

CREATE PROCEDURE dbo.StoredProcedure1
AS
declare @DailyPricingAndVolBySymbolType DailyPricingAndVolBySymbolType;
...

Upvotes: 7

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

You can easily create a table variable within a stored proc:

CREATE PROCEDURE dbo.StoredProcedure1

AS

DECLARE @DailyPricingAndVolBySymbolType TABLE (TradeDate date, 
Symbol varchar(120), O smallmoney, H smallmoney, L smallmoney, C smallmoney, 
Vol bigint)

Or create a temp table:

CREATE PROCEDURE dbo.StoredProcedure1

AS

CREATE TABLE #DailyPricingAndVolBySymbolType (TradeDate date, 
Symbol varchar(120), O smallmoney, H smallmoney, L smallmoney, C smallmoney, 
Vol bigint)

But creating a table type within the procedure makes no real sense, so far as I can see. If you were to create a new type within the procedure, nothing external could know of that type.

Based on your edits:

If it were possible to define a table type within a stored procedure, then based on other SQL Server behaviors, I wouldn't expect that same type to be available a) to other contexts, and b) after the stored proc's execution ends. That would be consistent with e.g. temp tables, and hence why I don't think it would be useful.

Upvotes: 3

Andomar
Andomar

Reputation: 238276

You're probably looking for a table variable:

declare @DailyPricingAndVolBySymbolType TABLE (...

If you're really after a table type, omit the space in big int. Your procedure will run only once, since you can't create a type if it already exists.

Upvotes: 1

Related Questions