3

I have database with many tables already existing. I need to write stored procedure that takes data identical to some of these tables and process it in stored procedure.

I know one can create tables from type. However sometime it's cumbersome to add new type just to facilitate single stored procedure.

Is there any way to define type inline based on existing table?

Is there any way to define it as stored procedure parameter based on existing table?

6
  • 3
    You mean something like SELECT ... INTO ...? Commented Jan 18, 2017 at 0:48
  • Can you elaborate on what you mean when you use the word "type"? Commented Jan 18, 2017 at 6:42
  • 3
    You could right-click the table, select "script table as..." > "create to" > "new query editor window" and just change the script to create a user defined type instead. Commented Jan 18, 2017 at 7:19
  • 1
    Do you want pass a table data to a stored procedures? You don't need pass the whole table by parameter, you can pass the table name into stored procedures, then use dynamic statement to excute script to get data Commented Jan 18, 2017 at 7:29
  • 2
    I know exactly what you want to do and why, but I don't believe it's possible. Commented Dec 3, 2019 at 16:05

1 Answer 1

3

Is there any way to define type inline based on existing table?

The feaure is called: anchored data type and there is no direct equivalent in T-SQL.

An anchored type defines a data type based on another SQL object such as a column, global variable, SQL variable, SQL parameter, or the row of a table or view.

A data type defined using an anchored type definition maintains a dependency on the object to which it is anchored. Any change in the data type of the anchor object will impact the anchored data type. If anchored to the row of a table or view, the anchored data type is ROW with the fields defined by the columns of the anchor table or anchor view.

This data type is useful when declaring variables in cases where you require that the variable have the same data type as another object

It is supported by IBM DB2 and Oracle DBs.


Alternative:

Pass argument as XML/JSON instead of table valued paratemer. It will ease a burden of maitining type for procedures.

CREATE PROCEDURE my_proc @param XML -- instead of table type
AS
BEGIN
    -- here @param validation could be performed if needed

    SELECT s.c.value('col_name1', type_name) AS col_name1
          ,s.c.value('col_name2', type_name) AS col_name2
    INTO #tmp_param
    FROM @param p 
    CROSS APPLY p.nodes('...') s(c)

    SELECT *
    FROM #tmp_param;  -- any other use like normal table valued parameter
END

And calling it:

--Standard way
DECLARE @param table_type;
INSERT INTO @param(col1, col2) ...;
EXEC my_proc @param;

-- different way
DECLARE @param XML = (SELECT ... FROM ... FOR XML AUTO);
EXEC my_proc @param;

The only drawback here is there is no param schema validation, and it has to be performed inside stored procedure.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.