asked on

Creating a function in SQL Server 2008r2 that loops through records and returns a string value.

Need help creating a function in SQL Server 2008r2 that loops through records and returns a string value.

I have a function written in VBA that loops through filtered records in a table and returns a string that i would like to move to SQL.

Public Function NDEOps(sBase As String, sLot As String, sSplit As String, sSub As String, iSSeq As Integer, iESeq As Integer) As String

Dim rst As DAO.Recordset
Dim sqlStr As String
Dim sOpsBetween As String

sOpsBetween = ""

sqlStr = "SELECT dbo_OPERATION.SEQUENCE_NO, dbo_OPERATION.RESOURCE_ID, dbo_OPERATION.SERVICE_ID, dbo_OPERATION.OPERATION_TYPE FROM dbo_OPERATION WHERE dbo_OPERATION.WORKORDER_TYPE='W' AND dbo_OPERATION.WORKORDER_BASE_ID='" & sBase & "' AND dbo_OPERATION.WORKORDER_LOT_ID='" & sLot & "' AND dbo_OPERATION.WORKORDER_SPLIT_ID='" & sSplit & "' AND dbo_OPERATION.WORKORDER_SUB_ID='" & sSub & "' AND dbo_OPERATION.SEQUENCE_NO>" & iSSeq & " And dbo_OPERATION.SEQUENCE_NO<" & iESeq & " Order by dbo_OPERATION.SEQUENCE_NO"
Set rst = CurrentDb.OpenRecordset(sqlStr)
If rst.RecordCount > 0 Then
   rst.MoveFirst
   Do Until rst.EOF
      If sOpsBetween <> "" Then 'not first one
         sOpsBetween = sOpsBetween & ", "
      End If
         sOpsBetween = sOpsBetween & rst("SEQUENCE_NO") & "-" & IIf(rst("OPERATION_TYPE") <> "", rst("OPERATION_TYPE"), IIf(rst("SERVICE_ID") <> "", rst("SERVICE_ID"), rst("RESOURCE_ID")))
      rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
End If
NDEOps = sOpsBetween

End Function

Open in new window

I would like to redo this logic in SqlServer 2008r2 and have been looking for the fastest solution.
I am new to creating functions in sql server and see that there are many ways to do this (cursors, Set based queries, temp table with or without identity columns....)

Any help would be greatly appreciated.

Ryan Chong

try create a stored procedure instead of a function. by using a stored procedure, you can do more stuffs that function can't do.

you're right to say that for looping a query in stored procedure, cursor will be used. but you may tell us the logic and your data patterns, sometimes it can be done just using pure SQL

BFanguy

ASKER

this is what i came up with. it did not increase the runtime of my existing query so i am guessing that it's impact was minimal.

alter FUNCTION [dbo].[CT_OpsBetween] (@TYPE nchar(1), @BASE nvarchar(30), @LOT nvarchar(3), @SPLIT nvarchar(3), @SUB nvarchar(3), @sSeq smallint, @eSeq smallint)
RETURNS nvarchar(255) 
AS
BEGIN

DECLARE @OpsBetween nvarchar(255);

DECLARE @MyCursor CURSOR;
DECLARE @SEQUENCE_NO smallint;
DECLARE @RESOURCE_ID nvarchar(15);
DECLARE @OPERATION_TYPE nvarchar(15);
DECLARE @SERVICE_ID nvarchar(15);

SET @OpsBetween = ''
SET @MyCursor = CURSOR FOR
     SELECT SEQUENCE_NO, RESOURCE_ID, OPERATION_TYPE, SERVICE_ID
     from dbo.OPERATION
     where WORKORDER_TYPE = @TYPE AND WORKORDER_BASE_ID = @BASE AND WORKORDER_LOT_ID = @LOT and WORKORDER_SPLIT_ID = @SPLIT AND WORKORDER_SUB_ID = @SUB AND SEQUENCE_NO > @sSeq AND SEQUENCE_NO < @eSeq
     ORDER BY SEQUENCE_NO
 
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @SEQUENCE_NO, @RESOURCE_ID, @OPERATION_TYPE, @SERVICE_ID
 
WHILE @@FETCH_STATUS = 0
   BEGIN
      IF @OpsBetween <> ''
         BEGIN
             set @OpsBetween = @OpsBetween + ', '
         END
      SET @OpsBetween = @OpsBetween + Cast(@SEQUENCE_NO as varchar(10)) + '-' + CASE WHEN @OPERATION_TYPE IS NOT NULL THEN @OPERATION_TYPE ELSE CASE WHEN @SERVICE_ID IS NOT NULL THEN @SERVICE_ID ELSE @RESOURCE_ID END END;
      FETCH NEXT FROM @MyCursor INTO @SEQUENCE_NO, @RESOURCE_ID, @OPERATION_TYPE, @SERVICE_ID      
   END; 
 
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;

RETURN @OpsBetween

END

Open in new window

BFanguy

ASKER

Note: using the function to pull the string in Sql Server, instead of access did speed up my access form by a factor of 5.

Ryan Chong

can you provide some sample data of table: dbo.OPERATION and your expected output when running the function CT_OpsBetween?

what values are you passing into CT_OpsBetween CT_OpsBetween ?

BFanguy

ASKER

Operation

WORKORDER_TYPE	WORKORDER_BASE_ID	WORKORDER_LOT_ID	WORKORDER_SPLIT_ID	WORKORDER_SUB_ID	SEQUENCE_NO	RESOURCE_ID	SERVICE_ID	OPERATION_TYPE
W	CC182460	1	0	0	5	QC	NULL	TRV APPR
W	CC182460	1	0	0	10	MISC	NULL	PROCURE
W	CC182460	1	0	0	20	QC	NULL	QC_MTR
W	CC182460	1	0	0	30	MISC	NULL	MARK ASSY API
W	CC182460	1	0	0	40	QC	NULL	HARDNESS 029
W	CC182460	1	0	0	50	QC	NULL	UTPA 040
W	CC182460	1	0	0	60	ASSY	NULL	NULL
W	CC182460	1	0	0	70	TEST	NULL	TEST API
W	CC182460	1	0	0	80	PAINT	NULL	PAINT 028
W	CC182460	1	0	0	90	TAG	       NULL	TAG
W	CC182460	1	0	0	100	QC	      NULL	QC FINAL 10.0
W	CC182460	1	0	0	110	MISC	NULL	PACK/SHIP 027
W	CC182460	1	0	0	120	FINAL REVIEW	NULL	NULL
W	CC182461	1	0	0	10	MISC	NULL	PROCURE
W	CC182461	1	0	0	20	ASSY	NULL	NULL
W	CC182461	1	0	0	30	LATHE	NULL	NULL
W	CC182461	1	0	0	40	MISC	NULL	MARK BALL
W	CC182461	1	0	0	50	XYLAN	NULL	XYLAN IN-HOUSE
W	CC182461	1	0	0	60	ASSY	NULL	ASSY BALL (F)
W	CC182461	1	0	0	70	TEST	NULL	TEST API
W	CC182461	1	0	0	80	TAG	NULL	TAG
W	CC182461	1	0	0	90	QC	NULL	QC FINAL 10.0
W	CC182461	1	0	0	100	MISC	NULL	PACK/SHIP 027
W	CC182461	1	0	0	110	FINAL REVIEW	NULL	NULL
W	CC182471	5	0	0	10	MISC	NULL	PROCURE
W	CC182471	5	0	0	20	MISC	NULL	NULL
W	CC182471	5	0	0	30	QC	NULL	HARDNESS 029
W	CC182471	5	0	0	40	QC	NULL	QC FINAL 10.0
W	CC182471	5	0	0	50	MISC	NULL	PACK/SHIP 027
W	CC182471	5	0	0	60	FINAL REVIEW	NULL	NULL

Open in new window

Values Passed

CT_OpsBetween('W','CC182461','1','0','0',20,70)

Open in new window

Expected Results:
30-LATHE, 40-MARK BALL, 50-XYLAN IN-HOUSE, 60-ASSY BALL (F)

Anthony Perkins

Do you still need this function?

BFanguy

ASKER

i have a solution. thanks

Anthony Perkins

Great! Please go ahead and close the question.

ASKER CERTIFIED SOLUTION

BFanguy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

I would recommend that you either post your solution or request this thread be closed. As is, this thread does not present any value.

BFanguy

ASKER

Anthony, i did post my solution on 10/15, but there is no "Yes! This is my solution", so i posted "figured it out" so i could move it to "Your problem is solved".

what else should i have done?

this was my solution

alter FUNCTION [dbo].[CT_OpsBetween] (@TYPE nchar(1), @BASE nvarchar(30), @LOT nvarchar(3), @SPLIT nvarchar(3), @SUB nvarchar(3), @sSeq smallint, @eSeq smallint)
RETURNS nvarchar(255) 
AS
BEGIN

DECLARE @OpsBetween nvarchar(255);

DECLARE @MyCursor CURSOR;
DECLARE @SEQUENCE_NO smallint;
DECLARE @RESOURCE_ID nvarchar(15);
DECLARE @OPERATION_TYPE nvarchar(15);
DECLARE @SERVICE_ID nvarchar(15);

SET @OpsBetween = ''
SET @MyCursor = CURSOR FOR
     SELECT SEQUENCE_NO, RESOURCE_ID, OPERATION_TYPE, SERVICE_ID
     from dbo.OPERATION
     where WORKORDER_TYPE = @TYPE AND WORKORDER_BASE_ID = @BASE AND WORKORDER_LOT_ID = @LOT and WORKORDER_SPLIT_ID = @SPLIT AND WORKORDER_SUB_ID = @SUB AND SEQUENCE_NO > @sSeq AND SEQUENCE_NO < @eSeq
     ORDER BY SEQUENCE_NO
 
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @SEQUENCE_NO, @RESOURCE_ID, @OPERATION_TYPE, @SERVICE_ID
 
WHILE @@FETCH_STATUS = 0
   BEGIN
      IF @OpsBetween <> ''
         BEGIN
             set @OpsBetween = @OpsBetween + ', '
         END
      SET @OpsBetween = @OpsBetween + Cast(@SEQUENCE_NO as varchar(10)) + '-' + CASE WHEN @OPERATION_TYPE IS NOT NULL THEN @OPERATION_TYPE ELSE CASE WHEN @SERVICE_ID IS NOT NULL THEN @SERVICE_ID ELSE @RESOURCE_ID END END;
      FETCH NEXT FROM @MyCursor INTO @SEQUENCE_NO, @RESOURCE_ID, @OPERATION_TYPE, @SERVICE_ID      
   END; 
 
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;

RETURN @OpsBetween

END

Open in new window

Anthony Perkins

I thought you were looking for better performance than looping ad nauseam, such as using a set-based solution or a CLR, but if that works for you that is fine with me.

The only thing I would add is that typically the way it works here, is that you select the comment with the solution, so that in future readers can easily find it, rather than some random comment.

BFanguy

ASKER

I'd like to revisit this.

My cursor loop is causing a 24 second delay.

you mentioned set-based solution or a CLR, can you give me some assistance on creating these?

Thank you in advance

Anthony Perkins

Solutions:
1. If you have SQL Server 2017 or 2019 you can use the new function STRING_AGG()

2. If you don't your choices are:

2.a. Xml (There are a lot of examples out there, I just don't like this approach)

2.b. Using a scalar function with something like this (untested):

alter function dbo.CT_OpsBetween (@TYPE nchar(1),
									  @BASE nvarchar(30),
									  @LOT nvarchar(3),
									  @SPLIT nvarchar(3),
									  @SUB nvarchar(3),
									  @sSeq smallint,
									  @eSeq smallint)
returns nvarchar(255)
as
	begin
		declare @OpsBetween nvarchar(255);

		select	 @OpsBetween = isnull(@OpsBetween, '') + N', ' + cast(SEQUENCE_NO as varchar(10)) + N'-'
							   + coalesce(OPERATION_TYPE, SERVICE_ID, RESOURCE_ID)
		from	 dbo.OPERATION
		where	 WORKORDER_TYPE = @TYPE
				 and WORKORDER_BASE_ID = @BASE
				 and WORKORDER_LOT_ID = @LOT
				 and WORKORDER_SPLIT_ID = @SPLIT
				 and WORKORDER_SUB_ID = @SUB
				 and SEQUENCE_NO > @sSeq
				 and SEQUENCE_NO < @eSeq
		order by SEQUENCE_NO;

		return @OpsBetween;
	end;

Open in new window

2.c. For a CLR solution, you can use this:
https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr

2.d. A Cursor