Solved

Access Function to SQL server

Posted on 2011-09-29
10
269 Views
Last Modified: 2012-05-12
I have the following function in an access database
Public Function Sequencing_SetGroupedSequence(pQueryName, pFieldName, pGroupField)
    Dim dbMe As Database
    Dim rsWork As Recordset
    Dim sequence
    Dim groupField
   
    Set dbMe = CurrentDb()
    Set rsWork = dbMe.OpenRecordset(pQueryName)
   
    sequence = 1
    groupField = -1
   
    Do Until rsWork.EOF
       
        If groupField <> rsWork(pGroupField) Then
            sequence = 1
            groupField = rsWork(pGroupField)
        End If
       
        rsWork.Edit
            rsWork.Fields(pFieldName) = sequence
        rsWork.Update
        rsWork.MoveNext
        sequence = sequence + 1
    Loop
   
    rsWork.Close
    Set rsWork = Nothing
    Set dbMe = Nothing
   
End Function

What is the best way to convert this to SQL server. A user defined function? or stored procedure?
Any guidance on how to convert it would be appreciated
0
Comment
Question by:DCMBS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 36814808
as this "function" is doing updates to the db, it cannot be a function in sql server.
it could be a stored procedure, as anyhow there is no result returned ...

what version of sql server?
if it's sql 2005, you could use something like this:

 update t
      set your_FieldName = sq.rn
   from yourtable t
   join ( select d.GroupField , d.primarykeyfield, row_number() over (partition by d.GroupField order by d.primarykefield) rn
            from yourtable d
        ) sq
    on sq.primarykeyfield = t.primarykefield 

Open in new window

to see the update/join syntax, you can also see here:
http://www.experts-exchange.com/A_1517.html

of course, this presumes a primary key field in your table

the second issue is that your code want's the table/column names as parameters.
so, you will need a dynamic sql, and a parameter to get the primary key field(s) ...

hope this helps to start with ...
0
 
LVL 9

Author Comment

by:DCMBS
ID: 36814953
This sql server 2008
I am not worried about passing the parameters. I am happy to ditch the parameters and code these values into the procedure.
I ahve come up with the following as a start

DECLARE @Sequence
DEClare @Groupfield

DECLARE @rsWorks CURSOR
SET @rsWorks = CURSOR FOR
SELECT Candidate_ID,Sequence
FROM ResultsLetterMailMerge_Work
ORDER BY Candidate_ID,School_Name

SET@Sequence = 1
Set @Groupfield = -1


OPEN @rsWorks
FETCH NEXT
FROM @rsWorks INTO @Groupfield,@Sequence

WHILE @@FETCH_STATUS = 0
BEGIN

IF Groupfield <> 

FETCH NEXT
FROM  @rsWorks INTO @Groupfield,@Sequence
END
CLOSE @rsWorks

DEALLOCATE @rsWorks

Am I on the right lines here.  If I am can you help with the bit that actually does work and updates the table?
0
 
LVL 7

Assisted Solution

by:luani
luani earned 250 total points
ID: 36815282
-- @groupFieldParam parameter of the stored proc
--

If @groupFieldParam <> @Groupfield
Begin
           SET  @sequence = 1
           SET @groupFieldParam=@Groupfield
End

Update
ResultsLetterMailMerge_Work set Sequence=@Sequence where Idoftherow=@idoftherow {you need to fetch the ID of the table in a varible so you can update the row }

SET @Sequence=@Sequence+1

0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 9

Author Comment

by:DCMBS
ID: 36815507
So the following should work? Am I correct in assuming the fetch will put the values in the variables according to the sequence they appear in the select and fetch statements?
DECLARE @Sequence int
      DECLARE @Groupfield int
      DECLARE @Current_Groupfield int
      DECLARE @CURRENT_Sequence int
      DECLARE @Consort int

      DECLARE @rsWorks CURSOR
      SET @rsWorks = CURSOR FOR
      SELECT Candidate_ID,ConsortiumSchoolID
      FROM ResultsLetterMailMerge_Work
      ORDER BY Candidate_ID,School_Name;

      SET @Sequence = 1;
      SET @Groupfield = -1;


      OPEN @rsWorks
      FETCH NEXT
      FROM @rsWorks INTO @Current_Groupfield,@Consort;

      WHILE @@FETCH_STATUS = 0
      BEGIN

      IF @Groupfield <> @Current_Groupfield
      BEGIN
            SET @Sequence = 1
            SET @Groupfield = @Current_Groupfield
      END

      UPDATE ResultsLetterMailMerge_Work
      SET Sequence = @Sequence
      Where ConsortiumSchoolID = @Consort and Candidate_ID = @Current_Groupfield
      
      SET @Sequence = @Sequence + 1
      FETCH NEXT
      FROM  @rsWorks INTO @Current_Groupfield,@Consort;
      END
      CLOSE @rsWorks

      DEALLOCATE @rsWorks
0
 
LVL 7

Assisted Solution

by:luani
luani earned 250 total points
ID: 36815772
you assume right.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36815785
yes, that could work, but cursor work is terribly bad in terms of performance, compared to a single query/update ...
0
 
LVL 9

Author Comment

by:DCMBS
ID: 36815801
If can be done without using a cursor I am open to suggetions.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36815977
please see my first post ... at least that is what I think it could be looking like.

if you take just the subquery, you can see how the data could look like.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 125 total points
ID: 36816320
you want to unlearn "to some extent" cursor processing...

you should be attempting to perform your updates to the whole set of rows at once , rather than on a Row by (agonising) Row (RBAR) process.

cursor processing is fairly slow and resource consuming within sql server ... do while loops can often be better if rbar processing is required.


you current update would seem to be able to be done simply by the following statement...

UPDATE U
      SET Sequence = rn     
      from (
      SELECT Candidate_ID,ConsortiumSchoolID
        ,ROW_NUMBER() over (partition by consortiumschoolid ORDER BY Candidate_ID,School_Name) as rn 
            FROM ResultsLetterMailMerge_Work
            ) as U

Open in new window


ie make use of the row_number() function of the OVER windowing function which provides a sequence number for
rows based on the partitioning columns in the sequence specified by the order by clause...

1 statement ... job done

put it in a stored procedure and execute via that rather than as a direct statement however,
0
 
LVL 9

Author Closing Comment

by:DCMBS
ID: 36890254
I will keep the cursor implementation for now and review the query method with a view to using it later. As Lowfatspread said I am used to programming in a linear way and the SQL queries are  not intuitively clear.  Many thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question