在SQL Server中处理查询结果

Is there any way to execute a query in SQL Server and then print a message that contains data from the query results.

有没有办法在SQL Server中执行查询,然后打印包含查询结果数据的消息。

For example, if I had a query like this:

例如,如果我有这样的查询:

SELECT ID, FirstName, LastName FROM Customers

I'd like to follow it with something like this, for each row of the results.

对于结果的每一行,我想用这样的东西来跟着它。

Print N'Customer ID: ' + [ID]
Print N'First Name: ' + [FirstName]
Print N'Last Name: ' + [LastName]

I know I can use the AS clause to format each column, but the data will always be formatted in a table. Is there any way to format the results differently without using another language to process the results?

我知道我可以使用AS子句格式化每一列,但数据将始终在表格中格式化。有没有办法以不同的方式格式化结果而不使用其他语言来处理结果?

3 个解决方案

#1

You could use a cursor:

你可以使用游标:

declare @curse cursor
set @curse = cursor fast_forward for
select ID, FirstName, LastName
from Customers

declare @id int,
        @firstName varchar(32),
        @lastName varchar(32)

open @curse

fetch next from @curse into @id, @firstName, @lastName

while (@@fetch_status = 0)
begin
    Print N'Customer ID: ' + @id
    Print N'First Name: ' + @firstName
    Print N'Last Name: ' + @lastName

    fetch next from @curse into @id, @firstName, @lastName
end

close @curse

Or you could do it without:

或者你可以没有:

select 'Customer ID: ' + cast(ID as varchar(32)) + char(13) + char(10) + 
       'First Name: ' + FirstName + char(13) + char(10) + 
       'Last Name: ' + LastName + char(13) + char(10)
from Customers

Use Ctrl + T to change your output in SSMS from Grid to Text mode if you use this second way.

如果使用第二种方法,请使用Ctrl + T将SSMS中的输出从网格更改为文本模式。

#2

You can try this:

你可以试试这个:

DECLARE @customerid varchar(100);
DECLARE @firstname varchar(100);
DECLARE @@lastname varchar(100);

SELECT @customerid=ID, @firstname=FirstName, @lastname=LastName 
FROM Customers

Print N'Customer ID: ' + @customerid
Print N'First Name: ' +  @firstname
Print N'Last Name: ' + @lastname

#3

If all you need is a vertical formatting of the data, then I would still recommend using SELECT over PRINT:

如果您只需要垂直格式化数据,那么我仍然建议使用SELECT over PRINT:

SELECT Lbl, Txt
FROM (
              SELECT ID, 'Customer ID:' As Lbl, CAST(ID As VARCHAR(50)) As Txt       FROM Customers
    UNION ALL SELECT ID, 'First Name:' As Lbl, CAST(FirstName As VARCHAR(50)) As Txt FROM Customers
    UNION ALL SELECT ID, 'Last Name:' As Lbl, CAST(FirstName As VARCHAR(50)) As Txt  FROM Customers
    ) A
ORDER BY ID, Lbl

#1