通过@Tomalak添加到注释中，下面是一个示例脚本，您可以使用该脚本来填充重量表达转换表。
CREATE TABLE dbo.Weights(
Weight varchar(20) NOT NULL
CONSTRAINT PK_Weights PRIMARY KEY
,weightingrams int NULL
,WeightInKilograms AS CAST(weightingrams / 1000.0 AS decimal(10, 2))
);
GO
INSERT INTO dbo.Weights(Weight)
SELECT DISTINCT Weight
FROM dbo.Foo;
GO
DECLARE
@Weight varchar(20)
, @weightingrams int
, @sqlExpression nvarchar(MAX);
DECLARE foo CURSOR LOCAL FAST_FORWARD FOR
SELECT weight
FROM dbo.Weights;
OPEN foo;
WHILE 1 = 1
BEGIN
FETCH NEXT FROM foo INTO @Weight;
IF @@FETCH_STATUS = -1 BREAK;
BEGIN TRY
SET @sqlExpression = 'SET @weightingrams =
' + REPLACE(REPLACE(REPLACE(@Weight, 'x', '*'), 'kg', '000'), 'g', '');
EXEC sp_executesql @sqlExpression
, N'@weightingrams int OUTPUT'
, @weightingrams = @weightingrams OUTPUT;
END TRY
BEGIN CATCH
SET @weightingrams = NULL;
END CATCH;
UPDATE dbo.Weights
SET weightingrams = @weightingrams
WHERE Weight = @Weight;
END
CLOSE foo;
DEALLOCATE foo;
GO
602392714
清零编程群