Solved

Access Function to SQL server

Posted on 2011-09-29
10
263 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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 142

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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 142

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 142

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now