SQL Server 2005: Determine Datatype of Variable

SQL Server 2005: Determine datatype of variable

run this

declare @d int

select @d = 500

if cast(sql_variant_property(@d,'BaseType') as varchar(20))  = 'int'
print 'yes'
else
print 'no'

SQL Server 2005 - DataType of variable to store a RowSet

You may cteate table typed variable and use it multiple times, like that:

CREATE @Projects TABLE(Id INT NOT NULL)

INSERT @Projects SELECT ProjectId FROM Tasks

SELECT ... FROM Projects WHERE ProjectId IN (SELECT ProjectId FROM @Projects)
SELECT ... FROM Projects WHERE ProjectId NOT IN (SELECT ProjectId FROM @Projects)

Althought - it is not replacement of query by variable, it is make more reusable the subquery results

But

under certain conditions this may degrade your queries' performance

SQL Server check resultant data type of expression

SQL_VARIANT_PROPERTY is close

DECLARE @what sql_variant;
DECLARE @foo decimal(19,3) = 1, @bar decimal(11,7) = 2;

SELECT @what = @foo / @bar;
SELECT
    SQL_VARIANT_PROPERTY(@what, 'BaseType'),
    SQL_VARIANT_PROPERTY(@what, 'Precision'),
    SQL_VARIANT_PROPERTY(@what, 'Scale'),
    SQL_VARIANT_PROPERTY(@what, 'MaxLength');

SELECT @what = @foo + @bar;
SELECT
    SQL_VARIANT_PROPERTY(@what, 'BaseType'),
    SQL_VARIANT_PROPERTY(@what, 'Precision'),
    SQL_VARIANT_PROPERTY(@what, 'Scale'),
    SQL_VARIANT_PROPERTY(@what, 'MaxLength');

SELECT @what = @foo * @bar;
SELECT
    SQL_VARIANT_PROPERTY(@what, 'BaseType'),
    SQL_VARIANT_PROPERTY(@what, 'Precision'),
    SQL_VARIANT_PROPERTY(@what, 'Scale'),
    SQL_VARIANT_PROPERTY(@what, 'MaxLength');

Or temp table/SELECT..INTO.. as an extension of what you've already done

Edit: Remus' answer?

Cannot find data type date in SQL Server 2005

SQL Server 2005 only supports the datetime (and smalldatetime) data type.

See http://msdn.microsoft.com/en-us/library/ms187752%28v=sql.90%29.aspx

How do I return the SQL data types from my query?

select * from information_schema.columns

could get you started.

How to determine the datatypes of the results of a SQL?

You can run the query with SET FMTONLY ON, but that might not help you to easily determine the data types returned, since you're working in management studio only.
If it was me, I think I'd create a view temporarily with the same body as the stored procedure (you may have to declare variables for any parameters). You can then look at the columns returned by the view with the INFORMATION_SCHEMA queries already discussed.

Data type size of variable in sql

You can use sql_variant_property.

declare @test varchar(255)
set @test = '' --Must assign a value
select sql_variant_property(@test, 'MaxLength')

How to determine internal name of table-valued variable in MS SQL Server 2005

I don't believe you can, as table variables are created in memory not in tempdb.

SQL statement to get column type

Using SQL Server:

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_SCHEMA = 'yourSchemaName' AND
     TABLE_NAME   = 'yourTableName' AND 
     COLUMN_NAME  = 'yourColumnName'

Finding the data types of a SQL temporary table

You need to make sure sp_help runs in the same database where the table is located (tempdb). You can do this by prefixing the call directly:

EXEC tempdb.dbo.sp_help @objname = N'#temp';

Or by prefixing a join against tempdb.sys.columns:

SELECT [column] = c.name, 
       [type] = t.name, c.max_length, c.precision, c.scale, c.is_nullable 
    FROM tempdb.sys.columns AS c
    INNER JOIN tempdb.sys.types AS t
    ON c.system_type_id = t.system_type_id
    AND t.system_type_id = t.user_type_id
    WHERE [object_id] = OBJECT_ID(N'tempdb.dbo.#temp');

This doesn't handle nice things for you, like adjusting max_length for varchar differently from nvarchar, but it's a good start.

In SQL Server 2012 or better, you can use a new DMF to describe a resultset, which takes that issue away (and also assembles max_length/precision/scale for you). But it doesn't support #temp tables, so just inject the query without the INTO:

SELECT name, system_type_name, is_nullable
  FROM sys.dm_exec_describe_first_result_set(N'SELECT 
        a.col1, 
        a.col2, 
        b.col1... 
      --INTO #temp
      FROM ...;',NULL,1);

---

Related Topics