Convert MS Access VBA code to T-SQL in a stored procedure
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:
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?
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.CloseWorks 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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Mike Eghtebas
Bottom line, without seeing your proc, nothing much could be done.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
GOASKER
Ok, this function returns the correct string
Now, to flange it into the original sproc
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
ENDNow, to flange it into the original sproc
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.
ASKER
I've been working on your suggestion of a function all morning, and slowly getting it flanged up, bit by bit
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
The suggestion to use a user-defined function was valuable, and how I approached the final code.
Thanks for prompting that, Mike.
Nick67