why is the diference between a select and print output?
-
PRINT probably formats to your local environmental set-up because PRINT denotes that a human wants to see the data. SELECT is used internally and doesn't need the nice formatting for the server to do what it needs to.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
In addition the SELECT generates a recordset whereas PRINT returns to the message buffer.
-
Another adition...
You can use SELECT statement in place of a PRINT statement to get some readable output OR in place of a SET statement to set variable values...
The diferences are that SELECT statement, as it produces a recordset as Antares said, also modifies the values of some internal variables like @@ROWCOUNT and @@ERROR (please, see BOL for details about them).
So, if you have a query like
DECLARE @a int
SET @a = 5
SELECT @a = 1/0 -- Produces an error
PRINT @a
IF @@ERROR <> 0
PRINT 'RESULT: ERROR'
ELSE PRINT 'RESULT: NO ERROR'
the output would be
Servidor: mensaje 8134, nivel 16, estado 1, línea 5
Divide by zero error encountered.
5
RESULT: NO ERROR
But, if you change the PRINT @a for a SELECT @a, the output will be:
Servidor: mensaje 8134, nivel 16, estado 1, línea 3
Divide by zero error encountered.
-----------
5,00
RESULT: NO ERROR
Nicolas
-
Here's a couple of other differences...
PRINT doesn't print those pesky column headers.
PRINT outputs to the MESSAGES tab even if the GRID mode is turned on. SELECT will print to the GRID if the GRID mode is turned on.
PRINT will only print one line at a time.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff you can insert ASCII codes into the print statement if you need special characters or want to generate a multi line statement. Char(10) is the ASCII code for a line feed.
Print 'Hello' + char(10)+ 'World'
Hello
World
DECLARE @intCount int
SET @intCount = 1
While @intCount <5
Begin
Print 'Hello' + char(10)+ 'World'+Char(10)
SET @intCount = @intCount + 1
end
Prints Hello World on 2 lines then adds a line feed
and prints it 3 more times.
Edit /*
OutPuts Hello on 1 line and world on another and does not insist on adding a pesky double line feed like this text
messenger. Oh for the good old days where you could explicity format your output in C. Well maybe not that was a PITA */ Mike
-
Hector you can format your output from a select statement by using Convert and some of the date functions.
HTH
Mike
IF Object_ID('TempDB..#Test') > 0
DROP TABLE #Test
CREATE TABLE [#Test]
(
[PkNumber] int IDENTITY (1, 1) NOT NULL,--
[SomeTimeData]DateTime NOT NULL,
)
Go
--Add Test data
DECLARE @Count int
DECLARE @DateStart DateTime
Set @Count=0
Set @DateStart = '6/6/2005 9:50AM'
While @Count < 5
Begin
Set @Count = @Count + 1
INSERT INTO #Test (SomeTimeData)
Values(@DateStart)
Set @DateStart =DateAdd(mi,30,@DateStart)
End
GO
--formatted
SELECT DateName(dw,A.SomeTimeData)AS "Day",
Convert(Char(17),A.SomeTimeData)AS "Open Dates" --Change Char(17) to Char(20)
--To display AM or PM
FROM
#Test AS A
--Returns Monday
--Jun 6 2005 9:50
--not formatted
SELECT * FROM #Test
--Returns Date as
--2005-06-06 09:50:00.000
DROP TABLE #Test
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply