SQL Server Performance

Stored Proc for Reports

Discussion in 'SQL Server 2005 General Developer Questions' started by jojupi01, Feb 10, 2011.

  1. jojupi01 New Member

    I have to write stored proc to get the daily transaction for a specified date. Right now what i do is everytime i log a transaction in table3 I update table 2 with the count. Then i use table 2 to write the stored proc and to get the desired result. I have pasted my stored proc below with the output. My problem is when another subsystem is added in the AppInfo table i need to update my stored proc because i have hardcoded the subsystem name. Can you please show me a better way to do this.



    Table1:AppInfo

    AppId SubSystem

    1 Dev
    2 Test
    3 Prod

    Table2:TransactionCount
    Id Appid LogDate DailyCount
    ----------------------------------------------------------
    1 1 11/9/2010 12:00:00 AM 2
    2 2 11/9/2010 12:00:00 AM 1
    3 3 11/9/2010 12:00:00 AM 1
    4 1 11/19/2010 12:00:00 AM 2
    5 2 11/19/2010 12:00:00 AM 1
    6 3 11/19/2010 12:00:00 AM 1


    Table3: LoggedTransactions

    Id AppId LogDate
    ------------------------------------------
    1 1 11/9/2010 12:00:00 AM
    2 2 11/9/2010 12:00:00 AM
    3 1 11/9/2010 1:00:00 PM
    4 3 11/9/2010 2:00:00 PM
    5 1 11/19/2010 12:00:00 AM
    6 2 11/19/2010 12:00:00 AM
    7 1 11/19/2010 1:00:00 PM
    8 3 11/19/2010 2:00:00 PM

    Stored Proc DailyTransCnt

    @FromDate datetime,
    @ToDate datetime

    SELECT LogDate, ISNULL([Dev],0) AS Dev, ISNULL([Test],0) AS Test,
    ISNULL([Prod],0) AS Prod
    FROM
    (SELECT LogDate, SubSystem, DailyCount from appInfo A INNER JOIN TransactionCount B ON A.AppId=B.AppId
    where (logdate >= @FromDate and logdate < DateAdd(dd,1,@ToDate)) )ps
    PIVOT
    (
    SUM (DailyCount)
    FOR [SubSystem] IN
    ( [Dev], [Test], [Prod])
    ) AS pv

    Result after executing stored proc
    ----------------------------------

    Logdate Dev Test Prod
    -----------------------------------
    2010-11-9 2 1 1
    2010-11-19 2 1 1


  2. hrishikesh_nk New Member

    Hii....
    The solution to your problem can be acheived by dynamic SQL.The below script show s how it can be acheived.This solution may not be the best and efficient solution as I am not the expert.There are risk s of SQL injection when using dynamic SQL and hence should be used cautiously.I got the sample test table from the msdn Code gallery.
    ---Create A sample Table----
    create TABLE OrderDetail
    (orderid
    int,productname
    varchar(30),productqty
    int
    )
    -- Load Sample DataINSERT
    INTO OrderDetail VALUES (1, 'Gift Card', 2)INSERT
    INTO OrderDetail VALUES (1, 'Shipping', 1)INSERT
    INTO OrderDetail VALUES (2, 'Gift Card', 2)INSERT
    INTO OrderDetail VALUES (2, 'T-Shirt', 2)INSERT
    INTO OrderDetail VALUES (2, 'Shipping', 2)insert
    into OrderDetail values (3,'sales',4)declare
    @part1 nvarchar(4000)declare
    @part2 nvarchar(4000)declare
    @part3 nvarchar(4000)declare
    @i int,@max int,@productname varchar(10)
    create table #test
    (sr_no
    int identity(1,1),productname
    varchar(100)) insert
    into #test(productname) select distinct productname from orderdetailselect @i=1,@max=MAX(sr_no) from #test set
    @part1=' SELECT orderid, 'set @part2=' from (select orderid,productname,productqty from orderdetail) as source pivot ( sum(productqty) for productname in( 'while
    (@i<=@max)begin
    select @productname=productname from #test where sr_no=@i
    set @part1=@part1+'['+@productname+'],'
    set @part2=@part2+'['+@productname+'],'
    set @i=@i+1endset
    @part3=left(@part1,LEN(@part1)-1)+left(@part2,LEN(@part2)-1)+')) as Pvt ' exec sp_executesql @part3 drop
    table #testdrop table OrderDetail
  3. jojupi01 New Member

    Hi hrishikesh_nk i tried using the pivot table.i tried doing something like thefollowing but i keep getting an error message Incorrect syntax near'@pivotIn'. Can you please tell me what i am doing wrong. Thanks.
    ALTER PROCEDURE [dbo].[getDyn]
    -- Add the parameters for the stored procedure here
    @FromDate datetime,
    @ToDate datetime

    AS
    BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    declare @cols nvarchar(150)
    declare @pivotIn nvarchar(150)
    declare @sql nvarchar(1000)
    declare @sqlParam nvarchar(100)

    select @cols =
    isnull(
    @cols+', ISNULL(['+subsystem+'],0) AS ['+subsystem+']',
    'ISNULL(['+subsystem+'],0) AS ['+subsystem+']'),
    @pivotIn =
    isnull(@pivotIn+', ['+subsystem+']', '['+subsystem+']')
    from appinfo

    SELECT LogDate, @cols
    FROM (
    SELECT LogDate, SubSystem, DailyCount
    from appInfo A INNER JOIN TransactionCount B
    ON A.AppId=B.AppId
    where (logdate >= @FromDate and logdate < DateAdd(dd,1,@ToDate))
    ) ps
    PIVOT(
    SUM (DailyCount)
    FOR [SubSystem] IN
    (@pivotIn)
    ) AS pv

    END
  4. hrishikesh_nk New Member

    Hii can you put square brackets near the @pivotln in the pivot query and then run the query.
    SELECT LogDate, @cols
    FROM (
    SELECT LogDate, SubSystem, DailyCount
    from appInfo A INNER JOIN TransactionCount B
    ON A.AppId=B.AppId
    where (logdate >= @FromDate and logdate < DateAdd(dd,1,@ToDate))
    ) ps
    PIVOT(
    SUM (DailyCount)
    FOR [SubSystem] IN
    ----brackets near @pivotln------
    ( [@pivotIn] )
    ) AS pv

  5. Madhivanan Moderator

  6. hrishikesh_nk New Member

    Hii Madhivanan,
    Its a Great Article

Share This Page