Solved

Convert MS Access VBA code to T-SQL in a stored procedure

Posted on 2015-01-16
12
704 Views
Last Modified: 2015-01-25
Ok, here's the fun.
I have a nicely normalized set of tables in an application with the backend in SQL Server.
The frontend is in MS Access.
Let's call it 'the picture windows' is being constructed in ASP.Net

In a report, each PieceID has a row of readings.
One row per PieceID
Each PieceID can have multiple 'criteria' as well as that row of readings.
That's a sticky requirement when you need to have everything stay in a single row.
So, in Access, in the Report's Detail Event I have this:
Dim rs As Recordset
Dim strPieceCriteria As String
'Grab a recordset of the nominals criteria for the present PieceID
Set rs = CurrentDb.OpenRecordset("select * from qryCriteriaByPieceID where typenumber = 1 and PieceID = " & Me.PieceID, dbOpenDynaset, dbSeeChanges)
'Empty the string of anything that may be in it
strPieceCriteria = ""
'if we've got criteria
If rs.RecordCount <> 0 Then
    Do Until rs.EOF 'lets loop
        If Len(strPieceCriteria) <> 0 Then
            'add a spacer after any existing string
            strPieceCriteria = strPieceCriteria & " / "
        End If
        'add the number and text part of the criteria to the string
        strPieceCriteria = strPieceCriteria & Format(rs!CriteriaValue, "#.0") & " " & rs!CriteriaText
        rs.MoveNext
    Loop
Else
    strPieceCriteria = ""
End If
'kick this out to the Report in a textbox
Me.txtNominal = strPieceCriteria
rs.Close

Open in new window


Works very nicely.  Very standard MS Access report technique.
Not so nice for an ASP.Net gridview however.
And playing with GridView RowDataBound() events is tedious and perhaps very bad for performance -- to run to the server for dataset for each PieceID and the do a similar concatenation in VB.Net syntax.  That's doable, but likely to be a dog.

But, I have a stored procedure going that grabs the main data.
How can I write up the logic in the code above so that the stored procedure grabs the appropriate data, does the concatenation and returns the result as a field -- which is what I need?
0
Comment
Question by:Nick67
  • 6
  • 6
12 Comments
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 500 total points
ID: 40554719
re:> But, I have a stored procedure going that grabs the main data.

So for now, add a function returning a constant like 'abc' to your proc in order to populate your gridview temporarily with this constant. Then you can post your proc to see how:

1. The new function has to be incorporated with the proc if not done yet, and
2. How to complete the function to perform exactly like your vba above.

Mike

This post has been revised.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40554819
Bottom line, without seeing your proc, nothing much could be done.
0
 
LVL 26

Author Comment

by:Nick67
ID: 40554869
Mike,
Of course that's not the bottom line.
For the purposes of drastic oversimplification the sproc could be

Select PieceID from tblFlowlineReadings where JobID = @JobID

The loop logic is in the code posted.
Basically, there's qryCriteriaByPieceID
PieceCritieriaID   PieceID  CriteriaValue TypeID CriteriaText
1                              1              10.0                1              3" Male
2                              1               11.0               1              3" Female

The code loops through and creates the string
10.0 3" Male / 11.0 3" Female

Which is all very nice in VBA with looping code.
But T-SQL and loops are not good friends.
So, how do I get the same result in T-SQL?
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 500 total points
ID: 40554916
I made the following tables to test the code below:

Table Q_28598483 (to replace qryCriteriaByPieceID, replace this with a view in SQL Server)
PC_ID   PieceID  CriteriaValue TypeID CriteriaText
1	1	   10	   	 1	 3" Male
2	1   	   11	   	 1	 3" Female
3	2	   13	   	 1	 2" Male
4	2	   15	   	 1	 2" Female

Open in new window

Table tblFlowlineReadingsQ_28598483
PieceID   JobID
1	100
1	200

Open in new window


Hear is the proc (which returns:  1      10 3" Male\11 3" Female)
Use EE
go
CREATE PROCEDURE dbo.spQ_28598483(@JobID int)
AS
Begin
DECLARE @ReturnString as varchar(25) ='';
DECLARE @PieceID as int;
Declare @result as varchar(25);

Select @PieceID = PieceID 
  From tblFlowlineReadingsQ_28598483  
  where JobID = @JobID;
 
DECLARE abc CURSOR FOR
  SELECT t.CriteriaValue + ' ' + t.CriteriaText As result   
  FROM Q_28598483 t 
  WHERE t.PieceID = @PieceID;

OPEN abc;
FETCH NEXT FROM abc INTO @result   
WHILE (@@FETCH_STATUS = 0)
Begin
  if @ReturnString =''
		set @ReturnString = @result; 
   else
        set @ReturnString = @ReturnString + '\' + @result; 
   FETCH NEXT FROM abc INTO @result 
End
CLOSE abc;
DEALLOCATE abc;

Select PieceID, CAST(@ReturnString AS VARCHAR(25)) from tblFlowlineReadingsQ_28598483  where JobID = @JobID

end

/*
exec dbo.spQ_28598483 200
Select * From tblFlowlineReadingsQ_28598483
Select * From Q_28598483
*/

Open in new window

0
 
LVL 26

Author Comment

by:Nick67
ID: 40558236
Here's the scalar function I created
Everything I read suggests that cursors are of the devil and to be avoided
Something's wrong in the While loop

ALTER FUNCTION ufnCriteriaByPieceType 
(
	-- Add the parameters for the function here
	@PieceID int, 
	@TypeID int
)
RETURNS nvarchar(max)
AS
BEGIN
Declare @FinalString nvarchar(255)
DECLARE  @imax INT 
DECLARE  @i INT 
DECLARE  @CriteriaValue float 
DECLARE  @CriteriaText nvarchar(255)
DECLARE  @CriteriaInfo  TABLE( 
                                 RowID       INT    IDENTITY ( 1 , 1 ), 
                                 CriteriaValue float, 
                                 CriteriaText nvarchar(255) 
                                 ) 
INSERT @CriteriaInfo 
SELECT tblCriteria.CriteriaValue, tblCriteria.CriteriaText
FROM (tblPieces INNER JOIN tblPieceCriteria ON tblPieces.PieceID = tblPieceCriteria.PieceID) INNER JOIN tblCriteria ON tblPieceCriteria.CriteriaID = tblCriteria.CriteriaID
Where tblPieceCriteria.PieceID = 1 and tblCriteria.TypeNumber = 2
Order by  tblPieceCriteria.PieceCriteriaID

SET @imax = @@ROWCOUNT 
SET @i = 1 

    WHILE (@i <= @imax) 
      BEGIN 
        SELECT @CriteriaValue = CriteriaValue, 
               @CriteriaText = CriteriaText
        FROM   @CriteriaInfo 
        WHERE  RowID = @i        
		set @FinalString = @FinalString + Cast(@CriteriaValue as Nvarchar(255)) + ' ' + @CriteriaText
        set @FinalString = Case when @i <> @imax then @FinalString + ' / ' else @FinalString end
        SET @i = @i + 1 
      END -- WHILE
RETURN @FinalString
END
GO

Open in new window

0
 
LVL 26

Author Comment

by:Nick67
ID: 40558266
Ok, this function returns the correct string

ALTER FUNCTION ufnCriteriaByPieceType 
(
	-- Add the parameters for the function here
	@PieceID int, 
	@TypeID int
)
RETURNS nvarchar(max)
AS
BEGIN
Declare @FinalString nvarchar(255)
DECLARE  @imax INT 
DECLARE  @i INT 
DECLARE  @CriteriaValue float 
DECLARE  @CriteriaText nvarchar(255)
DECLARE  @CriteriaInfo  TABLE( 
                                 RowID       INT    IDENTITY ( 1 , 1 ), 
                                 CriteriaValue float, 
                                 CriteriaText nvarchar(255) 
                                 ) 
INSERT @CriteriaInfo 
SELECT tblCriteria.CriteriaValue, tblCriteria.CriteriaText
FROM (tblPieces INNER JOIN tblPieceCriteria ON tblPieces.PieceID = tblPieceCriteria.PieceID) INNER JOIN tblCriteria ON tblPieceCriteria.CriteriaID = tblCriteria.CriteriaID
Where tblPieceCriteria.PieceID = 1 and tblCriteria.TypeNumber = 2
Order by  tblPieceCriteria.PieceCriteriaID

SET @imax = @@ROWCOUNT 
SET @i = 1 

      WHILE (@i <= @imax) 
      BEGIN 
        SELECT @CriteriaValue = CriteriaValue, 
               @CriteriaText = CriteriaText
        FROM   @CriteriaInfo 
        WHERE  RowID = @i   
		set @FinalString = isnull(@FinalString,'') + Cast(@CriteriaValue as Nvarchar(255)) + ' ' + @CriteriaText
        set @FinalString = Case when @i <> @imax then isnull(@FinalString,'') + ' / ' else isnull(@FinalString,'') end
        SET @i = @i + 1 
      END -- WHILE


RETURN @FinalString 
END

Open in new window


Now, to flange it into the original sproc
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40558286
Although I first suggested using a function, but later I included field concatenation in a proc. Please take a look at my previous post to see if it is of some use for you.
0
 
LVL 26

Author Comment

by:Nick67
ID: 40558323
I've been working on your suggestion of a function all morning, and slowly getting it flanged up, bit by bit
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 500 total points
ID: 40558363
Does your proc returns single row? If yes, a scalar function will be fine to use.

Does your proc return multiple records? If yes, the function ought to return a table to hand-shake with your proc.

Because I didn't know the details of your proc, I combined proc and function in a proc as described at my last solution.

I will recommend to take a look at it and make it work with your project's specific needs.

Mike
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 0 total points
ID: 40558558
The sproc is very meaty and returns (now) 21 columns and numerous rows, including now two calls to the UDF.
The UDF now works as desired, returning a string for each row, based on the row's PieceID
Alter PROCEDURE qryFlowLineReadingsByWO 
	-- Add the parameters for the stored procedure here
	@WONum nvarchar(255) = ''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @JobID int
declare @pieceID int
Declare @nominal nvarchar(max)

SELECT @JobID = jobID from tblInsDetails where [wo#] = @WONum
	
SELECT tblFlowlineReadings.JobID, 
tblFlowlineReadings.ReadingID, 
tblFlowlineReadings.PieceID,
/**
(Case when tblFlowlineReadings.[UT1]=0 then '' else dbo.tblFlowlineReadings.[UT1] end) AS [UT-1], 
(Case when tblFlowlineReadings.[UT2]=0 then '' else dbo.tblFlowlineReadings.[UT2] end) AS [UT-2], 
(case when tblFlowlineReadings.[UT3]=0 then '' else dbo.tblFlowlineReadings.[UT3] end) AS [UT-3], 
(Case when tblFlowlineReadings.[UT4]=0 then '' else dbo.tblFlowlineReadings.[UT4] end) AS [UT-4], 
(Case when tblFlowlineReadings.[UT5]=0 then '' else dbo.tblFlowlineReadings.[UT5] end) AS [UT-5], 
(Case when tblFlowlineReadings.[UT6]=0 then '' else dbo.tblFlowlineReadings.[UT6] end) AS [UT-6], 
(Case when tblFlowlineReadings.[UT7]=0 then '' else dbo.tblFlowlineReadings.[UT7] end) AS [UT-7], 
(Case when tblFlowlineReadings.[UT8]=0 then '' else dbo.tblFlowlineReadings.[UT8] end) AS [UT-8],
**/
[UT1] AS [UT-1],
[UT2] AS [UT-2], 
[UT3] AS [UT-3], 
[UT4] AS [UT-4], 
[UT5] AS [UT-5], 
[UT6] AS [UT-6], 
[UT7] AS [UT-7], 
[UT8] AS [UT-8],  
tblPieces.SerialNumber, 
tblPartDescriptions.PartDescription, 
tblMakes.Manufacturer, 
tblInsDetails.[WO#], 
tblInsDetails.Date, 
tblClients.[Client Name], 
tblCertResults.Unit, 
tblPartDescriptions.PartID,
dbo.ufnCriteriaByPieceType(tblFlowlineReadings.PieceID,1) as Nominal,
dbo.ufnCriteriaByPieceType(tblFlowlineReadings.PieceID,2) as Minimum

FROM tblClients INNER JOIN 
((((tblPartDescriptions RIGHT JOIN 
(tblMakes RIGHT JOIN 
(tblFlowlineReadings INNER JOIN 
tblPieces ON tblFlowlineReadings.PieceID = tblPieces.PieceID) 
ON tblMakes.MakeID = tblPieces.MakeID) 
ON tblPartDescriptions.PartID = tblPieces.PartID) LEFT JOIN 
tblTypes ON tblPieces.TypeID = tblTypes.TypeID) INNER JOIN 
tblInsDetails ON tblFlowlineReadings.JobID = 
tblInsDetails.JobID) INNER JOIN tblCertResults ON
tblInsDetails.JobID = tblCertResults.JobID) ON 
tblClients.[Client ID] = tblInsDetails.ConsigneeID
WHERE (((tblInsDetails.JobID)=@JobID));	

END

Open in new window


And here's the UDF
ALTER FUNCTION ufnCriteriaByPieceType 
(
	-- Add the parameters for the function here
	@PieceID int, 
	@TypeID int
)
RETURNS nvarchar(max)
AS
BEGIN
Declare @FinalString nvarchar(255)
DECLARE  @imax INT 
DECLARE  @i INT 
DECLARE  @CriteriaValue float 
DECLARE  @CriteriaText nvarchar(255)
DECLARE  @CriteriaInfo  TABLE( 
                                 RowID       INT    IDENTITY ( 1 , 1 ), 
                                 CriteriaValue float, 
                                 CriteriaText nvarchar(255) 
                                 ) 
INSERT @CriteriaInfo 
SELECT tblCriteria.CriteriaValue, isnull(tblCriteria.CriteriaText,'')
FROM (tblPieces INNER JOIN tblPieceCriteria ON tblPieces.PieceID = tblPieceCriteria.PieceID) INNER JOIN tblCriteria ON tblPieceCriteria.CriteriaID = tblCriteria.CriteriaID
Where tblPieceCriteria.PieceID = @PieceID and tblCriteria.TypeNumber = @TypeID
Order by  tblPieceCriteria.PieceCriteriaID

SET @imax = @@ROWCOUNT 
SET @i = 1 

      WHILE (@i <= @imax) 
      BEGIN 
        SELECT @CriteriaValue = CriteriaValue, 
               @CriteriaText = CriteriaText
        FROM   @CriteriaInfo 
        WHERE  RowID = @i   
        set @FinalString = isnull(@FinalString,'') + ltrim(str(@CriteriaValue,10,1)) + ' ' + @CriteriaText
        set @FinalString = Case when @i <> @imax then isnull(@FinalString,'') + ' / ' else isnull(@FinalString,'') end
        SET @i = @i + 1 
      END -- WHILE


RETURN isnull(@FinalString,'') 
END

Open in new window


It turned out that the isnull() in the concatenation was important
       set @FinalString = isnull(@FinalString,'') + Ltrim(str(@CriteriaValue,10,1)) + ' ' + @CriteriaText
        set @FinalString = Case when @i <> @imax then isnull(@FinalString,'') + ' / ' else isnull(@FinalString,'') end

Without it, the function returned null
STR() was needful to keep any trailing xx.0 from being truncated
LTRIM() was then needful to remove the resulting leading blank spaces.

But it's flanged up and delivering the desired results, one per row to the GridView.
A good chunk of the function was adapted from here
http://www.sqlusa.com/bestpractices/whilelooptablevariable/
And this --and Erland's stuff in general, is invaluable, although tangential to the problem at hand
http://www.sommarskog.se/share_data.html
and I had a look here
https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

It was only looking further down that I saw the redoubtable Phil Factor's method
DECLARE @accumulation VARCHAR(7000),
 --variable used for accumulating lists
 @CategoryID INT
 --variable used for keeping tabs on the GROUPING id

DECLARE @grouping TABLE--temporary table
(
   MyID INT IDENTITY(1, 1) PRIMARY KEY,
   CategoryID INT,
   ProductName VARCHAR(255),
   accumulation VARCHAR(7000)--used to collect the list
)
INSERT INTO @Grouping --insert raw result you want a GROUPING of
(CategoryID, ProductName)
   SELECT CategoryID, ProductName
       FROM Northwind..Products
       ORDER BY CategoryID, ProductName

UPDATE @grouping --and update the table, doing the accumulation.
   SET @Accumulation = [accumulation]
       = COALESCE(
               CASE WHEN CategoryID <> COALESCE(@CategoryID, 0)
               THEN '' + productName
               ELSE LEFT(@Accumulation + ',' + productName, 7000)
           END,
           ''),
       @CategoryID = CategoryID

SELECT CategoryID, MAX(accumulation)
   FROM @grouping
   GROUP BY CategoryID
   ORDER BY CategoryID

That's fairly neat!  Using the INSERT as the ersatz loop and UPDATE to concatenate.
That's the kind of set logic I was looking for!

No matter.
These are very small result sets, so it'll be ok.
0
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 500 total points
ID: 40558588
BTW, you may prefer to use tbd (to be determined) or n/a in:

 isnull(@FinalString,'n/a)  

if is helpful.

You worked so hard on this, I feel awarding some points to you. LOL

Congratulations on the solution.

Mike
0
 
LVL 26

Author Closing Comment

by:Nick67
ID: 40568988
I hunted down and created the solution documented in the accepted answer post.
The suggestion to use a user-defined function was valuable, and how I approached the final code.
Thanks for prompting that, Mike.

Nick67
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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.

758 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

20 Experts available now in Live!

Get 1:1 Help Now