.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

While loop in SQL Query

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :ASP.Net

I have create a function in sql RawMaterial with use of cursor and while loop.

Where I did mistake I didn't understand...

My query is

CREATE FUNCTION RawMaterial(@date varchar(100)) RETURNS INT
DECLARE @item_total int
DECLARE @qty int
DECLARE @item varchar(100)
DECLARE @tempitem varchar(100)
select item FROM master.recipe_item
open c
FETCH NEXT FROM c into @item
(select @item_total = SUM(Cast(qty AS int)) FROM master.recipe_ingredient WHERE recid IN (SELECT recid FROM master.recipe_recipe WHERE item = @item))
(select @qty = cast(qty as int) FROM executive.consumption_total WHERE item = @item and date = @date)
FETCH NEXT FROM c into @item
RETURN @qty - @item_total

close c
--PRINT  (@item_total - @qty)
--RETURN @avail 

but when I execute this function using

select dbo.rawmaterial('20-09-2010')

It will return only single row.




but i need all rows







how is it possible? Is function returns more than one row?

View Complete Post

More Related Resource Links

Do while loop query


Dear All,

Kindly check if my syntax is correct since I can't make it working.

@CutOffID INT,
@IDNumber INT,
@ShiftID INT
WHILE (@DateFrom <= @DateTo)
INSERT INTO ScheduleBreakdown (CutOffID, IDNumber, Date, ShiftID)
VALUES (@CutOffID, @IDNumber, @DateFrom, @ShiftID)
SET @ScheduleID = scope_identity()
SET @datefrom = DATEADD(DAY, 1, @datefrom)

Error: Cannot insert null to ScheduleID

I need to achieve like this for example

DateFrom '8/1/2010'

Date To '8/15/2010

ShiftID '1'

IDNumber '1'


ScheduleID     IDNumber          Date            Shift

1                    1                       8/1/2010       1

2                    1      &n

Succeeding loop of the same query takes longer than the previous one


Hi, I'm having trouble with an itermittent problem I'm facing with our site. See I'm running an sql query that accepts date range as variable. When I run the query in sql manager it runs in less than a second no matter what date range I put in. I have a function that loops a dozen times to get records for the past days getting a whole day's record for every loop. It would usually work fine but there are times that the succeeding loops slows down. I made it print out the duration for each loop and this is what I got in seconds.

start: 10/5/2010 12:00:00 AM|End: 10/5/2010 11:59:59 PM|0.5460009 <-- should be more or less the normal speed of each loop
start: 10/4/2010 12:00:00 AM|End: 10/4/2010 11:59:59 PM|14.3208252
start: 10/3/2010 12:00:00 AM|End: 10/3/2010 11:59:59 PM|14.5860256
start: 10/2/2010 12:00:00 AM|End: 10/2/2010 11:59:59 PM|14.1024248
start: 10/1/2010 12:00:00 AM|End: 10/1/2010 11:59:59 PM|4.6956082
start: 9/30/2010 12:00:00 AM|End: 9/30/2010 11:59:59 PM|5.4444096
start: 9/29/2010 12:00:00 AM|End: 9/29/2010 11:59:59 PM|8.3304146
start: 9/28/2010 12:00:00 AM|End: 9/28/2010 11:59:59 PM|11.0760195
start: 9/27/2010 12:00:00 AM|End: 9/27/2010 11:59:59 PM|20.1708354
start: 9/26/2010 12:00:00 AM|End: 9/26/2010 11:59:59 PM|20.8884367
start: 9/25/2010 12:00:00 AM|End: 9/25/2010 11:59:59 PM|15.444027

Microsoft's Collaborative Application Markup Language (CAML) Loop

discovered an interesting error recently while working with Microsoft's Collaborative Application Markup Language (CAML) that, surprisingly, had received no ink. Partly what surprises me about this is that the error may require you to rewrite large sections of your code if you haven't previously considered this SharePoint limitation. I'll start with some context, but first of all the error is:

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

How to send an email with information in query string


First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

for each loop



Need help in for each loop. i'm poor in for each loop. i wanna create a for each loop that can give me 2 value in each rows.

i create a for loop.. but is a mulitple rows. Anyone can help ?

here is my code.

Dim x As Integer, y As Double, x1 As Integer

        Dim d As Date, prevD As Date
        Dim d1 As TimeSpan

        temp_sec = ac.getTempTime(ddl_date.SelectedValue, ddl_id.SelectedValue)

        For I As Int32 = 0 To temp_sec.Tables(0).Rows.Count - 1
            If I Mod 1 = 0 Then
                ' To prevent list out of bounds
                If I + 1 < temp_sec.Tables(0).Rows.Count Then
                    d = temp_sec.Tables(0).Rows(I).Item(0)
                    prevD = temp_sec.Tables(0).Rows(I + 1).Item(0)
                    d1 = prevD - d
                    x = d1.TotalSeconds
                    x = x1 + x
                    y = temp_sec.Tables(0).Rows(I).Item(1)
                    list.Add(x, y)
                    x1 = x
                End If
            End If

Need Help in LINQ query for group By with chunks of record


I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&

Query an arraylist of array list using linq


I have an array list called Customer. I t holds a list of array list which has the account details.

proj.customer -> collection of arraylist of type customer

proj.customer.account -> collection of arraylist of type account

Account array list holds type account and it has a poperty account balance. How can I query all accounts which has account balance  >100

I tried the following one

var Accountlist= from Account accountdetails in proj.customer.account

where accountid.balance >100

select accountdetails.id

But it is always throwing an error saying cannot cast type customer to type account

Please help

Basic Instincts: Increase LINQ Query Performance


Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.

Jared Parsons

MSDN Magazine August 2008

Data Points: Standard Query Operators with LINQ


LINQ to Objects and LINQ to Entities have an arsenal of Standard Query Operators that operate on sequences to perform a wide variety of operations.

John Papa

MSDN Magazine March 2008

Basic Instincts: Query Comprehensions


Scott Wisniewski digs into LINQ and builds a custom query provider to create queries in real-world apps.

Scott Wisniewski

MSDN Magazine August 2007

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend