SQLTeam.com | Weblogs | Forums

How to determine the datatype in a view in sql server


#1

hello

i have the following code from a view
how can i determine a datatype for Expr1
i want datatype decimal or number for it

SELECT Teil_Nummer, SUM(Bestand) AS Expr1
FROM dbo.v_Bestand
GROUP BY Teil_Nummer


#2

The datatype will be whatever the type for Bestand is (int, decimal, float, etc). But it is better to be explicit:

CAST(SUM(bestand) AS decimal(p,s)) AS Expr1

#3

If you do operations involving two or more dissimilar data types that can be implicitly converted, the data type precedence determines what the data type of the result will be. See here

You can use the SQL_VARIANT_PROPERTY to see what the data type is (if you don't trust the documentation :wink: as in the following example

CREATE TABLE #tmp (id DECIMAL(10,2), id2 FLOAT);
INSERT INTO #tmp VALUES (1.33,1.33);

SELECT id+id2, SQL_VARIANT_PROPERTY(id+id2,'BaseType') FROM #tmp;

DROP TABLE #tmp;

SQL_VARIANT_PROPERTY also lets you look at some other neat things as explained in the documentation.