SQL For Loop Not Working

Jul 8, 2007

Ive got a gridview of options that the user can select. It displays all the networks that the user is in. When the user clicks "submit" button my code (below) does a loop for every gridview row in the gridview.It finds the checkbox, and the label (the name of the network) if the checkbox is not checked it does nothing (or is supposed to) if it is checked it is meant to add a network link to my database, when the check box is checked the item is entered into the database fine but when the user unchecks the checkbox is still enters it. Any ideas whats going wrong? thanks si!

            foreach (GridViewRow row in GridView1.Rows)
            {
                CheckBox chk = row.Cells[0].FindControl("CheckBox1") as CheckBox;
                Label lbl = row.Cells[0].FindControl("Label7") as Label;
                if (chk.Checked == false)
                {
                    //do nothing cos they arent going in that network.

                }
                else if(chk.Checked == true)
                {   //do something cos its going in the selected network....
                    //get the network id
                    SqlCommand TypeCommand2 = new SqlCommand("SELECT NetworkID FROM Networks WHERE Name = '" + lbl.Text + "'", Connection);
                    Reader = TypeCommand2.ExecuteReader();
                    Reader.Read();
                    networkid = (int)Reader.GetValue(0);
                    Reader.Close();
                    SqlCommand AddNetworks = new SqlCommand("INSERT INTO Networks (ItemID, NetworkID) VALUES ('" + ItemID + "','" + networkid + "')", Connection);
                    AddNetworks.ExecuteNonQuery();

                }
            }

View 6 Replies


ADVERTISEMENT

WHILE Loop Not Working

May 10, 2007

Evening everyone. I am having problems with my WHILE loops.

When I paste the following into the MS SQL query window I get an error message saying there is a problem near 'CURSOR'.


Code:


SELECT Course_ID, Course_Name
INTO CursorTable
FROM NorcatCourses
WHERE (Course_Name LIKE 'XNMM%') AND (Course_Name NOT LIKE '%Presentation')
ORDER BY Course_Name;

/* Declare the cursor */
DECLARE Cursor_original CURSOR
SCROLL
FOR
SELECT Course_ID, Course_Name
FROM CursorTable;

/* Declare the temporary cursor variables
* to be used
*/

DECLARE @Course_ID int;
DECLARE @Course_Name nvarchar(50);

/* Declare tepmorary variables for the cursor copy */
DECLARE @Course_ID_copy int;
DECLARE @Course_Name_copy nvarchar(50);

/* Open the cursor and fetch the first record */
OPEN Cursor_original;
FETCH NEXT FROM Cursor_original INTO @Course_ID, @Course_Name;

WHILE (@@FETCH_STATUS = 0) /* Outer Loop */
BEGIN

/* Declare a second copy of the cursor to be used
* for comparison
*/
DECLARE Cursor_copy CURSOR
SCROLL
FOR
SELECT *
FROM CursorTable;

/* Open Cursor_copy and fetch the first record */
OPEN Cursor_copy;
FETCH NEXT FROM Cursor_copy INTO @Course_ID_copy, @Course_Name_copy;

WHILE (@@FETCH_STATUS = 0) /* Inner Loop */
BEGIN

IF (@Course_Name LIKE '%' + @Course_Name_copy + '%') AND
(@Course_ID < @Course_ID_copy)
BEGIN

DELETE CursorTable
WHERE CURRENT OF Cursor_original;

END;

FETCH NEXT FROM Cursor_copy INTO @Course_ID_copy, @Course_Name_copy;
END; /* Inner Loop */

/* Clean up */
CLOSE Cursor_copy;
DEALLOCATE Cursor_copy;

FETCH NEXT FROM Cursor_original INTO @Course_ID, @Course_Name;
END; /* Outer Loop */

/* Clean up */
CLOSE Cursor_original;
DEALLOCATE Cursor_original;
/* DROP TABLE CursorTable; */



According to all of the tutorials I've read my syntax for a WHILE loop appears to be correct. Where am I going wrong?

Thanks.

View 1 Replies View Related

Help Needeed: For Each Loop Container Not Working

Nov 19, 2007

Hi
I am using a foreach loop container and Data flow task dropped on it.
According to the logic the dataflow task should be executed twice and for eacl loop fetches 2 records in the variable used in the sql in Data flow task. It works fine except that it does not iterate twice.It simply executes once.
Please help

View 2 Replies View Related

For Each Loop And Script Task Not Working Properly

Oct 5, 2007

Hi,

I have a FOR EACH LOOP that loops through a bunch of folders in search of particular filename (including path). That part works fine.

Then, INSIDE the FOR EACH LOOP, I have a script task that evaluates the filename and grabs the date portion of the filename. That part works fine too.

Now, here's my dilemma. If the date portion of the file name IS GREATER THAN the the value of another date in a variable, I want it to load the file into a table using a data flow task.

So, this is what my package looks like:

[ FOR EACH LOOP (Script task --- evaluate the date using an expression (see below) ---> Data flow task) ]

My expression looks like this:

@[User::fileDate] > @[User::maxLoadDate]

You think that this would work, right??

However, after the VERY FIRST iteration of the loop, the script task turns green, then it goes right to the data flow task, which then says that the file name is "", and can't be loaded. (I'm not sure why this is occuring)

My question is, is my logic correct? Can I use an expression inside the for each loop and have it work?





View 1 Replies View Related

Stored Procedure Loop Not Working, Please Advise, Code Attached

Apr 13, 2008

This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ? Thanks so much.CREATE PROCEDURE Table_1TT_1T                    (@PartNo                 varchar(20),                 @Wkorder        varchar(10),                 @Setup        datetime,                 @Line        smallint,                 @TT        integer,                 @Tester        smallint,                 @LT1        integer,                 @LT2        integer,                 @LT3        integer,                 @LT4        integer,                 @LT5        integer,                 @LT6        integer,                 @LT7        integer,                 @LT8        integer,                 @LT9        integer,                 @LT10        integer,                 @LT11        integer,                 @LT12        integer,                 @LT13        integer,                 @LT14        integer,                 @LT15        integer,                 @LT16        integer,                 @LT17        integer,                 @LT18        integer,                 @LT19        integer,                 @LT20        integer,                 @LT21        integer,                 @LT22        integer,                 @LT23        integer,                 @LT24        integer,                 @LT25        integer,                 @LT26        integer,                 @LT27        integer,                 @LT28        integer,                 @LT29        integer,                 @LT30        integer,                 @LT31        integer,                 @LT32        integer,                 @LT33        integer,                 @LT34        integer,                 @LT35        integer,                 @LT36        integer,                 @UnitFound        integer        OUT,                         @parameters_LamType         varchar(50)       OUT,                 @parameters_Shunt        real               OUT,                 @parameters_ShuType     varchar(50)       OUT,                 @parameters_Stack        real              OUT,                 @parameters_Steel          varchar(50)       OUT,                 @Partno11            varchar(20)    OUT,                 @Wkorder11            varchar(10)    OUT,                 @Partno12            varchar(20)    OUT,                 @Wkorder12            varchar(10)    OUT,                 @Partno24            varchar(20)    OUT,                 @Wkorder24            varchar(10)    OUT,                 @Partno29            varchar(20)    OUT,                 @Wkorder29            varchar(10)    OUT,                 @Partno34            varchar(20)    OUT,                 @Wkorder34            varchar(10)    OUT,                 --@DL1        integer        OUT,                 --@DL2        integer        OUT,                 --@DL3        integer        OUT,                 --@DL4        integer        OUT,                 --@DL5        integer        OUT,                 --@DL6        integer        OUT,                 --@DL7        integer        OUT,                 --@DL8        integer        OUT,                 --@DL9        integer        OUT,                 --@DL10        integer        OUT,                 @DL11        integer        OUT,                 @DL12        integer        OUT,                 --@DL13        integer        OUT,                 --@DL14        integer        OUT,                 --@DL15        integer        OUT,                 --@DL16        integer        OUT,                 --@DL17        integer        OUT,                 --@DL18        integer        OUT,                 --@DL19        integer        OUT,                 --@DL20        integer        OUT,                 --@DL21        integer        OUT,                 --@DL22        integer        OUT,                 --@DL23        integer        OUT,                 @DL24        integer        OUT,                 --@DL25        integer        OUT,                 --@DL26        integer        OUT,                 --@DL27        integer        OUT,                 --@DL28        integer        OUT,                 @DL29        integer        OUT,                 --@DL30        integer        OUT,                 --@DL31        integer        OUT,                 --@DL32        integer        OUT,                 --@DL33        integer        OUT,                 @DL34        integer        OUT)                 --@DL35        integer        OUT,                 --@DL36        integer        OUT)ASSET @Tester = 1WHILE @Tester < 36      BEGIN    Set @Line = (Select Line from dbo.location where Tester = @Tester)        IF @Line = 453        BEGIN        If @Tester = 1 BEGIN SET @LT1 = 453 END        If @Tester = 2 BEGIN SET @LT2 = 453 END        If @Tester = 3 BEGIN SET @LT3 = 453 END        If @Tester = 4 BEGIN SET @LT4 = 453 END        If @Tester = 5 BEGIN SET @LT5 = 453 END        If @Tester = 6 BEGIN SET @LT6 = 453 END        If @Tester = 7 BEGIN SET @LT7 = 453 END        If @Tester = 8 BEGIN SET @LT8 = 453 END        If @Tester = 9 BEGIN SET @LT9 = 453 END        If @Tester = 10 BEGIN SET @LT10 = 453 END        If @Tester = 11 BEGIN SET @LT11 = 453 END        If @Tester = 12 BEGIN SET @LT12 = 453 END        If @Tester = 13 BEGIN SET @LT13 = 453 END        If @Tester = 14 BEGIN SET @LT14 = 453 END        If @Tester = 15 BEGIN SET @LT15 = 453 END        If @Tester = 16 BEGIN SET @LT16 = 453 END        If @Tester = 17 BEGIN SET @LT17 = 453 END        If @Tester = 18 BEGIN SET @LT18 = 453 END        If @Tester = 19 BEGIN SET @LT19 = 453 END        If @Tester = 20 BEGIN SET @LT20 = 453 END        If @Tester = 21 BEGIN SET @LT21 = 453 END        If @Tester = 22 BEGIN SET @LT22 = 453 END        If @Tester = 23 BEGIN SET @LT23 = 453 END        If @Tester = 24 BEGIN SET @LT24 = 453 END        If @Tester = 25 BEGIN SET @LT25 = 453 END        If @Tester = 26 BEGIN SET @LT26 = 453 END        If @Tester = 27 BEGIN SET @LT27 = 453 END        If @Tester = 28 BEGIN SET @LT28 = 453 END        If @Tester = 29 BEGIN SET @LT29 = 453 END        If @Tester = 30 BEGIN SET @LT30 = 453 END        If @Tester = 31 BEGIN SET @LT31 = 453 END        If @Tester = 32 BEGIN SET @LT32 = 453 END        If @Tester = 33 BEGIN SET @LT33 = 453 END        If @Tester = 34 BEGIN SET @LT34 = 453 END        If @Tester = 35 BEGIN SET @LT35 = 453 END        END        SET @Tester = @Tester + 1      ENDSELECT       @parameters_LAMTYPE = LAMTYPE,       @parameters_SHUNT = SHUNT,       @parameters_SHUTYPE = SHUTYPE,       @parameters_STACK = STACK,       @parameters_STEEL = STEEL    FROM DBO.PARAMETERS A    INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF    WHERE B.PARTNO = @PARTNO    SET @UnitFound = @@rowcountIF @UnitFound = 0    BEGIN            SELECT               @parameters_LAMTYPE = LAMTYPE,               @parameters_SHUNT = SHUNT,               @parameters_SHUTYPE = SHUTYPE,               @parameters_STACK = STACK,               @parameters_STEEL = STEEL            FROM DBO.PARAMETERS            WHERE PARTNO = @PARTNO            SET @UnitFound = @@rowcount            END        --IF @LT1 = @Line  BEGIN SET @DL1 = 1 END        --IF @LT2 = @Line  BEGIN SET @DL2 = 1 END        --IF @LT3 = @Line  BEGIN SET @DL3 = 1 END        --IF @LT4 = @Line  BEGIN SET @DL4 = 1 END        --IF @LT5 = @Line  BEGIN SET @DL5 = 1 END        --IF @LT6 = @Line  BEGIN SET @DL6 = 1 END        --IF @LT7 = @Line  BEGIN SET @DL7 = 1 END        --IF @LT8 = @Line  BEGIN SET @DL8 = 1 END        --IF @LT9 = @Line  BEGIN SET @DL9 = 1 END        --IF @LT10 = @Line  BEGIN SET @DL10 = 1 END        IF @LT11 = 453  BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END        --IF @LT11 = @Line  BEGIN SET @DL11 = 1 END        IF @LT12 = 453  BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END        --IF @LT13 = @Line  BEGIN SET @DL13 = 1 END        --IF @LT14 = @Line  BEGIN SET @DL14 = 1 END        --IF @LT15 = @Line  BEGIN SET @DL15 = 1 END        --IF @LT16 = @Line  BEGIN SET @DL16 = 1 END        --IF @LT17 = @Line  BEGIN SET @DL17 = 1 END        --IF @LT18 = @Line  BEGIN SET @DL18 = 1 END        --IF @LT19 = @Line  BEGIN SET @DL19 = 1 END        --IF @LT20 = @Line  BEGIN SET @DL20 = 1 END        --IF @LT21 = @Line  BEGIN SET @DL21 = 1 END        --IF @LT22 = @Line  BEGIN SET @DL22 = 1 END        --IF @LT23 = @Line  BEGIN SET @DL23 = 1 END        IF @LT24 = 453  BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END        --IF @LT25 = @Line  BEGIN SET @DL25 = 1 END        --IF @LT26 = @Line  BEGIN SET @DL26 = 1 END        --IF @LT27 = @Line  BEGIN SET @DL27 = 1 END        --IF @LT28 = @Line  BEGIN SET @DL28 = 1 END        IF @LT29 = 453  BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END        --IF @LT30 = @Line  BEGIN SET @DL30 = 1 END        --IF @LT31 = @Line  BEGIN SET @DL31 = 1 END        --IF @LT32 = @Line  BEGIN SET @DL32 = 1 END        --IF @LT33 = @Line  BEGIN SET @DL33 = 1 END        IF @LT34 = 453  BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END        --IF @LT35 = @Line  BEGIN SET @DL35 = 1 END        --IF @LT36 = @Line  BEGIN SET @DL36 = 1 ENDGO

View 1 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

ForEach Loop Or For Loop??

Feb 23, 2006

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View 1 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

How Do You LOOP In T-SQL?

Aug 20, 2007

I used to loop through recordsets with ease in old classic .asp pages.
Please Help me understand how Professionals now loop through and update tables using JUST SQL Query Analyzer using my pseudo-code provided below.
I would love to learn how to do it to better develop my skills.
 SELECT * FROM zz_2007_Booth_Visitors
WHERE COALESCE ([Product Interest - Other Actuator],
[Product Interest - Chillers],
[Product Interest - Other Chiller],
[Product Interest - Electronic Products],
[Product Interest - Other network interfaces],
[Product Interest - Fittings],
[Product Interest - High Vacuum],
[Product Interest - Other high vacuum actuators],
[Product Interest - Pick& Place and Transfer],
[Product Interest - Teflon Products],
[Product Interest - Training],
[Product Interest - Valves& Manifolds],
[Product Interest - Actuators]) Is Not Null
Order BY [Contact Name]

IF [Product Interest - Actuators] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Phuematic = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Actuator] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Act_Electric = 1 Where [Contact Name] = [Contact Name]

IF [Product Interest - Other Chillers] IS NOT NULL THEN
UPDATE Booth_Visitors_Data Set Chiller = 1 Where [Contact Name] = [Contact Name]
 
 

View 24 Replies View Related

For Loop

Jan 12, 2008

Dear All.
 Have a nice day.
I have db table, I need to update all fields of table.
Please can you write code," for loop " how can update all fields of my table by loop.
 Thanks.
Zahyea.

View 3 Replies View Related

While Loop

Mar 3, 2008

Hello everyone,I've got this While loop here which is giving me a problem:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN            DECLARE @ProdId int, @ProdSize varchar, @ProdQuan int            SELECT @ProdId = ProductId, @ProdSize = ProductSize, @ProdQuan = Quantity FROM _ShoppingCart WHERE CartId = @CartId                                   If @ProdSize = 'XL'            BEGIN                UPDATE                    _ProductBatches                SET                    XL = '0'                WHERE                    ProductId = @ProdId            END            DELETE FROM _ShoppingCart WHERE ProductId = @ProdId AND CartId = @CartIdEND The problem is that the IF statement isn't being executed. And I know for a fact that 'XL' is ProductSize in my _ShoppingCart database. Whats even stranger is that my delete statement is being executed. So @ProdId is Being set correctly, but when it gets to the IF @ProdSize = 'XL' it doesn't get executed for some reason. If @ProdId is being recognized correctly in my DELETE statement, why is my @ProdSize not being reconized correctly in my IF statement. I checked my _ShoppingCart database, and my ProductSize is definitely 'XL'. Can any one see what could be going on here.  Thank you, Alec 

View 7 Replies View Related

More Of This While Loop

Mar 4, 2008

Hello everyone...... I'm trying to do the following but am having issues:WHILE (SELECT ProductId FROM _ShoppingCart WHERE CartId = @CartId) IS NOT NULLBEGIN        execute code with item......... erase itemEND      In the while loop I want to execute code from each item in my _ShoppingCart and then erase them until there are no more items. However the above code gives me the error: "Subquery returned more than 1 value. This is not permitted........" It works fine when there is only one item. Does any one know what format to use when dealing with more that one entry?  Thank you, Alec 

View 2 Replies View Related

For Next Loop

Sep 21, 2000

hi,
I am trying to find a way of using a loop that won't be an endless loop because I have to insert parts of a string until the string reaches the end. I am unable to make the loop get to a point where the statement is false.

Is there anyway I can find out the length of the string so that I can tell the statement to loop while the statement is true?

Help!!!!!!!!!!!!1

View 1 Replies View Related

For Loop

Jan 26, 2004

HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;


The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.

can I write like this?


for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@AcctNum);
set @Cntr=@Cntr+1
}


I duno its just my guess, can any one help me out. @Cntr and @Acctnum are declared
in the beginnning.



tks

View 14 Replies View Related

Loop Help

Apr 11, 2008

I get the following results on a view.

Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 2 | A1102
99-999 | 4 | AB839

What I would like is the following.

Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 1 | A1102
06-324 | 1 | A1102
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839

View 2 Replies View Related

Loop Won't End.

Apr 18, 2008

I have a loop is running with no end point. What I'm trying to do is get the Grand total of each row where BudgetNodeID = 120. Your help is much appreciated.

AV

Set NoCount on
Declare @Amt as bigint
Declare @Cont as bigint
Declare @Mark as Bigint
Declare @Total as bigint
Declare @BudgetNodeID as Bigint
Declare @GTotal as bigint
Set @BudgetNodeID ='120'
Set @Amt = 0
set @Cont = 0
set @Mark = 0
set @GTotal = 0

While exists (Select * from xBudgetNodeCosts where BudgetNodeID =@BudgetNodeID) Begin
select @Amt = IsNull(xBudgetNodeCosts.Qty,0) * IsNull(xBudgetNodeCosts.CostRate,0)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')

select @Cont = @Amt * (xBudgetNodeCosts.Contingency/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID ) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
select @Mark = @Cont * (xBudgetNodeCosts.Markup/100)
FROM xBudgetNode INNER JOIN
xBudget ON xBudgetNode.BudgetID = xBudget.BudgetID INNER JOIN
xBudgetNodeCosts ON xBudgetNode.BudgetNodeID = xBudgetNodeCosts.BudgetNodeID left JOIN
xProposalChanges pc on xbudgetnodecosts.ProposalChangeID = pc.ProposalChangeID
WHERE (xBudgetNodeCosts.BudgetNodeID = @BudgetNodeID) AND (xBudget.IsActive = '1') AND (xbudgetnodecosts.ProposalChangeID IS NULL OR
pc.Status='Approved')
-- compute the sell

select @Total = @Amt + @Cont + @Mark

-- add to grand total
Select @GTotal = Sum(@Total+ @GTotal)

select @GTOtal
end

View 5 Replies View Related

Loop

Jun 17, 2008

Hi

I want to loop this command with a pause. It must run every 30 min to refresh my query. Can anyone assist me.



select * from BULKPICKXLOC



Thanks

View 4 Replies View Related

For To Next Or Do While Loop In SP

Apr 11, 2006

Exist a funtion that I can use in a SP that do something like the for to next or Do while Loop do?

View 5 Replies View Related

While Loop

Jan 30, 2007

I need to keep the first 4 values above 80 or the first 2 values above 90. If there are not enough, I need to keep as many values as possible. Should this be done with a while loop, if so, how would it be done.

View 19 Replies View Related

Loop

Mar 13, 2007

hello,
i have this SP to tally up my inventory tables.. im finding a way to loop through my table tblitemdetail to get necessary parameter to be insert into my other SP (SP_StkAdj_tbl_alignmt) that should accept this params (from the itemdetail) :-
@ItemID ='',
@ClientID='',
@CustomLotNo ='',
@UDF1=NULL,
@UDF2=NULL,
@UDF3 =NULL,
@UDF4 =NULL,
@UDF5=NULL,
@UDF6 =NULL,
@UDF7 =NULL,
@UDF8 =NULL,
@UDF9 =NULL,
@UDF10 =NULL,
@StockID ='0950-4388',
@RecvOwn ='OWN',
@ConsignorID ='JAB1MY'
EG:i will GROUP BY my itemdetail so it will give me the x records of data with :-

SELECT ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3, ownerstatus
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership, ConsignorID, RecvUDF1, RecvUDF2, RecvUDF3,ownerstatus
ORDER BY ItemID

with the result then, i need to insert the param into the SP:SP_StkAdj_tbl_alignmt so that it perform the calculation.

so i guess this will need some looping from the result set i get from the group by and some Sp calling from main Sp

~~~Focus on problem, not solution~~~

View 20 Replies View Related

While Loop

Apr 18, 2007

hi all, ive no idea what's wrong with my while loop nested in IF .. it only work correctly when i remove the while from IF :-


IF @Picktype='FI'
BEGIN
-- Insert data into @Stage to play around
DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))

WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
INSERT@Stage (ItemStorageID, Qty, RecvDate, BB)
SELECT TOP 1t1.ItemStorageID,
t1.Qty,
t1.RecvDate,
t1.BB
FROM#DataList AS t1
LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
WHEREs.ItemStorageID IS NULL
ORDER BYt1.RecvDate,
t1.Qty DESC

IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
SELECTrecID,
ItemStorageID,
Qty,
RecvDate,
BB
FROM@Stage
ELSE
select * from #DataList
END
correct result after i remove the while from inside IF

DECLARE@Stage TABLE (RecID INT IDENTITY(1, 1), ItemStorageID VARCHAR(12), Qty MONEY, RecvDate DATETIME,BB char(1))


WHILE (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) < @WantedValue AND @@ROWCOUNT > 0
INSERT@Stage (ItemStorageID, Qty, RecvDate, BB)
SELECT TOP 1t1.ItemStorageID,
t1.Qty,
t1.RecvDate,
t1.BB
FROM#DataList AS t1
LEFT JOIN@Stage AS s ON s.ItemStorageID = t1.ItemStorageID
WHEREs.ItemStorageID IS NULL
ORDER BYt1.RecvDate,
t1.Qty DESC

IF @pickType='FI'
BEGIn
IF (SELECT COALESCE(SUM(Qty), 0) FROM @Stage) >= @WantedValue
SELECTrecID,
ItemStorageID,
Qty,
RecvDate,
BB
FROM@Stage
end


~~~Focus on problem, not solution~~~

View 9 Replies View Related

Should I Use A Loop?

Sep 18, 2007

I have the following stored procedure which enters items bought by the usr in the database, inserting the user's ID, the item and the price. now every user has a unique id and every user can only buy three items; thus only three inputs must be inserted in the table, how can i do that? This is the current SP i have...

ALTER PROCEDURE [dbo].[spA_ALW_InsertIntoMLAGoods]

@MLAFormIDFINT,
@DescriptionNVARCHAR(50),
@AmountINT

AS

BEGIN
BEGIN TRY
--UPDATE MLAGoods
--SET
--MLAFormIDF=@MLAFormIDF,
--Description=@Description,
--Amount=@Amount
--WHERE MLAFormIDF = @MLAFormIDF

INSERT INTO MLAGoods
(
MLAFormIDF,
Description,
Amount
)
VALUES
(
@MLAFormIDF,
@Description,
@Amount
)
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [ERRORMSG], ERROR_SEVERITY() AS [ERRORSEV]
RETURN @@ERROR
END CATCH

END

View 4 Replies View Related

Sql Loop

Sep 25, 2007

Hi i have a sql loop query which i have working in asp fine, i have altered it to try and get it working as a stored procedure.

but i am not sure what the syntax is.

can someone help please.

many thanks



DECLARE
CURSOR GetWebOrder_cur IS
SELECT O_R_ID, O_Name, O_Add_1, O_DB_Code, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_Instore, O_STC_Code, O_ID
FROM [newserver].dbo.X_TBL_ORDER
WHERE NewOrder = 0

BEGIN
FOR GetWebOrder_rec IN GetWebOrder_cur LOOP

-- SET ALL FIELDS
set R_ID34 = GetWebOrder_cur("O_R_ID")
set R_Name = GetWebOrder_cur("O_Name")
set R_Contact = GetWebOrder_cur("O_Add_1")
set R_Code = GetWebOrder_cur("O_DB_Code")
set R_Add_1 = GetWebOrder_cur("O_Add_2")
set R_Add_2 = GetWebOrder_cur("O_Add_3")
set R_Add_3 = GetWebOrder_cur("O_Add_4")
set R_Add_4 = GetWebOrder_cur("O_Add_5")
set R_Add_5 = GetWebOrder_cur("O_Add_6")
set R_Add_6 = GetWebOrder_cur("O_Add_6")
set R_PostCode = GetWebOrder_cur("O_PostCode")
set R_CostCode = GetWebOrder_cur("O_CCode")
set R_Delivery = GetWebOrder_cur("O_Service")
set R_Instore = GetWebOrder_cur("O_Instore")
set R_STCODES = GetWebOrder_cur("O_STC_Code")
set WebOrderID = GetWebOrder_cur("O_ID")

-- GET MAX ID
SELECT Max(O_ID) AS MAXOID FROM dbo.X_TBL_ORDER

-- INSERT ORDER INTO F4 ORDERS
INSERT INTO dbo.X_TBL_ORDER
(O_Date, O_R_ID, O_Name, O_DB_Code, O_Add_1, O_Add_2, O_Add_3, O_Add_4, O_Add_5, O_Add_6, O_PostCode, O_CCode, O_Service, O_O_By, O_Instore, O_STC_Code)
VALUES
(getdate(), R_ID34, R_Name, R_Code, R_Contact, R_Add_1, R_Add_2, R_Add_3, R_Add_4, R_Add_5, R_PostCode, R_CostCode, R_Delivery, R_Contact, R_Instore, R_STCODES)

-- UPDATE VIRTUAL SERVER SET NewOrder = 1
UPDATE [newserver].dbo.X_TBL_ORDER
SET NewOrder = 1
WHERE O_ID = WebOrderID

-- SET CURSOR FOR ORDERLINES
CURSOR orderlines_cur IS
SELECT *
FROM [newserver].dbo.X_TBL_ORDER_LINE
WHERE OL_O_ID = @WebOrderID
-- OPEN LOOP THROUGH ORDERLINES
FOR orderlines_rec in orderlines_cur LOOP

-- SET ORDERLINE FIELDS
set B_St_Code = orderlines_cur("OL_St_Code")
set B_Description = orderlines_cur("OL_Desc")
set B_Qty = orderlines_cur("OL_Qty")
set B_dbcode = orderlines_cur("OL_DB_Code")

-- INSERT INTO F4 ORDERLINES
INSERT INTO dbo.X_TBL_ORDER_LINE
(OL_O_ID, OL_St_Code, OL_Desc, OL_Qty, OL_Allocated, OL_Despatch, OL_DB_Code)
VALUES
(B_preorderID, B_St_Code, B_Description, B_Qty, B_Qty, B_Qty, B_dbcode)
-- CLOSE LOOP THROUGH ORDERLINES
END LOOP;

END LOOP;
END;

View 9 Replies View Related

Loop

Nov 21, 2007

Hi,

I have this scenario: I have a table one database in sql called facttable_Temp with columns CustomerName, ItemKey.The fields are varchar.


I have another table called Accounts in a different database. Accounts contains fields such as CustomerName,Account. The fields are varchar.

What I need to do is to check if the values for CustomerName from table FactTable_Temp exists in the field CustomerName in the table Accounts.
If it exists then I need to insert the entire row for that CustomerName including ItemKey into a 3rd table called FactTable.

What is the best way of accomplishing this in SSIS?

Thanks


Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 1 Replies View Related

SQL Loop

Feb 14, 2008

hi i need to do a loop inside a stored procedure, just not sure how to go about it!

this is what i need



select count(TI_ID) AS REC_COUNT
FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

CASE WHEN REC_COUNT > 1 THEN

===LOOP THROUGH AND DELETE 1000 AT A TIME===

DELETE top 1000 FROM TRANSIT...T_TBL_TRANSIT
WHERE DATEDIFF(m, TI_Date, getdate())>12

END CASE

View 7 Replies View Related

How To Add A Do Loop In Sql? Thanks A Lot!

Jul 23, 2005

What I am trying to do is to get balances at each month-end from Jan toDec 2004. Now I am doing it by manually changing the date for eachmonth, but I want to do all the months at one time. Is there a way toadd something like a do loop to achieve that goal? Please see my querybelow. Thanks so much!declare @month_date_b smalldatetime--B month beginning datedeclare @month_date_e smalldatetime--E month ending dateselect @month_date_b='9/1/2004'select @month_date_e='9/30/2004'select a.person_id, a.fn_accno, a.fn_bal, b.mm_openfrom fn_mm_fnbal as ajoin fn_mm_list as bon a.person_id=b.person_idand b.mm_open < @month_date_ewhere a.bal_date between @month_date_b and @month_date_egroup by a.person_id, a.fn_accno, a.fn_bal, b.mm_openorder by a.fn_accno, a.fn_bal

View 2 Replies View Related

For Loop

Aug 17, 2005

helloDoes exist in SQL language "for" loop ? If yes, what syntax does it has ?best wishesAdam

View 9 Replies View Related

For Loop

May 9, 2007

Hello,

Please, could someone explain me how "For Loop" funtions?

because I need to load data depending on a variable?

Can I use For loop to do that?

thanks

View 1 Replies View Related

For Loop

Jul 24, 2006

I have a table which has an identity column. I want to traverse through the table one row at a time using FOR Loop. Can someone help me with the syntax.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved