Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2006
    Posts
    6

    Unanswered: Help me to understand this SQL sentence

    Set @mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
    AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
    FROM IV_tblIVMaster
    WHERE (BalDate<= 'Exec(@mSQL + '''' + @mtxtDate + '''' + ')
    GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what is that sql sentence supposed to be doing?

    it looks like it's trying to be recursive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    If we are trying to get some value into the variable , then this query is no good.
    "Set @mSQL = 'SELECT Max([AccountID] "
    should actuallu read
    "SELECT @mSQL = Max([AccountID] "

    Hope this helps...

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    First we must identify the subject, then the verb, and if they exist, the direct object and the indirect object.....Oh, sorry.

    Any ideas what the value of @mSQL was before this assignment? Maybe the original programmer was trying to reduce the number of variables he had? (OK, I am reaching, there)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It looks to me like the code is creating a "Superkey", a concatenation of multiple natural values to fabricate a single unique semi-surrogate key.

    Superkeys are database abominations frequently found in legacy systems or in applications created by noob developers.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2006
    Posts
    6
    Hi all,
    The original procedure as follow



    --Repaired 03/11/2005

    CREATE Procedure IV_spStockReportSummary
    (
    @mName Varchar(50),
    @mtxtDate DateTime,
    @moptName TinyInt,
    @mchkReport Bit
    )

    As
    Declare @mSQL Varchar(3000)

    Set @mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
    AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + @mName + ']
    FROM IV_tblIVMaster
    WHERE (BalDate<= '
    Exec(@mSQL + '''' + @mtxtDate + '''' + ')
    GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')

    If @mchkReport=0
    Begin
    Set @mSQL='SELECT LD.AccountID, (Case When '
    Exec (@mSQL + '' + @moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
    LD.StorehouseID, (Case When ' + '' + @moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
    I.CategoryID, (Case When ' + '' + @moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End) AS CategoryName,
    LD.ItemID, (Case When ' + '' + @moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
    (Case When ' + '' + @moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End) AS Unit, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @mName + ']
    FROM IV_tblItemList I INNER JOIN CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @mName + '] LD INNER JOIN
    IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND
    LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON C.AccountID = LD.AccountID ON I.ItemID = LD.ItemID INNER JOIN IV_tblUnitOfMeasureList U ON
    I.InvUnitOfMeasr = U.UMID INNER JOIN IV_tblCategoryList CI ON I.CategoryID = CI.CategoryID INNER JOIN IV_tblStoreHouseList S ON
    LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
    GROUP BY LD.AccountID, (Case When ' + '' + @moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End), LD.StorehouseID, (Case When ' + '' + @moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
    I.CategoryID, (Case When ' + '' + @moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End),
    LD.ItemID, (Case When ' + '' + @moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End), (Case When ' + '' + @moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End), L.LotNo, L.ExpireDate
    HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
    End

    Else
    Begin
    Set @mSQL='SELECT LD.AccountID, (Case When '
    Exec (@mSQL + '' + @moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
    LD.StorehouseID, (Case When ' + '' + @moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
    LD.ItemID, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount INTO [xIV_tblStockSumTmp' + @mName + ']
    FROM CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @mName + '] LD INNER JOIN IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND
    LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON
    C.AccountID = LD.AccountID INNER JOIN IV_tblStoreHouseList S ON LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
    GROUP BY LD.AccountID, (Case When ' + '' + @moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End),
    LD.StorehouseID, (Case When ' + '' + @moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
    LD.ItemID, L.LotNo, L.ExpireDate
    HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')

    Set @mSQL = 'SELECT S.AccountID, S.AccountName, S.StorehouseID, S.StoreHouseName, S.ItemID, (Case When '
    Exec (@mSQL + '' + @moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
    (Case When ' + '' + @moptName + '' + '=1 Then U1.UMName Else U1.UMName_Secn End) AS Unit,
    I.CategoryID, (Case When ' + '' + @moptName + '' + '=1 Then C.CategoryName Else C.CategoryName_Secn End) AS CategoryName, S.LotNo, S.ExpireDate,
    (S.OnHand * (Case When V.ConvFactor IS Null Then 1 Else V.ConvFactor End)) AS OnHand, S.Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @mName + ']
    FROM IV_tblUnitOfMeasureList U1 LEFT JOIN IV_tblUMConversion V ON U1.UMID = V.UMToID RIGHT JOIN IV_tblUnitOfMeasureList U ON
    V.UMFromID = U.UMID RIGHT JOIN IV_tblItemList I ON U1.UMID = I.PrintUnitOfMeasr AND U.UMID = I.InvUnitOfMeasr LEFT JOIN
    IV_tblCategoryList C ON I.CategoryID = C.CategoryID RIGHT JOIN [xIV_tblStockSumTmp' + @mName + '] S ON I.ItemID = S.ItemID')

    End

    Return



    GO

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i can't believe that runs

    and the guy that wrote it should be shot

    it no longer looks like it's trying to be recursive

    but there's a dangling ) after the first Exec, just before If @mchkReport=0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    i can't believe that runs
    ....
    but there's a dangling ) after the first Exec, just before If @mchkReport=0
    Nor me. There look to be a lot of dangly things.

    dangquanghai - are you saying that this actually works?

    Quote Originally Posted by DanglySproc
    --Repaired 03/11/2005
    Doesn't look like it from here
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2006
    Posts
    6
    I m sure It run smoothly.

  10. #10
    Join Date
    Jul 2006
    Posts
    6
    I can give you the examble from SQL server book online

    C. Use EXECUTE 'tsql_string' with a variable
    This example shows how EXECUTE handles dynamically built strings containing variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables (type = U).



    Note This example is shown for illustrative purposes only.


    DECLARE tables_cursor CURSOR
    FOR
    SELECT name FROM sysobjects WHERE type = 'U'
    OPEN tables_cursor
    DECLARE @tablename sysname
    FETCH NEXT FROM tables_cursor INTO @tablename
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    /* A @@FETCH_STATUS of -2 means that the row has been deleted.
    There is no need to test for this because this loop drops all
    user-defined tables. */.
    EXEC ('DROP TABLE ' + @tablename)
    FETCH NEXT FROM tables_cursor INTO @tablename
    END
    PRINT 'All user-defined tables have been dropped from the database.'
    DEALLOCATE tables_cursor

  11. #11
    Join Date
    Jul 2003
    Posts
    123
    Uh-Oh... you said the forbidden word... "cursor"...

  12. #12
    Join Date
    Jul 2006
    Posts
    6
    Oh sorry friends !!!
    this sentence
    Set @mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
    AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
    FROM IV_tblIVMaster
    WHERE (BalDate<= 'Exec(@mSQL + '''' + @mtxtDate + '''' + ')
    GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')

    contain two sentences
    1. Set @mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
    AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
    FROM IV_tblIVMaster
    WHERE (BalDate<= '

    2.'Exec(@mSQL + '''' + @mtxtDate + '''' + ')
    GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')

    The coder typed it at the same row so it make me confuse
    Now, It is so clear
    Thank for your consideration

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by oneleg_theone
    Uh-Oh... you said the forbidden word... "cursor"...
    I think that was to explain to us what EXEC does
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ah. So that is what EXEC does. I had no idea. Apparently it is a convenient method for f***ing up an application. The posted code demonstrates it clearly.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •