Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-16
12
Medium Priority
?
745 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
[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
  • 6
  • 6
12 Comments
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 2000 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 34

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 2000 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
 
LVL 34

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 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 2000 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 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 2000 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

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!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

610 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