CODEWITHSUNDEEP
sql-serverstored-procedures
Venkatvasan
Venkatvasan

Reputation: 491

what is the error in this stored procedure

Hi I am using a Pivot in SP but I cannot execute this

GO
/****** Object:  StoredProcedure [dbo].[Ascend_sp_rpt_CashierTransactionAmt]    Script Date: 07/23/2016 12:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Venkat>
-- Create date: <Create Date,23-07-2016,>
-- Description: <Description,Cashier Transaction Amount Reports ,>
-- =============================================
ALTER PROCEDURE [dbo].[Ascend_sp_rpt_CashierTransactionAmt]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * FROM(
SELECT
b.Sale_Rate,b.Type,b.Type_Code,CASE 
WHEN 1=0 then convert(char(19),b.LogTime,20)
ELSE convert(char(10),b.LogTime,20)
END 'LogDate' from tbllog b)a
PIVOT
(
       Sum(Sale_Rate)
       FOR Type IN ([S], [D], [C])
) AS P 
END

The error shows as 

Msg 208, Level 16, State 6, Procedure Ascend_sp_rpt_CashierTransactionAmt, Line 14
Invalid object name 'dbo.Ascend_sp_rpt_CashierTransactionAmt'.

When I removed the CASE statement it executed.Don't know Why.

Upvotes: 0

Views: 57

Answers (1)

Igor
Igor

Reputation: 62213

The stored proc Ascend_sp_rpt_CashierTransactionAmt

  1. Does not exist in the database context in which you are running. Check you are pointing to the correct database.
  2. Or the stored proc does not exist under the schema dbo, if you initially created it without dbo with a user that has a non dbo default schema then it will be created in that schema. For more about schemas see SQL Server Best Practices – Implementation of Database Object Schemas

The ALTER means you are trying to modify an existing stored procedure so if it does not already exist for whatever reason the statement will fail. If it does not exist yet then replace the ALTER keyword with CREATE.

Finally end your creation/alter statement with the keyword GO. See Create a Stored Procedure for additional syntax examples.

Upvotes: 2

Related Questions