Convert VBA UDF to T-SQL Scalar UDF 1

[1DMF]

Hi,

I want to use a stored procedure for my dataset, but I need to do some calculations for the returned results.

How do I go about converting VBA logic into T-SQL UDF's and is this possible?

For example how would you write the following using a T-SQL Scalar UDF?

Public Function CheckRefs(ByVal iContactID As Integer) As String

    ' routine to check if references present and OK

    CheckRefs = "Yes"
    
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT Applied,Received,Acceptable FROM References WHERE ContactID = " & iContactID)

    If rs.RecordCount = 0 Then
        CheckRefs = "No"
    Else

        Do While Not rs.EOF
            If Nz(rs.Fields("Applied"), "") = "" Or Nz(rs.Fields("Received"), "") = "" Or Nz(rs.Fields("Acceptable"), "No") = "No" Then
                CheckRefs = "No"
                rs.MoveLast
            End If
            rs.MoveNext
        Loop
        
    End If
    
    Set rs = Nothing
    
End Function

Basically I need to check for each record in my recordset, if there are any references and if they are OK. the returned value needs to be a simple varchar(3) string of either 'Yes' or 'No'.

Is this possible?

Or would I be better running the SP, getting the record set, looping it and doing the calculation from within VBA?

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

You can create a sql server UDF to do this, but I would recommend against it.

One major problem with UDF's is caused by the way they are called. Basically, a call is made to the UDF for each row that is returned in your output. This may not be a problem if you are only returning a small handful of rows, but can become a problem with large datasets. The problem, of course, being performance.

Returning the dataset to VBA and then looping on it would be even worse because you would have multiple round trips to the server and would be even slower.

Instead, I would suggest that you write a query that satisfies the "Yes" condition or the "No" condition, and then using that as a left join to the original query (in the SP) to perform the calculations. This is likely to be faster than any other solution.

I'm not 100% sure what the NZ function does, but I think your closest equivalent would be this (with the loop removed).

Create function dbo.CheckRefs(@ContactId Int)
Returns Char(3)
AS
Begin
  Declare @Output Char(3)

  If Exists(SELECT Applied,Received,Acceptable 
            FROM   [References] 
            WHERE  ContactID = @ContactID
	           And (   Applied Is NULL 
		        Or Applied = ''
		        Or Received is NULL
		        Or Received = ''
		        Or Acceptable is NULL
		        Or Acceptable = 'No'
		        )
	  )
    Begin
      Set @Output = 'Yes'
    End
  Else
    Begin
      Set @Output = 'No'
    End

  Return @Output
End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Hi George,

NZ(Name,Value) is a Null check with default. If the identifier 'Name' is Null, then return 'Value' as a default, otherwise return its actual value.

I appreciate the UDF per row performance issue, though we aren't talking tonnes of records, so perhaps it's not a major problem, though I would prefer the most optimium solution!

I beleive the above has the 'Yes' and 'No' output the wrong way round, if a reference exisits that has any of those columns null or = 'No', then I wan't 'No' returned.

Though I think I prefer your other suggestion.

I get half the picture I believe with....

SELECT FirstName,Lastname,RefTable.Refs
FROM Contacts 
LEFT OUTER JOIN 
               (SELECT [References].ContactID,Refs='No'
                FROM [References] 
                WHERE Applied Is NULL 
                  Or Applied = ''
		  Or Received is NULL
		  Or Received = ''
		  Or Acceptable is NULL
		  Or Acceptable = 'No'
	        GROUP BY ContactID ) RefTable
 ON Contacts.ContactID = RefTable.ContactID

However, This gives me either 'No' or 'NULL', how do I get that to return 'No' or 'Yes' ?



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[1DMF]

And yes I have realised that that won't work for those with no references at all as I wan't 'No' and I'll get 'NULL'.

Close but alas no cigar :-(

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

Try this:

SELECT FirstName,Lastname,
       Case When RefTable.Refs = 1 Then 'Yes'
            Else 'No'
            End As Refs
FROM   Contacts 
       LEFT OUTER JOIN 
          (
          SELECT [References].ContactID,
                 Max(Case When Applied Is NULL 
                               Or Applied = ''
                               Or Received is NULL
                               Or Received = ''
                               Or Acceptable is NULL
                               Or Acceptable = 'No'
                          Then 0
	                  Else 1
		          End) As Refs
          FROM   [References] 
          GROUP BY ContactID 
          )
          As RefTable
          ON Contacts.ContactID = RefTable.ContactID

The way I see it (and I could be wrong)....

For each Contact Id in Contacts, there could be 0, 1 or more rows in the References table. If any one row in the references table represents a positive (Yes) result, you want to show yes. Otherwise, show (No). This is why I moved the where clause logic in to a case statement. This way, you will get a 1 (for yes) or a 0 (for no) for each contactid. If there is no matching contactid in the references table, you'll get a null on the left join, which is checked in the outer query with a case statement.

If any part of this doesn't make sense to you, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

George, you're a genius!

Though I'm not sure it is quite what I want.

If any one row in the references table represents a positive (Yes) result, you want to show yes.

No! It doesn't matter if there are rows that meet the 'Yes', if there are no rows or any one row that meets the 'No' then it's 'No' I want.

So either there are no references yet, so the result should be 'No' or there are references but at least one of them is not complete or is not acceptable then the answer is 'No', however if all references are complete and acceptable then the answer is 'Yes'.

Is 'Case' a T-SQL keyword or just SQL? I tried to port it to an Access query and it errors so assume this is MS T-SQL syntax.

I can't wait to start my SQL course next year, I have some serious holes in my SQL knowledge I need to plug for sure!

One thing I would like clarification of is the use of the 'MAX' aggregation function, is this what is giving me the 'if any of them are yes?' , so I get a 1 at the top (MAX)?

Do I simply change it to MIN and so if it is 0 then it's 'No'.



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

One thing I would like clarification of is the use of the 'MAX' aggregation function, is this what is giving me the 'if any of them are yes?' , so I get a 1 at the top (MAX)?

You are correct. If you change this to MIN instead, that should do it for you.

CASE is not strictly a T-SQL thing, other databases have it too. Unfortunately, Access is not one of them. In Access, you would need to use IIF instead. IIF takes 3 arguments, the first one is an evaluation condition, the second argument is the "true" and the 3rd is the false.

So this:

Case When Applied Is NULL 
                               Or Applied = ''
                               Or Received is NULL
                               Or Received = ''
                               Or Acceptable is NULL
                               Or Acceptable = 'No'
                          Then 0
	                  Else 1
		          End

Would look like this:

iif(Applied Is NULL 
     Or Applied = ''
     Or Received is NULL
     Or Received = ''
     Or Acceptable is NULL
     Or Acceptable = 'No', 0, 1)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Ahh I see, yes I am familiar with IIF, didn't link the two, same principle, different syntax, of course!

Though I've just extended the query for other 'calculated' columns I need (now I have the syntax!) and it processed 18,272 records in less than a second, awesome!

So I will be sticking with an SP once I get my head round all the calculations required, even if it locks my app to SyBase!

One of them is very complicated, due to combinations of columns that could equal 'Yes', but I shall have a stab and come back if I need any guidance.

Many thanks for your help, it is very much appreciated.

Regards,
1DMF



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Dance Music Downloads

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom