Using For Loop In Sql Server SP
May 13, 2008
Is there a way for using for loop inside an SP in SQL server 2005?
If so, what is the syntax??
ADVERTISEMENT
SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?
May 28, 2010difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies View RelatedLoop Though Table Using RowID, Not Cursor (was Loop)
Feb 22, 2006I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
Foreach Loop Doesn't Loop
Mar 3, 2006I 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
Fishing For A Clue. To Loop Or Not To Loop
Jul 8, 2006I 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.
SQL Server Loop
Apr 19, 1999On a couple of occassions I have had MS SQL Server 6.5 with SP3 go into a loop writing the following message to the errorlog.
Error 17824 Severity 10, State 0 Unable to write on ListenOn Connection '.pipesqlquery', loginname 'sa'
By the time I have caught it, it has written about 1/2 gig worth of messages to the errorlog.
Both times it has happened I had been in SQL Enterprise Manager and the application stops responding and I am forced to terminate it.
I believe this is the point as which the error starts..
Any ideas?
Thanks,
Phil
How To Loop Through In Sql Server 2000
Nov 27, 2007I have a scenario where I have to keep track of users last three passwords,
if the new password entered by user matches with the last three passwords in the PasswordHistory Table then I will display message to user that "you can not repeat any of your last three passwords.."
Basically I can do : select top 3 passwords from MyTable order by createdDate desc
But how to do comparision ?
I was googling for if anything like array exist in sql server 2000?
Please suggest, any pointer........
Thanks
Loop Through SQL Server Instance ?
May 11, 2006Hi,
Is it possible to loop through dynamically through all sql instances in a particular environment ?
Thanking you in advance.
Regards,
Ranjit
Moving A Logic Loop To SQL Server
Jan 14, 2006I am using C# and ASP.NET 2.0, with SQL Server 2000. In my database I have a table that is similar to the following:
WebpageId WebpageAddress Handler 1 /company/about ~/about.aspx 2 /blog ~/blog.aspx
As you can guess, one of my queries will be a SELECT command where WebpageAddress = @address.
The hiccup I have is with a friendly URL such as the following:
/blog/2005/10/6
The friendly URLs have no extension, so I cannot immediately "pick out" the extension. (If the address were /blog.aspx/2005/10/6, then things would be simpler.)
What I am doing at present is using a loop in C# where I first perform a query with that full address. If no match is found, I trim the address back to the last slash (/blog/2005/10) and perform another query. If no match is found, I trim the address (/blog/2005) and perform another query. Again, no match is found, so I trim the address again (/blog) and perform yet another query. This time, a match is found, in which case the URL rewriting looks vaguely like this:
~/blog.aspx?parameters=2005/10/6
While this works fine, these friendly URLs require hitting the database up to four or five times. My question is, can this looping logic be moved to SQL Server?
My SQL knowledge is extremely basic, so I am just looking for someone to point me in the right direction. If it is not possible, I'd love to know now rather than wasting hours trying. If it is possible, I've love to know the keyword or technique involved, so that I can Google for the full answer.
SQL Server 2014 :: Possible To Replace While Loop By CTE?
Aug 11, 2015Is it possible to replace while loop by CTE?
I will fetch records from table 1 based on tretmentid.
get the count of records
run the while loop
inside loop I will compare the column values with the same table(table 1) for different tretmentid.
if any one row matches will come out from loop and send status .
can it be replaced by CTE ?
Trying To Do REALLY Simple Procedure And Loop In SQL Server
Aug 16, 2006Dear experts,Again, sorry to bother you again with such a seemingly dumb question,but I'm having some really mysterious results here.ie.Create procedure the_testAsBeginSelect CustomerID
Quote:
Foreach Loop Though All Databases On A Server
Oct 8, 2007Hi
I am trying to write an SSIS package that will look though all the databases on a server (in this case 2k server) and get the list of tables in each database
I have created a Execute SQL Task with select name from master..sysdatabases where name <> 'Tempdb'
This is connected to a Foreach Loop that uses Foreach ADO Enumerator on the first table from the var generated in step 1 (List of databases)
Within the Foreach loop there is a dataflow that has a a sql command
select @@servername SQLInstanceName
,db_name() DatabaseName
, name TableName
--, getdate() RecordDate
from sysobjects where type = 'u'
This should return all the tables in a database however the databases is not changing it just lists all the tables in the master database times the number of databases on the server
Can anyone point me in the correct direction for this one
Thanks
Kevin
Checking Server Connection Before A For Each Loop
Apr 9, 2008I have a list of servers on a table, I take this list and pass to a for each loop container and that will grab some system information from each server that we give for our internal auditors.
The issue that I'm currently having with this package is that some times, 1 or 2 servers from the list are down and/or extremelly busy and it times out, either case causes the whole package to fail.
What I'm trying to do is, test the connection to the server prior passing the server name to the For Each Loop Container and log that information somewhere (Maybe a boolean field in a table), so then I check after the package finish and validate that the server was actually up or down.
Any ideas or suggestions?
Thanks,
Igor Santos
SQL Server 2012 :: Loop Through Each Instance On A Server
Apr 15, 2015I'm writing a query to loop through each instance on a server so I can get each instances relevant information. I can't seem to get each instances sql service and sql agent service, either one or the other.
DECLARE @InstanceNames varchar(50), @SQL varchar(2000)
DECLARE @GetInstances TABLE (Value varchar(100), InstanceNames varchar(50), Data varchar(100), ID int IDENTITY(1,1))
DECLARE @Serverinfo TABLE (ServerName varchar(50), InstanceName varchar(50), ProductVersion varchar(20), ProductLevel char(3)
[Code] ....
SQL Server 2012 :: While Loop In Place Of Cursor
Feb 16, 2014I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.
Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.
Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.
For example Table1
Tableid, Processigndate Amount remainingCollectonCount Frequency
1 2014-02-15 48 8 12
the future cash flow table the prcessing date column will be shown in the following way
Processigndate
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15
2014-09-15
2014-10-15
I do not to want to use cursor....
SQL Server 2008 :: Use Top N For Select / Delete In A While Loop?
Jul 27, 2015Can I safely use top n select/delete in a while loop? For example:
declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
SQL Server 2008 :: Loop To Insert 0s Into Table
Oct 22, 2015I have a problem where I want to create a loop in my script to insert 0's into my table.
I have a temp table with a list of company codes.
SELECT CODE FROM ##SENData GROUP BY CODE
This produces the following;
CODE
00C
00D
00K
00M
00Q
00T
00V
00X (there are 110 of these codes)
I want to insert data into a different table in the following format.
+------+------------+-------+---------+
| CODE | Month | Value | Measure |
+------+------------+-------+---------+
| 00C | 01/09/2015 | 0 | HAR-01 |
| 00D | 01/09/2015 | 0 | HAR-01 |
| 00K | 01/09/2015 | 0 | HAR-01 |
| 00M | 01/09/2015 | 0 | HAR-01 |
| 00Q | 01/09/2015 | 0 | HAR-01 |
| 00T | 01/09/2015 | 0 | HAR-01 |
| 00V | 01/09/2015 | 0 | HAR-01 |
| 00X | 01/09/2015 | 0 | HAR-01 |
+------+------------+-------+---------+
The month will be set from a declared variable and the others will just be hard coded.
SQL Server 2008 :: SSIS - For Each Loop Through Multiple Servers
Jan 30, 2015I have an SSIS job that dynamically loops through each server, grabbing data for typical DBA reporting, like diskspace, and errorlogs. If the server is down for whatever reason the SSIS package fails. Is there any way I can prevent the SSIS package from failing if one of the servers is down?
View 1 Replies View RelatedSQL Server 2008 :: Iterate Query Using A Loop As Many As 5 Times Max?
Mar 20, 2015If exists (select fieldID from #tmploginfo where status <> 0
group by fieldID
having count(*) > 0)
begin
backup log rdb to disk = N'C:
db1.trn'
End
I want to iterate this query using a loop as many as 5 times max.
SQL Server 2012 :: Loop Through Each Row Of Table Valued Parameter
Jun 16, 2015I need looping through each row of Table valued parameter. I have user defined type
CREATE TYPE [dbo].[PCS_SPEC_ATTR_VALUE] AS TABLE(
[ATTR_NAME] [varchar](256) NULL,
[ATTR_VAL] [varchar](4000) NULL
)
I am using this type in my procedure like
@P_TYPE VARCHAR(4000),
@P_SCOPE VARCHAR(4000),
@P_PART_CLS_ATTR PCS_SPEC_ATTR_VALUE readonly
I am using P_PART_CLS_ATTR as input where I can insert the data as attr_name and attr_value. I want to access each row of inserted values like row by row. I need take attribute name and value process them and return for inserting.. How to access the values in row by row?
SQL Server 2008 :: How To Increase Performance Of Insertion SP In While Loop
Aug 13, 2015WHILE (@MyLoop3 > 0)
BEGIN
SELECT Top 1 @UploadId = UploadId,@FirstName = (CASE WHEN FirstName = '' THEN @Update ELSE FirstName END),
@LastName = (CASE WHEN LastName = '' THEN @Update ELSE LastName END),
@Claim = (CASE WHEN Claim = '' THEN @Update ELSE Claim END),
@Insurer = (CASE WHEN Insurer = '' THEN @Update ELSE Insurer END),
@InsurerBranch = (CASE WHEN InsurerBranch = '' THEN @Update ELSE InsurerBranch END),
[Code] .....
How To Implement The For Loop Logic In Sql Server Reporting Services
Dec 17, 2007Hi All,
This is the code for calculating a formula field in Crystal Reports.
I want to implement the same in Sql Server Reporting Services..
But it doesn't have the feature of For Loop....
The strings started with @ are formula fields....
Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR;
for YR := 1 to {@CalcFiscalAge} step 1 do
(
IF YR = {@CalcLifeCode} +1 then
locFactor1 := 1;
exit for;
IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN
HALF_YEAR := 2
ELSE
HALF_YEAR := 1 ;
LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4);
MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4);
IF MACR >= LINEAR then
DEP := MACR
ELSE
DEP := LINEAR;
locFactor1 := locFactor1 + DEP ;
REM := 1 - locFactor1;
locFactor := locFactor1;
);
Thanks in advance
Regards,
ForEach Loop Or For Loop??
Feb 23, 2006I 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... ..............................
SQL Server 2012 :: How To Loop Unmatched Rows Data From Two Tables
Jan 12, 2015We are having folder table and team table as like below structure.
Folderlist (F)Table: (
==============
id ,folder_name, parent_id
1, c, 101
2,b,202
3,c,203
Teamlist table (T)
============
team_id, Team_name, Parent_folderid
101 , mobile,101
202 ,Tab,200
200, Phone,200
203,apple,205
205,nokia,208
208,samsung,208
If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101)
then output should come as 'Mobile/c' (this is for f.parent_id=101)
If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id
then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id
Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache
like phone/tab/b (this is for F.parent_id=202)
SQL Server 2012 :: Inserting Dummy Records Using Loop Statement
Jul 17, 2015I have the following attributes in this Table A.
1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)
I am trying to create 1000000 dummy records in this this table A.How do i go about do it? I would like my data to be something like this
LOCATION_ID
1
Serial Number
SN-01
KeyID
E1210
Reference_Address
83
SourceTime
2015-05-21 00:00:00 000
SourceValue
6200
Replication Loop Back Detection Problems In SQL Server 2000
May 10, 2007Hi,
I am monitoring my replication process and I have a problem with the loop detection, I can see origined transacctions in the server, executed in the same for the replication.
The subscriptions configuration is sp_helpsubscription
exec sp_addsubscription @publication = N'PUBLICATION',
@article = N'all',
@subscriber = N'192.168.0.23',
@destination_db = N'PRUEBABD',
@sync_type = N'none',
@update_mode = N'read only',
@offloadagent = 0,
@dts_package_location = N'distributor',
@loopback_detection = 'true'
GO
I need for help.
SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?
Jun 27, 2014I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
SQL Server 2008 :: Compare Data / Loop Through Dates And Insert Into Table
Sep 21, 2015I have three tables:
"PaymentsLog"
"DatePeriod"
"PaidOrders"
As per below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
Executing SSIS Package Using Jobs In SQL Server Agent Infinite Loop
Jan 4, 2008I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.
The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.
SQL Server 2012 :: Write A Loop On Result Of First Query Inside A Stored Procedure
Jan 23, 2015I have to write a Stired Procedure with the following functionality.
Write a simple select query say (Select * from tableA) result is
ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
How Do You LOOP In T-SQL?
Aug 20, 2007I 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]
For Loop
Jan 12, 2008Dear 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.
While Loop
Mar 3, 2008Hello 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