SQL Server stored procedures questions

t0ybt7op 于 11个月前发布在 SQL Server

关注(0)|答案(3)|浏览(109)

I got simple 2 SQL Server stored procedure questions that need your help. I'm a newbie to SQL Server, so I got stuck with very simple syntax errors when I execute my code

I got a table as below and I need to create a stored procedure named count_status that accepts a single argument and returns only the total number of animals of a particular conversation status and the code should not return the corresponding conservation status.

Species table

My code:

CREATE PROCEDURE count_status 
     @ConservationStatus INT
AS
  SELECT COUNT(ID) 
  FROM Species 
  WHERE ConservationStatus = @ConservationStatus
GO;
END

And I got a syntax error for the above code. Could you help me with this?

Thanks

Create a stored procedure named format_currency that accepts a character and a double number. It will return a varchar(32) with the symbol in the front, followed by the number to 2 decimal places. For example format_currency ('$',123.4) should return $123.4

I don't know how to write code for this.

Thanks.

sql-server

来源：https://stackoverflow.com/questions/55088859/sql-server-stored-procedures-questions

3条答案

按热度按时间

acruukt91#

CREATE PROCEDURE count_status 
     @ConservationStatus INT
AS
BEGIN
  SELECT COUNT(ID) 
  FROM Species 
  WHERE ConservationStatus = @ConservationStatus
END
GO;

Explanation : 1) Use Begin After As 'this begins the procedure query' 2) Use Go After end

赞(0）回复(0）举报 11个月前

yvgpqqbh2#

For #1, I haven't tested this but I think you need to use a group by clause

CREATE PROCEDURE count_status @ConservationStatus int
AS
SELECT COUNT(ID) 
FROM   Species 
WHERE  ConservationStatus = @ConservationStatus
GROUP  BY ConservationStatus
GO;

For #2, note the input parm is decimal (12, 2), that defines 2 decimal places which should both pad anything like 123.1 and trim anything like 123.123

ALTER  FUNCTION format_currency
(
    @currency_symbol varchar(1),
    @currency_amount decimal(12, 2)
)
RETURNS varchar(12)
AS
BEGIN
    RETURN @currency_symbol + ltrim(cast(cast(@currency_amount as money) as varchar))
END
GO

if you have a current version of Sql Server you can also use this

ALTER  FUNCTION format_currency
(
    @currency_symbol varchar(1),
    @currency_amount decimal(12, 2)
)
RETURNS varchar(32)
AS
BEGIN
    RETURN '$' + FORMAT(@currency_amount,'#,###,##0.00')
END
GO

赞(0）回复(0）举报 11个月前

6jjcrrmo3#

In this way we can solve the second task using stored procedure in SQL Server:

CREATE OR ALTER PROCEDURE format_currency 
    @symbol CHAR(1),
    @amount DECIMAL(18, 2),
    @fin_out VARCHAR(32) out
AS
BEGIN
    DECLARE @formatted_amount VARCHAR(32);

    SET @formatted_amount = CONCAT(@symbol, FORMAT(@amount, '0.00'));

    SELECT @formatted_amount AS FormattedCurrency;
END
GO

--To see the result
DECLARE @result VARCHAR(32)
EXEC format_currency '$', 123.4, @result OUTPUT;

Hope it helps.

赞(0）回复(0）举报 11个月前

我来回答

SQL Server stored procedures questions

3条答案

相关问题

热门标签

最新问答