CREATE FUNCTION dbo.fnResolveFractionals
(
@data VARCHAR(20)
)
RETURNS FLOAT
AS
BEGIN
RETURN CASE
WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.0E * SUBSTRING(@data, CHARINDEX(' ', @data) + 1, CHARINDEX('/', @data) - CHARINDEX(' ', @data) - 1) / NULLIF(RIGHT(@data, LEN(@data) - CHARINDEX('/', @data)), 0)
WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 0 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 0 THEN CAST(@data AS FLOAT)
ELSE NULL
END
END
GO
DECLARE @Sample TABLE
(
data VARCHAR(20)
)
INSERT @Sample
SELECT '5 3/16' UNION ALL
SELECT '7' UNION ALL
SELECT '2 /' UNION ALL
SELECT ...
DECLARE @Sample TABLE
(
StudentID INT,
Class VARCHAR(20)
)
INSERT @Sample
SELECT 1, 'Maths' UNION ALL
SELECT 1, 'English' UNION ALL
SELECT 1, 'Science' UNION ALL
SELECT 2, 'Maths' UNION ALL
SELECT 2, 'English' UNION ALL
SELECT 2, 'Science' UNION ALL
SELECT 2, 'History' UNION ALL
SELECT 3, 'English' UNION ALL
SELECT 3, 'Maths' UNION ALL
SELECT 3, 'Science' UNION ALL
SELECT 3, 'RE' UNION ALL
SELECT 4, 'Science' UNION ALL
SELECT 4, 'Maths' UNION ALL
SELECT 4, 'English' UNION ALL
SELECT 4, 'History' UNION ALL
SELECT 4, 'French'
;WITH Yak(ClassName, ClassPath, Combinations)
AS (
SELECT Class,
CAST(Class AS VARCHAR(MAX)),
CAST(1 AS INT)
FROM @Sample
GROUP BY Class
UNION ALL
SELECT s.Class,
y.ClassPath + '-' + s.Class,
y.Combinations + 1
FROM Yak AS y
INNER JOIN @Sample AS s ON s.Class > y.ClassName
...
I had a strange scenario today and I can't reproduce it.
I changed current database to adventureworks and ran following code
DECLARE @SQL NVARCHAR(200)
SET @SQL = 'SELECT DB_NAME()'
EXEC sp_executesql @SQL
EXEC (@SQL)
The sp_executesql statement returned "master" and exec statement returned "adventureworks".
Anyone knows why?
SELECT cpu_count AS [Logical CPUs],
cpu_count / hyperthread_ratio AS [Physical CPUs]
FROM sys.dm_os_sys_info