Issue with RAW SQL passed into a Function as an Argument to check if Data Exists

Question

I cannot touch the VB Code to make this part easier on myself. It's handled by a very small number of Devs out of State. So this is a SQL issue only. I know I could make the change in VB (so don't throw that as a solution) but, us Analysts can't. It's a multi billion dollar Corp and changes like this would go through a rigorous Approval process and would probably be denied. But, we have the freedom to make Custom Tables/Fields [in the Application] and Edits on a DB Level.

My Goal is to Create a function that Grabs RAW SQL from a Field on the Front End and pass it through as an Argument to prove if it's valid to "Print a Document". It returns True or False. If True, the document prints.

Example: The Script: This will take the SQL as hmy from property where scode = '26thst' in the Argument.

The Function: Grabs the SQL and just needs to check if the SQL Exists and pass back True or False

I have made the Function and it's legal but, does not seem to produce results. The Document gets skipped. I'm kind of stumped on an OOP process for SQL to prove True/False on an Execute @SQL

CASE 
        WHEN ISNULL(RS1.sdocument1, '') <> '' AND RS1.balwaysprint1 = 'NO'
            AND NYSQLFUNCTION(RS1.SSQL1) = 'TRUE' THEN RS1.RS1.DOCUMENT1            
        WHEN ISNULL(RS1.sdocument1, '') <> '' AND RS1.balwaysprint1 = 'YES'
            THEN RS1.RS1.DOCUMENT1
        END "_FILE_1"

CREATE FUNCTION NYSQLFUNCTION(@SQLTEXT NVARCHAR(MAX))
RETURNS VARCHAR(500)AS BEGIN
DECLARE @FILEPRINT AS VARCHAR(5)
IF EXISTS(SELECT @SQLTEXT) 
SET @FILEPRINT = 'TRUE'
ELSE
SET @FILEPRINT = 'FALSE'
RETURN @FILEPRINT END;

James S · Answer 1 · 2019-08-01T14:08:27.720

NOTE! When you are calling a function you generally need to include the schema (default dbo)... so dbo.NYSQLFUNCTION

Also... that function will always return 'TRUE' as far as I can see. If you want to at least check that some SQL has been supplied you could do this...

ie:

CREATE FUNCTION NYSQLFUNCTION(@SQLTEXT NVARCHAR(MAX))
RETURNS VARCHAR(500) AS 
BEGIN
  DECLARE @FILEPRINT AS VARCHAR(500)
  IF @SQLTEXT IS NOT NULL AND LEN(@SQLTEXT) > 0  
    SET @FILEPRINT = 'TRUE'
  ELSE
    SET @FILEPRINT = 'FALSE'
  RETURN @FILEPRINT 
END;

If you actually want to execute the SQL supplied then you'll want to change the function to do that.

... but you can't call sp_executeSQL to dynamically execute the SQL from within a function (because its a stored procedure.. see Execute Stored Procedure from a Function for more info)

So you can do one of the hideous hacks in that answer above...

Or... you might be able to use a Stored procedure instead of a function (depends on the code calling it)

Or... if you know the query is always going to be asking for entries from the same table you could just parse the text for the parameters. EG.... I have assumed that the SQLTEXT will ALWAYS be asking for an entry from the table 'property' with a where clause of scode = 'something'

CREATE FUNCTION NYSQLFUNCTION(@SQLTEXT NVARCHAR(MAX))
RETURNS VARCHAR(500) AS 
BEGIN

  DECLARE @FILEPRINT AS VARCHAR(500) = 'FALSE'

  -- eg.... @SQLTEXT should be something like....  hmy from property where scode = '26thst'
  DECLARE @scode NVARCHAR(500)
  DECLARE @scodeStartIndex INT, @sCodeEndIndex INT
  SELECT @scodeStartIndex = CHARINDEX(' scode ', @SQLTEXT)
  IF @scodeStartIndex > 0 
  BEGIN
    SELECT @scodeStartIndex = CHARINDEX('''', @SQLTEXT, @scodeStartIndex)
    IF @scodeStartIndex > 0
    BEGIN
      SELECT @sCodeEndIndex = CHARINDEX('''', @SQLTEXT, @scodeStartIndex + 1)
      IF @sCodeEndIndex > 0
      BEGIN
        SELECT @scode = SUBSTRING(@SQLTEXT, @scodeStartIndex + 1, @sCodeEndIndex - (@scodeStartIndex + 1))
        IF EXISTS (SELECT NULL FROM [property] WHERE scode = @scode)
        BEGIN
            SELECT @FILEPRINT = 'TRUE'
        END
      END
    END
  END

  RETURN @FILEPRINT 
END;

Or... one last option available to you (depending upon your ability to modify the database) is to create a CLR function that executes the supplied SQL query and returns true/false. You would be able to call a CLR function within your user defined function. Here's an article that might help: https://www.codeproject.com/Articles/1098543/Use-SQL-CLR-Function-to-Overcome-Limitation-of-SQL

Changed. Sorry that was my fault. I must not have saved my work from yesterday — Matt, Aug 01 '19 at 12:13
The @FILEPRINT variable also needs a length I've just noticed — James S, Aug 01 '19 at 12:15
The problem with this solution is it does not check if the Data Exists. If there is no hmy record in the Property Table for that AddressCode I want it to throw false. It's not about the Query itself. A good example is I want a Rider Document to Print if the person owns Rentable Items. So the User can use "Custom SQL" to check if they do. If so it'll print, if not, it gets skipped. — Matt, Aug 01 '19 at 12:29
ok... well you'll need to actually execute the SQL then. Let me edit the answer — James S, Aug 01 '19 at 12:30
Okay awesome. Each time I tried to use EXECUTE sp_executesql in an IF statement to prove != NULL the IDE complains. — Matt, Aug 01 '19 at 12:50
yes... it will do, as you can't have EXECUTE sp_executesql inside a function (but you can inside a stored procedure). See if my editted answer helps — James S, Aug 01 '19 at 13:20
This is really good. I can make some alterations for something more dynamic. If all else fails try a nested Function in a Stored Procedure. Thank you so much James! — Matt, Aug 01 '19 at 13:36
You're welcome. If that doesn't work out I've realised you also have the option of creating a CLR function to do it. Linked an article in the answer above — James S, Aug 01 '19 at 14:34

Issue with RAW SQL passed into a Function as an Argument to check if Data Exists

1 Answers1