CODEWITHSUNDEEP
sqlsql-servert-sql
Ildar
Ildar

Reputation: 115

How to DECLARE TYPE myType AS TABLE?

I am trying to use table-valued parameter to pass a column to "IN" clause in SQL Server. To do so I need to declare a type of table. The SQL expression:

DECLARE TYPE myType AS TABLE( myid varchar(50) NULL);

is giving the error:

'myType' is not a recognized CURSOR option.

Replacing DECLARE to CREATE is working good. Using "CREATE" is requiring to drop this type in any next SQL calls like:

IF TYPE_ID('myType') IS NOT NULL DROP TYPE myType;

But I would like to use the type "myType" just within this SQL expression only. How to declare a type as table without creating it permanently and without deleting it in all requests?

Upvotes: 0

Views: 712

Answers (1)

BlackMath
BlackMath

Reputation: 1858

DECLARE requires the following sinthax:

DECLARE @LastName NVARCHAR(30), @FirstName NVARCHAR(20), @Country NCHAR(2);

With DECLARE, you can:

  • To assign a variable name(using @).
  • To assign a Variable Type
  • To assign a NULL default Variable value

So "DECLARE TYPE" is not a valid statement.

Have a look to temporary tables in sql. You can create a temporary table in sql with the following code:

CREATE TABLE #TempTable
(
    name VARCHAR(50),
    age int,
    gender VARCHAR (50)

)

INSERT INTO #TempTable
SELECT name, age, gender
FROM student
WHERE gender = 'Male'

With a temporary table, you can store a subset of data from a standard table for a certain period of time.

Temporary tables are stored inside “tempdb” which is a system database.

Upvotes: 1

Related Questions