Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    3

    Question Unanswered: Stored procedure problems

    Hi everyone,

    This is my first go at T-SQL and stored procedures and I'm having some problems. I wrote a procedure that given a table containing a waveform takes a number of points from it and does averaging based on an also given sample size. The functionality or implementation of the procedure is not relevant, I can sort it out myself. It is T-SQL I'm struggling with.

    Looking at the procedure I've pasted below this lines, the first problem I've got is when I declare the cursor that supposedly only contains @intNoOfPoints from the original table:

    SET ROWCOUNT @intNoOfPoints
    DECLARE RealTimeData_Csr CURSOR FOR
    SELECT RealtimeData
    FROM Sawtooth
    SET ROWCOUNT 0

    It seems to me that setting ROWCOUNT doesn't have any effect, because the cursor ends up containing the whole "Sawtooth" table. Is there anything wrong with the syntax? I just want the cursor to contain a certain number of points from the table.

    The other problem I've got is when I'm trying to return multiple rows from the procedure by doing "SELECT * FROM AvgRealTimeData" at the end. When I execute the procedure it doesn't return any row; whereas if I comment out all the code related to manipulation of the data contained in the cursor, it works as expected, that is, it returns all rows from "AvgRealTimeData". Any ideas?

    Thanks for taking some time reading this, any help will be greatly appreciated.



    ALTER PROCEDURE dbo.GetDataSample
    @intNoOfPoints INT,
    @intSampleSize INT
    AS

    DECLARE @intCurrentValue INT, @intAvgValue INT

    -- Cursor declaration: The cursor contains the specified number of points from the Sawtooth
    -- table
    --
    SET ROWCOUNT @intNoOfPoints
    DECLARE RealTimeData_Csr CURSOR FOR
    SELECT RealtimeData
    FROM Sawtooth
    SET ROWCOUNT 0

    -- AvgRealTimeData is a table that contains the Real Time Data after having been averaged.
    -- The next 2 statements recreate the table every time GetDataSample is executed
    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AvgRealTimeData')
    DROP TABLE AvgRealTimeData
    CREATE TABLE AvgRealTimeData (RealTimeData INT)

    -- SamplesSet is a table that contains a set of samples from the RealTimeData table which will
    -- be averaged to a single value and inserted into the AvgRealTimeData table.
    -- The next 2 statements recreate the table every time GetDataSample is executed
    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SamplesSet')
    DROP TABLE SamplesSet
    CREATE TABLE SamplesSet (RealTimeData INT)

    -- Open the cursor
    OPEN RealTimeData_Csr

    -- Perform the first fetch
    FETCH NEXT FROM RealTimeData_Csr INTO @intCurrentValue

    -- Keep fetching rows until passed the end of the cursor.
    while @@FETCH_STATUS = 0
    BEGIN
    if ( (SELECT COUNT(*) FROM SamplesSet) = @intSampleSize )
    BEGIN
    SET @intAvgValue = (SELECT AVG(RealTimeData) FROM SamplesSet)
    INSERT AvgRealTimeData (RealTimeData) VALUES (@intAvgValue)
    DELETE FROM SamplesSet
    END
    ELSE
    FETCH NEXT FROM RealTimeData_Csr INTO @intCurrentValue
    INSERT SamplesSet (RealTimeData) VALUES (@intCurrentValue)
    END

    -- Ignore the remaining samples if they are less than the sample size
    /*
    if ( (SELECT COUNT(*) FROM SamplesSet) <> 0 )
    BEGIN
    INSERT AvgRealTimeData (RealTimeData) VALUES (SELECT AVG(RealTimeData) FROM SamplesSet)
    -- No need to delete the contents of the SamplesSetTable at the end since it will be recreated next time
    -- the procedure is called
    END
    */

    -- Destroy the cursor
    CLOSE RealTimeData_Csr
    DEALLOCATE RealTimeData_Csr

    -- Return the AvgRealTimeData table so it can be contained by an ADO recordset
    SELECT * FROM AvgRealTimeData

    GO

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No such thing as ROWCOUNT in SQL Server.....Oracle background?

    There are several fundamental differences....

    Cursors being one of them....while everyone shags Oracle because they say it's a CURSOR based RDBMS (It's not), it's imporatnt to avoid them anywhere...

    What are you simply trying to do?

    You're open/fetch/insert/loop/ect

    Are sure it could be condezed in to 1 INSERT INTO Table SELECT Colmns FROM Table WHERE Condition
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    Looks like SELECT TOP n (*) does what ROWCOUNT would do.

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    3

    Can't use SELECT TOP n when n is a variable

    Hi Brett,

    Thanks for your reply. Unfortunately, I haven't got Oracle background, I mean, I haven't got any SQL background at all! I only started working with MSDE and T-SQL last week. I am an electronics engineer and I usually do embedded software (C) or Visual Basic, but I had to start working with MSDE/SQL Server due to the requirements of a new application.

    What do you mean when you say ROWCOUNT doesn't exist in SQL Server? I'm using MSDE 2000 and reading the documentation for MS SQL Server 2000, it states (http://msdn.microsoft.com/library/de...t-set_0bjo.asp) : "SET ROWCOUNT
    Causes Microsoft® SQL Server™ to stop processing the query after the specified number of rows are returned."

    I thought about using SELECT TOP n, but I don't know n in advance, n is a parameter passed to the procedure, and SQL Server won't allow me to do a SELECT TOP @intNoOfPoints.

    Regarding your second question, what I'm trying to achieve is :
    Say, I've got a table with 20 rows, and from that table I need to obtain a new one with less points (rows). Each one of these new points will be calculated by averaging a number of samples from the first table. So, if the sample size is 2, the first row on the second table will be calculated by averaging rows 1 and 2 from the first table. The second row will be the average of rows 2, 3 from the first table; the 3rd row the average of rows 4,5 and so on.

    That's why I thought I needed the cursor, because as far as I can see there's no arrays in T-SQL, and the cursor was the only way I could manage to iterate (or scroll) through the first table by using the while loop.

    If I go into more detail, I can say that the first table contains points of a graph spaced 1 second apart. The second table contains a second representation of the graph where a user can specify a time interval (1 min, 5 min, 10 min...) in case he/she wants to zoom out and see the data i.e. in minutes, hence the sample size. For example, if the sample size is 60, every 60 points on the first graph will constitute a point (after averaging) on the second graph, and therefore, the data on the second graph will be displayed in minutes.

    I know this is a really long post and perhaps I shouldn't have gone into much detail, but I prefer being detailed and answer any possible questions before they get asked.

    Again, thanks a lot for your reply and sorry for the long post.

  5. #5
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    CREATE PROCEDURE dbo.MyProc
    @np Integer
    AS
    DECLARE @sql Varchar(255)

    SET @sql = 'SELECT TOP ' + Cast(@np As Varchar) + ' * From Table'
    EXEC (@sql)

    GO

    -- Works here

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well......

    The Order of data in database is meaningless...

    So even when you use a cursor you still would need to tell and RDBMS what the order is suppose to be.

    This is becuase you could run and rerun the same code and get different results.

    Do you have something that say how the rows of data relate to each other?

    Alot of times an ADD_DT column is used...


    Still not sure I follow...

    Since this is "you're first go" at SQL, I'm just curious as to your background...

    Seems like it's mathematics....no?

    If it is (or even if its not) you should google about set theory...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    Chester Springs, PA
    Posts
    16

    Ordering & Temp Tables

    Originally posted by Brett Kaiser
    Well......

    The Order of data in database is meaningless...

    SET ROWCOUNT will not apply the order by on the complete data set before returning rows. If you use this, the engine pulls the first n rows based on normal return specifications (seemingly random for non PK selction, PK dictionary order for PK selection, cluster order for tables with clustered indexes) and then apply an order by to the n rows.

    SELECT TOP n will apply the order by to the whole table and then return the n rows. Much more useful.

    Databases don't need arrays. They have tables and views. If you have a need for an array create a temp table and populate it with the data you're trying to 'zoom' through. This is accomplished by naming a table with a single hash [CREATE TABLE #tablename] for a local temp table or a double hash [CREATE TABLE ##tablename] for a global temp table. Local temp tables are available only to the connection that created them. Global temps are available across connections. It is important to note that a dynamic execution is treated as a new connection and therefore dynamic SQL executions cannot see local temps created by parent transactions.

  8. #8
    Join Date
    Feb 2004
    Location
    UK
    Posts
    3

    Sorted, but with 2 little questions

    Many thanks to everyone for all your help.

    I've finally rewritten my stored procedure so it doesn't use evil cursors and I use a temporary table to effectively simulate an array of values. See the code at the end of this post.

    dbslave: since I'm not using ORDER BY in my SELECT statement, @@ROWCOUNT is valid for me. Thanks a lot for the tip on tables instead of arrays.

    Brett: as I said on my second post, my background is electronics, not maths. I usually do hardware and software design for embedded systems applied to control and automation -usually in C, assembler or VB6. My only experience with databases (apart from the current project) have been on a project involving the Jet engine and ADO through Visual Basic.

    Just a couple of questions more: is there any difference in performance between using a view or a temporary table? The reason I'm asking is because when running my stored procedure from VB (averaging 600 points with a sample size of 60) it takes about 1.2 seconds for the data to be returned, and that is a wee bit too slow for my application. I'm just trying to think of a way of speeding it up a bit.

    And finally, I'm having some funnies when calling this code from VB using ADO. After executing the procedure, the data is present in the table containing the results as expected, so I can read it fine by doing a "SELECT * FROM AvgRealTimeData". But what I was trying to do was to get this data returned into the recordset that invokes the stored procedure. I thought that by including "SELECT RealTimeData FROM AvgRealTimeData" at the end of the procedure I would achieve this, but when I use VB to execute the stored procedure like this:

    m_rstAveragedData.Open "GetDataSample (100,25)", _
    m_cnnRtdbConnection, adOpenStatic, , adCmdStoredProc

    the m_rstAveragedData contains no fields. after reading the documentation, I've been trying to change the cursor location and type, but with no success.

    Anyway, thanks again for your help. If someone reads this and thinks of any suggestions, I'll appreciate any comments as usual.

    CREATE PROCEDURE dbo.GetDataSample
    @intNoOfPoints INT,
    @intSampleSize INT
    AS

    DECLARE @intCurrentValue INT, @intAvgValue INT,
    @intLoopCount INT, @intCount INT

    -- AvgRealTimeData is a table that contains the Real Time Data after having been averaged.
    -- The next 2 statements recreate the table every time GetDataSample is executed
    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AvgRealTimeData')
    DELETE AvgRealTimeData
    ELSE
    CREATE TABLE AvgRealTimeData (RealTimeData INT)

    -- SamplesSet is a table that contains a set of samples from the RealTimeData table which will
    -- be averaged to a single value and inserted into the AvgRealTimeData table.
    -- The next 2 statements recreate the table every time GetDataSample is executed
    IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SamplesSet')
    DELETE SamplesSet
    ELSE
    CREATE TABLE SamplesSet (RealTimeData INT)

    SET ROWCOUNT @intNoOfPoints
    SELECT IDENTITY(INT,1,1) ID, RealTimeData INTO #TempTable FROM Sawtooth
    SET @intCount = @@ROWCOUNT
    SET ROWCOUNT 0
    SET @intLoopCount = 1

    WHILE @intLoopCount <= @intCount
    BEGIN

    SET @intCurrentValue = (SELECT RealTimeData FROM #TempTable WHERE ID = @intLoopCount)
    INSERT SamplesSet (RealTimeData) VALUES (@intCurrentValue)

    if ( (SELECT COUNT(*) FROM SamplesSet) = @intSampleSize )
    BEGIN
    SET @intAvgValue = (SELECT AVG(RealTimeData) FROM SamplesSet)
    INSERT AvgRealTimeData (RealTimeData) VALUES (@intAvgValue)
    DELETE FROM SamplesSet
    END

    SET @intLoopCount=@intLoopCount + 1
    END

    DROP TABLE #TempTable

    -- Return the AvgRealTimeData table so it can be contained by an ADO recordset
    SELECT RealTimeData FROM AvgRealTimeData

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •