Dynamic Indexdefragmentation Loop

Aug 23, 2005

I have a couple of servers each with around 900 databasesa and need to do indexdeframentation on them.
The loop to database and loop for tables are fine. The problem is the loop for indexes and always return -1 for @@Fetch_status after open the cur_indfetch cursor as follows

select @sqlStringField = ' DECLARE cur_indfetch CURSOR FOR ' + ' SELECT indid FROM ' + @databaseName + '.dbo.sysindexes ' + ' WHERE id = OBJECT_ID (' + '''' + @TableName + ''''+ ') and keycnt > 0'
print @sqlStringField
exec master..sp_executesql @sqlStringField

OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
print ' before while @@FETCH_STATUS: ' + cast(@@FETCH_STATUS as varchar(5)) + ' @indid:' + cast(isnull(@indid, 0) as varchar(5))
WHILE @@FETCH_STATUS = 0
BEGIN
print ' Cursor 3--cur_indfetch--' + @databaseName + '--@tableName:' + @tableName + '--' + cast(@indid as varchar(7))
IF @indid <> 255
begin
DBCC INDEXDEFRAG (@databaseName, @TableName, @indid)
end
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch

any idea?
thanks
David

View 3 Replies


ADVERTISEMENT

Dynamic Query In While Loop

Nov 18, 2011

@strSql is a dynamic query in while loop which can return single record, single row of records, multiple rows of records.

So only if it returns single record then I have to store it otherwise convert to xml before storing.

1) If it returns 1 record(1row and 1 column) then save as it is.
2) if it returns a row with more than 1 columns then convert to xml before saving.
2) if it returns data rows then convert to xml before saving.

View 2 Replies View Related

Dynamic Sql To Loop Over Fiscal Years

Jan 6, 2004

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECTcount(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHEREstart_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECTcount(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHEREstart_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...


expected output....

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
.... ....

View 4 Replies View Related

Please Help! Dynamic DB Connection In Foreach Loop

Apr 4, 2006

Hello-

I'm trying to read a table that has database connection information for other DBs and use this within an "Execute SL Task" task. I have seen a number of posts that talk about this possibility, but I have not been able to get it to work yet.

When I've tried to set the connection to a variable (@[User::DB_ConnectionStr]) in the Expressions area of the SQL Task, the Connection type defaults to OLE DB and I can't seem to force it back to ADO or ADO.net.

I've tried doing this with the variable being set to both a connection object and a String with the connection string, but neither seems to work.

Any suggestions? Should this be a string value of the connection string? Am I missing something when trying to set the connection type?



Thanks much!

Tristan

View 3 Replies View Related

Building Dynamic Tsql Statements In A Loop

Jul 20, 2005

Hi;I would like to read a list of tables from a temp table and then do asql statement on each table name retrieved in a loop, ie:-- snip cursor loop where cursor contains a list of tablesdeclare @rec_count intset @rec_count = 0exec('select @rec_count = count(myfield) from ' + @retrievedTableName)This does not work. SQLSERVER tells me @rec_count is not declared.How can I get the @rec_count populated....or can I?Thanks in advanceSteve

View 3 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

Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns

Aug 25, 2007

Hi Craig/Kamal,

I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.

Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.

We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.

I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.

Your help in this respect is highly appreciated!

Thanks,


Hi Anthony, I am glad the Web cast was helpful.

Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.

I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1

Thanks



Craig Guyer
SQL Server Reporting Services

View 12 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?

May 2, 2007

If anyone could confirm...

SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.

E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.

First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.

However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.

Question: I thought the filters would remain dynamic and be applied on each sync?

I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!

Question: I wonder why if parent records are supplied, why not child records?

If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...

Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?

View 4 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

T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot

Mar 9, 2015

I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?

Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),

[code]....

View 1 Replies View Related

Mixing Dynamic SQL With Non-Dynamic In Stored Proc

Mar 24, 2007

I have a Stored Procedure for processing a Bill of Material.

One column on the Assembly Table is a Function Name that contains some busniess rules.

OK, now I'm doing a Proof of Concept and I'm stumped.

Huuuuh!

I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.

Note: The function just returns a bit.

So; here's what I had in mind ...

if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output




CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS

declare @SQL varchar(8000)

set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'

exec (@SQL)
GO



Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.


So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?


My many thanks to anyone who can solve this riddle for me.
Thank You!


Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.

------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END

------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.

View 10 Replies View Related

Dynamic Cursor/ Dynamic SQL Statement

Oct 24, 2004

I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

Using the examples given in Books Online returns compilation errors. See below.

Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

James



-- SQL ---------------

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;



--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.

View 2 Replies View Related

Dynamic Source And Dynamic Destination

Apr 15, 2008

I have a requirment which i have partly accomplished , but could not get through completely

i have a file which comes in a standard format ending with date and seq number ,

suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .

then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder


what i have taken apprach is

script task select source file --------------------> data flow task------------------------------------------> script task to destination file

dataflow task -------------------------> does count and copy in delimited format



what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .

but cannot work the dynamic pick of a source file.


please advise with your comments or solution you have

View 14 Replies View Related

SQL 2012 :: Creating Dynamic SSIS File Format - Dynamic CSV File As Output

Mar 2, 2014

I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.

sample file name:

Unique identifier + query output + systemdate();

The expression is looking like this.

@[User::FilePath] + @[User::FileName] + ".CSV"

-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .

When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.

View 3 Replies View Related

Dynamic Columns For Dynamic SQL

Mar 9, 2007

I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.

Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.

I have come to understand that a dynamic anything is a moving target!



Please advise.

View 3 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







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