December 9, 2014 at 2:40 pm
Can some one tell me what should be the OUTPUT format of this query: I was expecting some average number and then the phrase' the average is less than 10 or greater than 10' but I just get the phrase in my results windows without the Average......?
IF OBJECT_ID (N'Avg_items_GGTS', N'P') IS NOT NULL
DROP PROCEDURE Avg_items_GGTS
go
Create Procedure Avg_items_GGTS
AS
Declare @number int
Declare @msg varchar(30)
Select @number= Avg(Price)
From items
IF @number> 10
Begin
Set @msg =Convert(int, @number) + 'Average is greater than 10'
End
Else
Begin
Set @msg = 'Average is less than 10'
end
Select @msg
GO
Thank-you
December 9, 2014 at 2:49 pm
Port1433 (12/9/2014)
Can some one tell me what should be the OUTPUT format of this query: I was expecting some average number and then the phrase' the average is less than 10 or greater than 10' but I just get the phrase in my results windows without the Average......?IF OBJECT_ID (N'Avg_items_GGTS', N'P') IS NOT NULL
DROP PROCEDURE Avg_items_GGTS
go
Create Procedure Avg_items_GGTS
AS
Declare @number int
Declare @msg varchar(30)
Select @number= Avg(Price)
From items
IF @number> 10
Begin
Set @msg =Convert(int, @number) + 'Average is greater than 10'
End
Else
Begin
Set @msg = 'Average is less than 10'
end
Select @msg
GO
Thank-you
Why so complicated for such a simple thing? And why are you using an explicit convert to convert and int to an int?
You only get one line in your output because there is only one select statement in here that is not a variable assignment.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 9, 2014 at 3:04 pm
I put something a little more simple together.
USE tempdb
GO
-- Sample Data
IF OBJECT_ID('tempdb..items') IS NOT NULL DROP TABLE items;
CREATE TABLE items (Price int);
GO
INSERT items
SELECT ABS(CHECKSUM(newid()))%10+6--5 to 15
FROM (VALUES (NULL),(NULL),(NULL),(NULL)) t(c);
GO
-- The Proc
IF OBJECT_ID (N'Avg_items_GGTS', N'P') IS NOT NULL DROP PROCEDURE Avg_items_GGTS
GO
Create Procedure Avg_items_GGTS
AS
WITH avgprice(avgprice) AS (Select Avg(Price) From items)
SELECT msg = 'Average is '+
CASE
WHEN avgprice = 10 THEN 'equal to '
WHEN avgprice > 10 THEN 'greater than '
ELSE 'less than '
END + '10.'
FROM avgprice
GO
EXEC Avg_items_GGTS
Note: Did not see Sean's response when posting this
Edit: Fixed my code... Set for average = 30, not 10... Added logic for when average = 10;
-- Itzik Ben-Gan 2001
December 9, 2014 at 3:18 pm
Alan what you posted is almost exactly what I had envisioned. I refrained from posting simply because this seems so much like homework. Let's hope the OP reads this and understands what you did.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 9, 2014 at 3:24 pm
Sean Lange (12/9/2014)
Alan what you posted is almost exactly what I had envisioned. I refrained from posting simply because this seems so much like homework. Let's hope the OP reads this and understands what you did.
I was suspecting homework too ;-).
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply