Stored Procedure

• jagjitsingh

  Ten Centuries

  Points: 1059

  More actions

  October 28, 2017 at 4:29 am

  #326030

  Hi
    I have below Stored Procedure. I want
  
USE [Test]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  CREATE PROCEDURE Sp_Test
  @fromDate datetime,
  @toDate datetime,
    @Location nvarchar(10)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

      SELECT A.[No_] as 'Account No' , A.[Name] as 'Description' from [Test].[dbo].[Table1] as A
    inner join [Test].[dbo].[Table2] as B on A.No_ = B.[G_L Account No_] where B.[Posting Date] <= @toDate
    and [Location Code] = @Location
    group by A.No_

  END
GO


  if Date in alias b is less than frdate than Amount value gets stored in variable oamount.
  if Date Between @frdate and @todate than Amount value gets stored in tamount.

  There are more than 80 lakh records and wamt performance should be fast
  Secondly Location Parameter will have multiple values say Location1,Location4 passed . What should be its DataType.

  Values returned will be Location , oamount,tanount,AccountNo,Description

  Thanks

• Thom A

  SSC Guru

  Points: 99130

  More actions

  October 28, 2017 at 5:15 am

  #1965655

  I think the problem is a language barrier, however, I'm not entirely sure what you mean by your statements:

  if Date in alias b is less than frdate than Amount value gets stored in variable oamount.
  if Date Between @frdate and @todate than Amount value gets stored in tamount.

  You don't have a variable oamount in your SP; is this a variable outside of your SP? Does @oamount need to be an output parameter you can return to the calling query? Where is tamount? Are you wanting to update the table? What table contains the column tamount, and what value should it be updated with?

  At guess, I think what you actually mean to say is something along the lines of this (wording in Italics are total guesswork):

  If the value of the column Date in Table2 has a value less than @fromdate then the value of the column amount, in table2, needs to be returned, with the alias oamount. If not, the value should be NULL.
  If the value of the column Date in Table1 has a value between @fromdate and @Todate then the value of the column Amount, in Table1, needs to returned with the alias tamount. If not, the value should be NULL.

  If that is what you're after, this should get your what you want, using a CASE expression:

  SELECT A.[No_] AS 'Account No' , A.[Name] AS 'Description',

     CASE WHEN B.[Date] < @FromDate THEN B.Amount END AS oamount,

     CASE WHEN A.[Date] BETWEEN @FromDate AND @ToDate THEN A.Amount END AS tamount

  from [Test].[dbo].[Table1] AS A

    INNER JOIN [Test].[dbo].[Table2] B ON A.No_ = B.[G_L Account No_]

  WHERE B.[Posting Date] <= @toDate

  AND [Location Code] = @Location;

  --GROUP BY A.No_; -- This Group by is achieving nothing. In fact, your query won't run with it there.

  What is the aim of your GROUP BY though? GROUP By is used to often aggregate data. In simple terms:

  CREATE TABLE #Sales (EmpName varchar(10), Sales int);

  GO

  INSERT INTO #Sales

  VALUES

    ('Jane',10),

    ('Jane',12),

    ('John',7);

  GO

  SELECT *

  FROM #Sales;

  GO

  SELECT EmpName, SUM(Sales) AS TotalSales

  FROM #Sales

  GROUP BY EmpName;

  GO

  DROP TABLE #Sales;

  GO

  Notice in the second result set the results are "grouped" by EmpName, allowing you to aggregate the the number of sales each employee made. in this case, Jane's total sales were 22, where as John remains at 7 (as they only had one entry for sale volumes).

  If this isn't what you're after, can you try to word your question more clearly please? It seemed in your question that you were using the words Variable and Column interchangeably. Columns and Variables are very different things. One is a property of a table, and holds data data for every row inside that table, and exists for as long as the table does. The other normally holds one item of one data type (although table variables exist) and normally only exist for the duration of a batch; once that batch finishes (the script ends, or in SSMS you reach a GO command) the variable no longer exists.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  October 28, 2017 at 6:56 am

  #1965660

  jagjitsingh - Saturday, October 28, 2017 4:29 AM

  Hi
    I have below Stored Procedure. I want
  
  USE [Test]
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO

  CREATE PROCEDURE Sp_Test
    @fromDate datetime,
    @toDate datetime,
      @Location nvarchar(10)
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      SELECT A.[No_] as 'Account No' , A.[Name] as 'Description' from [Test].[dbo].[Table1] as A
      inner join [Test].[dbo].[Table2] as B on A.No_ = B.[G_L Account No_] where B.[Posting Date] <= @toDate
      and [Location Code] = @Location
      group by A.No_

  END
GO


  if Date in alias b is less than frdate than Amount value gets stored in variable oamount.
  if Date Between @frdate and @todate than Amount value gets stored in tamount.

  There are more than 80 lakh records and wamt performance should be fast
  Secondly Location Parameter will have multiple values say Location1,Location4 passed . What should be its DataType.

  Values returned will be Location , oamount,tanount,AccountNo,Description

  Thanks

  Simply turn this procedure into a inline table valued function which lessens the execution overhead, the logic is very simple so this should be easy.
  😎