SQL Azure – Dynamic SQL – Using a parameterized Stored Procedure to Create a View

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