I am curious, if it's better to iterate through a set of records using cursor or using a WHILE (SELECT ..) ? I have read that cursors can be expensive, so may be WHILE (SELECT..) is a better approach.But I am not sure ?
EXAMPLE OF WHILE (SELECT..) is as below
DECLARE @warehouseId INT
DECLARE @warehouseIds TABLE ( WarehouseId INT )
[code]...
I'm using cursor to select a list of records in sql server 2005
There are 80+ columns in the result, the cursor parameters named as @pn1,@pn2,@pn3... @pn12. @pid1,@pid2,@pid3.... @pid12
[Code]....
The above code rotates the parameter ('@pn' + loop id) to get the cursor parameter value.
i have two tables (Users, Roles) and each user belongs to one role. Can I make a sql query that returns 10 newest users from each role in one query?
View 5 RepliesI have to retreive all records from a table except 1 record. For example: I give example from default database in SQL ie "master" In "master" DB we have "spt_values" table. Now if I want to retreive all records from spt_values table except 1-1 record of type = A and type = B. To retreive all records its a simple query "Select * from spt_values where type in ('A','B')" -- this will returns 16 records. Now I want to retreive all record except 1-1 record left in each type, so it will return 14 records in result.
View 6 RepliesI want to select the records of current week, Starting day should be from saturday till Friday.
I can fetch the records of current week:
[Code]....
but starting day of week is Sunday and ending is Saturday.
What I want is, Starting day should start from Saturday and ending day should be Friday.
The DoctorGen Table has primary key DocId and Doctors has Keys set on DocId and MemId. I want to get all records from DoctorGen where UserID = @UserId even if there are no matches in the Doctors Table. I'm not getting any records.
[Code]....
I have 2 tables ( having one to many relation )
SELECT CAT.CAT_TYPE, Agencies.Name
FROM CAT INNER JOIN
Agencies ON CAT.CAT_ID = Agencies.CAT_ID
CAT_A Val1
CAT_A Val2
CAT_A Val3
I need the select statement to list only the last records of the Table Agencies , see the red color at the top , if this can be only acheived using SP
I have a basic CMS which I'm adding to a site to display some downloads organised into groups. I have two tables, one for the groups and one for the downloads. I'm using a repeated to display the groups with another repeater inside to display the children. This works perfectly.
However some of my download groups may not have any downloads related to them and I'd like to handle this by filter the groups so that only those with a relate download record(s) are shown.
I'm trying to do this with the query which populates the top repeater based on some ideas I read but I must be going wrong with the syntax.
Here is what I'm using to try and only select downloads groups which have downloads linked to them by the download group ID.
[Code]....
Can anyone offer any thoughts on how I should construct the query to perform this?
I have two gridview. The first gridview with checkbox and if i check the checkbox in first grid that particular row will be binded to second grid. In second grid i have a textbox and dropdown. Have enabled pagination in both grids. When i check the checkbox in first grid i loop through all records in second grid to bind the previous values. Problem is since i have enabled pagination gridview row count only retuns current page row count. How to loop through all rows?.
View 5 Replieswhen i select a row in my gridview the cursor is going to top after selecting the row.
After selecting the row my page is paostbacking so it is going to top.so is there any command in grid to focus the cursor to recnetly selected row.
Running a select * from my table give me data like this:
Field1 Field2 Field3 Field4 Field5 Field6
NULL NULL NULL J 4GG4K C46D
DRIVE 0155 1 NULL W6665 NULL (C46D)
TEST 01444 5 NULL W4434 NULL (C46D)
NULL NULL NULL 1 WGG4K AAVE
HOME 01444 3 NULL 5HTYL NULL (AAVE)
NEW 09888 9 NULL 8HTTTE NULL (AAVE)
The problem is Field6 for the rows that are bolded. I need Field6 to repeat for each row until the next non null value is found in Field6, than I need that value to repeat again. I placed in parenthesis outside the NULL for Field6 what I need to show which is the value of Field6 above the records which Field6 is null.
I know this is confusing, but it's actually pretty simple to understand. I just need to get the first NON NULL value of Field6 and UPDATE the records below it where Field6 IS NULL to that value, then when it reached the next bolded row there is a new value for Field6, I then need to get that value and UPDATE the records below it which are null with the value.
How to loop through the table get only paticular records based on the particular value and store it in a datatable.
for example, i've a table with two columns, Store name and Store number. For a single store name it can have mulitple store number like
[store name ] [store number]
a1 10
b1 12
a1 15
a1 21
b1 11
b1 22
a1 25
etc....
How to loop through the table and get all the records/ rows of a paticular store name and store it in a datatable.
Table1 - T1Col1(varchar) T1Col2(int) T1Col3(varchar)
Table2- T2Col1(varchar), T2Col2(varchar), T2Col3(int)
Table3- T3col1(varchar),T3col2(varchar)
For every record in Table1 I need to loop through top 10 records in Table2 - check this condition if( T1Col2 element & T2Col3 element)/T2Col3 element = 1 and insert T1Col1,T2col1 in to Table3
using sql server 2005.
i need to query a table which normally will return multiple records for the id and I need to loop through probably using a cursor and pull values out from each record to make a string.
what is the syntax for this? i've seen cursors using a set number (EX: loop through 5 times) but I need to create a query so that I can loop through once for each row returned from my query.
Tag TableName FieldNameabc wc s_namelbl wc s_deslb2 wc s_prodin above fieldname(s_name,s_des,s_prod) column represent field in wc table.How can i bring this result. is this possible without using cursor. i was struggling past two days. i cant able to bring result for above.
View 17 RepliesI have a stored procedure that uses a cursor and also uses another stored procedure to return a column from the returned records.
[Code]....
See that part with 'exec dirinfo.dbo.d_searchempybyname @TicketEmail'? It works and returns the records but what I really need is to return a COLUMN from the record and store that column value in a variable which I can use - for example 'PRINT @VariableName'.
I need to have maybe 5 threads running which all use the same DataSet and need to loop through all the rows retrieved but each row must be processed by one thread only (e.g. I don't want row 1 to be processed by thread 1 and thread 3).
I can also use a DataReader if that helps, but I need to dow some locking logic I would have thought.
I have a Gridview that has Paging enabled, and I need to loop through all the rows of the data. Therefore, I assume I need to loop through the datasource instead of through the Gridview.
I change the datasource of my gridview at various times when running.
How can I get a DataView of the GridView's current DataSource, so that I can loop through it?
Something like:
Dim dv as DataView = DirectCast(GridView1.DataSource, DataView)
except that this comes back as Nothing, probably because a Datasource isn't a dataview.
I have a requirement where I need to declare a Sql Statement dynamically in a cusror. When I declare dynamic Sql Statement and execute the SP, it throws an error message. The Cursor is not recognizing the dynamic Sql command.
As per the requirement, the sql statement will change according to the input parameter and it can't be declared as a static sql as shown below. Can anyone tell me, how this can be achieved any other alternative to do?
I have created the stored procedure in SQL server 2005. i have used nested cusrsors in this SP. also many records are there. So, its performance is too low.
i forced to use cursor, because the this is calcualtion process. i have to fetch the records one by one, do the calculation and insert the result to the another relevent tables.
So it cause big performance issue.this is my sample scanario.
First i have to take the products from the customerforecast for the certain duration. (i used cursor to fetch the products)
then i need to get the list of machines can run for the each product. (i used cursor to fetch the machines)
then i have to fetch the Unit per hour value for the combination of machine and prduct.then i have to use formula and do the calculation and store the result to the 3 different tables.
Looping Through Checkbox List and adding selected items to MS SQL DatabaseI don't want to store it using comma deliminator.I will need to do a search on analyze which users have what music in common.The image above is from a tbl_lookup_music table I created. MusicID, MusicNow how should I build the logicIF we have a user that has a tbl_profile table. And listens to many different types of music. How should We store the data.------------------------------------------------------------------------------------------CREATE TABLE [dbo].[tbl_lookup_music]( [MusicID] [int] IDENTITY(1,1) NOT NULL, [Music] [nvarchar](64) NOT NULL, ---- Type of music. CONSTRAINT [PK_tbl_lookup_music] PRIMARY KEY CLUSTERED ( [MusicID] ASC )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY----------------------------------------------------------------------------------------------CREATE TABLE [dbo].[tbl_Profile_Music]( [ProfileMusicID] [int] IDENTITY(1,1) NOT NULL, [ProfileID] [int] NOT NULL, [MusicID] [int] NOT NULL,PRIMARY KEY CLUSTERED ( [ProfileMusicID] ASC)WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOHow should I setup this table.
View 4 RepliesI have a database table as follows:
[Code]....
This table receives data from my web application via a stored procedure, snippet pasted below:
[Code]....
In my quote.aspx page, I have a wizard control that collects numerous data points. In one of the wizard steps, I have 20 textbox controls for PartNumbers and 20 textbox controls for respective Quantity.
Question:How do I write a for..each loop that checks for values in my Part Number and Quantity fields and inserts them via my SQLDataSource?
how can i find the total null values in a table without using sql cursor .
i want to find the percentage of the data which is avaibale in the table and how much percentage of data is unavailable ( or null). to make a comparision chart.
i have 6000 rows and 200 columns in a table , if i use sql cursor , it is taking too much time to execute ( 6000 * 200 ) loops. is there any inbuilt-function in sql to do it .?
my requirement is that SSRS 2005 report should show all records with checkboxes(or can be by any other means of flagging ?) .
Now the records for which checkboxes are checked or flagged are only to be included in the exported excel sheet or pdf .
When can I use Stored Procedure? Is this useful for looping into multiple SELECT statements to return single value? How? Can anyone post some sample codes? I have text query and notice that my web application is slow and want to find ways to speed it up. Is there any possible ways aside from Stored Procedures?
View 4 Replies