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')what is that sql sentence supposed to be doing?

it looks like it's trying to be recursive|||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...|||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)|||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.|||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|||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|||i can't believe that runs
...
but there's a dangling ) after the first Exec, just before If @.mchkReport=0Nor me. There look to be a lot of dangly things.

dangquanghai - are you saying that this actually works?

--Repaired 03/11/2005
Doesn't look like it from here|||I m sure It run smoothly.|||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|||Uh-Oh... you said the forbidden word... "cursor"...|||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|||Uh-Oh... you said the forbidden word... "cursor"...I think that was to explain to us what EXEC does :)|||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.