Sql-server – How to determine the data type of all fields in a result set in SSDT

sql serverssdt

I'm writing a table valued function in SQL Server Data Tools (SSDT). ~~Table valued functions require you to explicitly state the data type of the returned table~~. In my particular case, I've written a query which returns the values that I want. I can execute it using SSDT, but can't figure out how to quickly determine the data type of the columns returned.

Is there a way to get the list of columns that it returns and the data types of said columns so I can quickly and accurately populate the list of columns in the ReturnTable?

The closest I've gotten is to hover over the field name in the SELECT clause, but that doesn't provide the length or precision limitations.

enter image description here

Edit: I struck through my comment regarding table valued functions require you to explicitly state the data types of the columns in the returned table based on @srutkzy's answer. Thanks!

Best Answer

Table valued functions require you to explicitly state the data type of the returned table.

That statement is not necessarily correct. Yes, multi-statement TVFs do require that you declare the return table. However, inline TVFs do not require any table definition as they simply encapsulate a single query (essentially a View that accepts input parameters). Please see the MSDN page for CREATE FUNCTION for more details.

Given that inline TVFs are much more efficient than multi-statement TVFs, the first question is: can this query be done in an inline TVF? If yes, then make it an inline TVF and then there is no need to define the table structure.

If the operation cannot be done as an inline TVF, then you should get the datatypes from the base table(s). You can use either:

EXEC sp_help 'SchemaName.TableName';
-- for NVARCHAR / NCHAR, use Length/2 to get the size

or:

SELECT col.*
FROM   INFORMATION_SCHEMA.COLUMNS col
WHERE  col.TABLE_SCHEMA = N'SchemaName'
AND    col.TABLE_NAME = N'TableName';
-- for NVARCHAR / NCHAR, use CHARACTER_MAXIMUM_LENGTH to get the size

Related Solutions

Sql-server – SQL Data type for “Reference” fields in table – SQL Server

Here is what I would do based on your requirements:

CREATE DATABASE [demo];
GO

USE [demo];



CREATE TABLE [dbo].[Player](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [firstName] [varchar](50) NULL,
    [secondName] [varchar](50) NULL,
    [fk_team] [int] NULL,
 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED ([playerID] ASC)
 );
GO


CREATE TABLE [dbo].[EventTypes](
    [EventTypeID] [int] IDENTITY(1,1) NOT NULL,
    [EventType] [varchar](50) NULL,
 CONSTRAINT [PK_EventTypes] PRIMARY KEY CLUSTERED ([EventTypeID] ASC)
 );

GO


CREATE TABLE [dbo].[Events](
    [eventsID] [int] IDENTITY(1,1) NOT NULL,
    [Minute] [varchar](50) NULL,
    [fk_EventType] [int] NULL,
    [fk_Player] [int] NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ( [eventsID] ASC)
 );
GO
SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD  
CONSTRAINT [FK_Events_EventTypes] FOREIGN KEY([fk_EventType])
REFERENCES [dbo].[EventTypes] ([EventTypeID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_EventTypes]
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD  
CONSTRAINT [FK_Events_Player] FOREIGN KEY([fk_Player])
REFERENCES [dbo].[Player] ([playerID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_Player]
GO



INSERT INTO [dbo].[EventTypes] ([EventType]) 
      VALUES('Try'), ('Conversion'), ('Penalty');
GO
select * from EventTypes;

This should be what you need. Have a great day!

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

Best Answer

Related Solutions

Sql-server – SQL Data type for “Reference” fields in table – SQL Server

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Related Question