Solved

Access Function to SQL server

Posted on 2011-09-29
10
268 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to keep a record with the highest value 3 42
IF SQL Query 12 29
Returning Message/Value Back to Access from a SQL Server trigger 3 35
Convert VBA UDF to SQl SERVER UDF 4 51
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…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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