Stored Procedure with Dynamic SQL

Please note that the example below is purely for illustrative purposes. I do not recommend using Dynamic SQL queries in such a way as using normal Linx processes are MUCH simpler. I’m just illustrating a scenario where errors did occur in much more complex DSQL use-cases.

If, for instance, you are using Linx to call Stored Procedures, you will see that Linx requests the parameters from the SQL Database in order for you to set up the call. In rare examples where you are using input parameters of the call to generate Dynamic SQL queries within the Stored Procedure, you could run into similar errors to this one:

To understand why this error occurs, lets use the following Stored Proc as example (Again, this is just as an example and not how I would recommend you to use Linx):

CREATE PROCEDURE [dbo].[GetSpecificValue]

@TableName VarChar(50) = ‘’,
@FieldName Varchar(50) = ‘’,
@FilterField Varchar(50) = ‘’,
@FilterValue Varchar(50) = ‘’
AS
BEGIN
DECLARE @DSQL nvarchar(max) = ‘select ’ + @FieldName + ’ from ’ + @TableName + ’ where ’ + @FilterField + ’ = ‘’’ + @FilterValue+‘’‘’;
DECLARE @Return nvarchar(50);
EXECUTE sp_executesql @DSQL, @Return output;

END

In this Stored Procedure, you are using your input parameters as values inside of an SQL string, which is called. Thus, in the correct scenario, where you apply the correct values, that internal SQL string would look like this (or whatever values you enter as inputs, bolded):

select CompanyName from Customers where CustomerID = ‘BLAUS

But, when you are setting up your stored procedure inside of Linx, Linx does not yet know what your input parameters would be, thus it calls the Procedure with empty values, in order to get the return structure of the Stored Procedure. In our example then, because all the inputs will be empty strings, that Dynamic SQL string will look like this:

select from where = ‘’

And immediately the error makes sense.

How to fix this?

Again, I do not recommend using Dynamic SQL in this fashion inside of a Stored Procedure. There is a truck-load of reasons why this will give issues. Rather build your processes inside of Linx.

But, sometimes you do not have a choice. In that case, it is simple to fix the issue. Just add a bit of code in front of your Stored Procedure to check whether you’re getting empty values. If that is the case, simply return the structure as a set Select statement. For example:

CREATE PROCEDURE [dbo].[GetSpecificValue]

@TableName VarChar(50) = ‘’,
@FieldName Varchar(50) = ‘’,
@FilterField Varchar(50) = ‘’,
@FilterValue Varchar(50) = ‘’
AS
BEGIN
if @TableName = ‘’
> BEGIN
> select ‘Example’ Column1
> return 0
> END

DECLARE @DSQL nvarchar(max) = ‘select ’ + @FieldName + ’ Column1 from ’ + @TableName + ’ where ’ + @FilterField + ’ = ‘’’ + @FilterValue+‘’‘’;
DECLARE @Return nvarchar(50);
EXECUTE sp_executesql @DSQL, @Return output;

END

Now Linx will be able to get the Structure of the return and you can easily call the SP.