There are situations where you need to create SQL on the fly from a record that you are currently dealing with. This is sometimes referred to as Dynamic SQL. Here I start investigating creating a dynamically created SQL view using a stored procedure. In my case I was doing this so that I could create some particularly valuable values.
Here we have a table called T0001Persons that has the following three fields
PKID
Firstname
DateCreated
How do we create a stored procedure that will Create a view
USE yourdatabasename
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[auditselection]
(@firstname nvarchar(15),
@Dcreate datetime)
AS
BEGIN
SET NOCOUNT ON
EXEC ('CREATE or ALTER VIEW v01 AS SELECT dbo.T0001Persons.PKID,
dbo.T0001Persons.Firstname,
dbo.T0001Persons.DateCreated
FROM
dbo.T0001Persons WHERE dbo.T0001Persons.Firstname=' + ''''
+ @firstname + '''' + ' AND dbo.T0001Persons.DateCreated <
' + '''' + @Dcreate + '''' +
'')
END
and then an example execution is
EXEC auditselection 'Mark', '2021-10-03'
Using the script as facility in SSMS we can identify the query that has been created by EXEC auditselection 'Mark', '2021-10-03'.
SELECT dbo.T0001Persons.PKID, dbo.T0001Persons.Firstname, dbo.T0001Persons.DateCreated FROM dbo.T0001Persons WHERE dbo.T0001Persons.Firstname='Mark'AND dbo.T0001Persons.DateCreated<'Oct 3 2021 12:00AM'
Several things should be noted here
Within the EXEC statement we use a single quote to escape the string and then a plus sign followed by the parameter to insert the parameter into the SQL that will be executed. A single quote is then used again to escape the code to go back into the string.
But what if the SQL we want to run needs to include a single quote (which is often the case). I tried escaping the the string with a single quote and then a plus sign and then a Unicode Character references (only tried 39) surrounding a single quote but that didn’t really work.
What did work was to have four single quotes with two pluses on either side.
+ '''' +
Here a single quote escapes the code and starts the string again. Two single quotes together are interpreted as a single quote (clearly some kind of special interpretation is happening in the background) and then the last single quote escapes the string back into code.
From a discussion on Stackoverflow I also found the option to SET QUOTED_IDENTIFIER switch.
If escaping your single quote with another single quote isn’t working for you (like it didn’t for one of my recent REPLACE() queries), you can use SET QUOTED_IDENTIFIER OFF before your query, then SET QUOTED_IDENTIFIER ON after your query.
SET QUOTED_IDENTIFIER OFF; UPDATE TABLE SET NAME = REPLACE(NAME, "'S", "S"); SET QUOTED_IDENTIFIER ON; -- set OFF then ON again
Next up I will be passing parameters to parameterized stored procedure via PHP Runner where the parameters are taken from an onscreen record.
PHPRunner create an event to pass parameters to a stored procedure
This looks promising
Passing one of the field values as a parameter:
$sql = DB::PrepareSQL( "EXEC StoredProcNameHere ':1'", $values["FieldName"] ); DB::Exec( $sql );
and I have discovered that the :1 is considered a placeholder for the following variable and so passing two variables would come in as the following assuming both first and second parameters are text values. If :1 was the target of an integer instead of ‘:1’ we would have :1.
$sql = DB::PrepareSQL( "EXEC auditselection ':1',':2'", $values["FieldName1"], $values["FieldName2"]); DB::Exec( $sql );
This is php here the single quotes DO NOT escape the string. They will form part of the string that will be executed which is what I want.
The string is not escaped until the double quotes. Confusingly two single quotes look EXACTLY the same as a double quote but they are completely different. If you use your cursor you can see that the double quote is a single character which is interpreted completely differently by the php engine.
The following link may be important as well in determining what can be achieved
Grid row Javascript API