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.

Solution 1:

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;
...

Solution 2:

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.

Solution 3:

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.