asked on

Insert from Stored Procedure where some field/s > 0

Hi

i am trying to create stored procedure but getting syntax error and dont know what am i doing wrong,

CREATE PROCEDURE Get_Save_ComSon 

@IDTNo float (10),            --Input parameter
@BkgDate as date,
@BasicFRT AS INT 
AS
BEGIN

	SET NOCOUNT ON;


	INSERT INTO [dbo].[CmsonDtl] SELECT [IDTNo]
      ,[ConName]
      ,[Address]
      ,SUM(CASE WHEN [CmsonVasuli] > 0 THEN (AmtTTL) + (ExpTTL) * (CmsonVasuli) / 100 ELSE [CmsonArt] > 0 THEN (CmsonArt) * (Article) ELSE [CmsonWeight] > 0 THEN ([Weight]) * [CmsonWeight] ELSE '0' END) as CMson
      ,@BkgDate
      ,[Description]
      ,SUM(Article)
      ,Sum([Weight])
      ,CASE WHEN [CmsonVasuli] > 0 THEN 'Vasuli' ELSE [CmsonArt] > 0 THEN 'Article' ELSE [CmsonWeight] > 0 THEN 'Weight' ELSE '0' END as CmsonMode
      ,@BasicFRT
      ,@GroupNameCOM
      FROM [ComsonOTHRView]
      WHERE IDTNo = @IDTNo
END

Open in new window

please assist me

Pawan Kumar

Try.. You have syntax error with Case

CREATE PROCEDURE Get_Save_ComSon 

@IDTNo float (10),            --Input parameter
@BkgDate as date,
@BasicFRT AS INT 
AS
BEGIN

	SET NOCOUNT ON;


	INSERT INTO [dbo].[CmsonDtl] 
	
	SELECT [IDTNo]
      ,[ConName]
      ,[Address]
      ,SUM(CASE 
	  WHEN [CmsonVasuli] > 0 THEN (AmtTTL) + (ExpTTL * CmsonVasuli) / 100 
	  WHEN [CmsonArt] > 0 THEN (CmsonArt) * (Article) 
	  WHEN [CmsonWeight] > 0 THEN ([Weight]) * [CmsonWeight] ELSE 0 END) 
	  as CMson
      ,@BkgDate
      ,[Description]
      ,SUM(Article)
      ,Sum([Weight])
      ,CASE 
	  WHEN [CmsonVasuli] > 0 THEN 'Vasuli' 
	  WHEN [CmsonArt] > 0 THEN 'Article' 
	  WHEN [CmsonWeight] > 0 THEN 'Weight' 
	  ELSE '0' END as CmsonMode
      ,@BasicFRT
      ,@GroupNameCOM
      FROM [ComsonOTHRView]
      WHERE IDTNo = @IDTNo
END

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Added Query Syntax to Topics.

Note - I hope you have same number of columns that you are selecting from the table.

awking00

I addition to the case statements needing corrections, since you have three aggregate functions in your select, I believe you will also need a group by statement of the non-aggregated attributes.

Pawan Kumar

Dear crystal_Tech,
Have you seen my solution which is same as the solution you choose.?

Did you find any issues with it?

Regards,
Pawan

Altaf Patni

ASKER

Please open this question , as want to clear some issue

Pawan Kumar

Hi,

Please tell us , what is your question ?

Regards,
Pawan

Insert from Stored Procedure where some field/s &gt; 0

Insert from Stored Procedure where some field/s > 0