2. PHPRunner General questions
  3. [SOLVED] Passing 2 variables to a SQL Azure Stored procedure
This topic is locked
[SOLVED]

Passing 2 variables to a SQL Azure Stored procedure

 		11/4/2021 3:33:28 PM
PHPRunner General questions
Dalkeith author

Dear All

I have a stored procedure in SQL Azure that accepts two arguments

I would normally execute it by writing

EXEC auditselection 'Mark', '2021-10-03'

from the how to documents I cam across the following

Executing an SQL Server stored procedure from events

which states that the way to run code with one parameter would be as follows

$sql = DB::PrepareSQL( "EXEC StoredProcNameHere ':1'", $values["FieldName"] );
DB::Exec( $sql );

Question is this
How do I pass across two fied values as parameters I am guessing something like below?

$sql = DB::PrepareSQL( "EXEC auditselection ':2'", $values["FieldName1"], $values["FieldName2"]);
DB::Exec( $sql );
Dalkeith author 11/4/2021

As ever writing down my thoughts helps me understand them...

I think SERVER code will be as follows

$sql = DB::PrepareSQL( "EXEC auditselection ':1',':2'", $values["FieldName1"], $values["FieldName2"]);
DB::Exec( $sql );

I deduced from below that :1 is actually a place holder for the following value so I am planning on inputting two variables so I will need two place holders.
Also the single quotes are actually part of the string that I want passed so I have not escaped the string by adding them.
The string is not escaped until the double quotes. (I have confirmed they are doubles by jumping back left and right using the cursor keys - its not possible to identify this any other way.

Somewhat confusingly the way you escape a string or escape code in SQL can be with a single quote...

Notes on Prepare SQL
admin 11/4/2021

Yes, the following is a correct syntax, assuming that both first and second parameters are text values:

$sql = DB::PrepareSQL( "EXEC auditselection ':1',':2'", $values["FieldName1"], $values["FieldName2"]);
DB::Exec( $sql );
Dalkeith author 11/5/2021

Thanks Xline

....and can I just check? Would the CLIENT BEFORE be something like

values["FieldName1"] = row.getFieldValue("FieldName1")
values["FieldName2"] = row.getFieldValue("FieldName2")
admin 11/5/2021

You need to explain your question fully as the latest code didn't make much sense to me. You are not even saying what page is this, is this ClientBefore event of a button or of a field event etc.

Dalkeith author 11/5/2021

Apologies - I was thinking about having a button in each line of the list view that will take the values of two fields in the row and be pass those to the stored procedure and the above code is aimed at being placed in the CLIENT BEFORE event.
Dalkeith author 11/6/2021

I nearly had it...

On the CLIENT BEFORE I had to alter the word values to params like so

params["Firstname"] = row.getFieldValue("Firstname");
params["Dcreate"] = row.getFieldValue("DateCreated");

Where Firstname and DateCreated are field values in my table T0001Persons

So in Javascript I am passing the field values to parameters called Firstname and Dcreate and then in PHP I pass those variables to the stored procedure and the database procedure does the rest.

and so I had to alter the syntax of the DB::PrepareSQL to...

$sql = DB::PrepareSQL( "EXEC auditselection ':1',':2'", $params["Firstname"], $params["Dcreate"]);
DB::Exec( $sql );

The above works but I am told from reading that it is better to indicate the schema and there should be a performance boost to that.

$sql = DB::PrepareSQL( "EXEC dbo.auditselection ':1', ':2'", $params["Firstname"], $params["Dcreate"]);
DB::Exec( $sql );

You can also reference the variables - I don't know if there is an advantage to this one but I include for completeness

$sql = DB::PrepareSQL( "EXEC dbo.auditselection @firstname=':1', @Dcreate=':2'", $params["Firstname"], $params["Dcreate"]);
DB::Exec( $sql );

And this is the syntax of the actual storedprocedure in SQL Azure.. where you can see the variables that I happen to have called @firstname and @Dcreate although they could have been named anything because PHP is passing the values and not the memory location(variable name) which makes sense because you are passing from the web server to the database server and the same variables don't exist in both.

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