sql-server - SQLCMD PowerShell仅需要返回值(不包含标题)

Question

在PowerShell中运行以下sqlcmd:

$unassignedCnt = sqlcmd -W -k 1 -S MSSQLSERVER -E -Q "SET NoCount ON; SELECT   Count(DISTINCT SP.SubnetKey) AS 'UnassignedCount' FROM [Control].[dbo].[Subnet] AS SP Left OUTER JOIN [Control].[rw_current].[QIP_Subnet] AS Q ON SP.KeyHash = Q.__key_Hash; SET NoCount OFF; "
Write-Host $unassignedCnt

这将返回以下格式化结果:

UnassignedCount --------------- 1

我只想拥有:

1

我可以通过SQL命令实现此功能还是需要对结果进行后期处理？如果是这样，最简单的方法是什么？

Answer 1

您可以通过将SQL查询更改为使用PRINT而不是SELECT来执行此操作

SELECT将创建一个表，该表具有与之关联的标题和数据。
PRINT仅输出结果。

$unassignedCnt = sqlcmd -W -k 1 -S MSSQLSERVER -E -Q "SET NoCount ON; DECLARE @result varchar(max); SELECT  @result= Count(DISTINCT SP.SubnetKey) FROM [Control].[dbo].[Subnet] AS SP Left OUTER JOIN [Control].[rw_current].[QIP_Subnet] AS Q ON SP.KeyHash = Q.__key_Hash; PRINT @result ;SET NoCount OFF; "
Write-Host $unassignedCnt