CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Mike
Mike

Reputation: 13

I can't seem to get the syntax correct to call a stored procedure in a Select statement

I have a simple stored procedure that i want to use the output in a formula.

My stored procedure is as follows:

USE [MGCustom]
GO
/****** Object:  StoredProcedure [dbo].[stpGetCogAmt]    Script Date: 4/10/2019 4:55:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Mike Ruoppoli
-- Create date: 
-- Description: <Procedure to get the Cogs value based on the month,Values to be included in a table later.>
-- =============================================
ALTER PROCEDURE [dbo].[stpGetCogAmt]
    -- Add the parameters for the stored procedure here
    @ProdMonth int
,   @CogsRate DECIMAL(4,4)  OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here

    --@CogsPerLb, @p2
    Set @CogsRate =(CASE @ProdMonth 
            WHEN 1 THEN 0.0957 WHEN 2 THEN 0.0904 WHEN 3 THEN 0.0884 WHEN 4 THEN 0.0906 WHEN 5 THEN 0.0818 WHEN 6 THEN 0.0787 WHEN 7 THEN 0.0777 WHEN 8 THEN 0.0769 WHEN 9 THEN 0.0756 WHEN 10 THEN 0.0778
            WHEN 11 THEN 0.0754 WHEN 12 THEN 0.0787 ELSE .08 END) 

END

The stored procedure runs and returns the value as expected but when i try to use in this context i keep getting the Incorrect syntax near 'EXEC'

More Code

SELECT dbo.vwUD15.Date01, (EXEC dbo.stpGetCogAmt @ProdMonth = "(LEFT(dbo.vwUD15.Key2, LEN(dbo.vwUD15.Key2) - 5))") AS CogsRate
FROM dbo.vwUD15

I searched a good bit and cannot figure out what i am doing wrong here.

I will be using this sproc in quite a few formulas...

Upvotes: 1

Views: 84

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

One of the comments in the code points towards a far better way of modelling this:

create table CogRates (
    ProdMonth int not null,
    CogsRate decimal(4,4) not null,
    constraint PK_CogRates PRIMARY KEY (ProdMonth),
    constraint CK_CogRates_Months CHECK (ProdMonth between 1 and 12)
)
go
insert into CogRates (ProdMonth,CogsRate) values
(1 ,0.0957),
(2 ,0.0904),
(3 ,0.0884),
(4 ,0.0906),
(5 ,0.0818),
(6 ,0.0787),
(7 ,0.0777),
(8 ,0.0769),
(9 ,0.0756),
(10,0.0778),
(11,0.0754),
(12,0.0787)
go
SELECT v.Date01, cg.CogsRate AS CogsRate
FROM dbo.vwUD15 v
    inner join
    CogRates cg
        on cg.ProdMonth = LEFT(v.Key2, LEN(v.Key2) - 5)

Don't write procedural code when set-based code will do the job.

Stored procedures cannot be composed into larger queries. You could have moved your logic into a user-defined function but when it's basically a lookup, that's the sort of thing that SQL excels at. (Although it would be preferable if we weren't doing manipulation of a column's value on the right hand side of that comparison)

In general, if you're considering writing a function (not a stored proc) and the definition is deterministic and the domain is "small", consider using a table instead. As a bonus, you also get the function's inverse for free, if you need that.

Upvotes: 1

Related Questions