Execute stored procedure that has parameters with sp_executesql

Question

I have a simple stored procedure that has parameter

CREATE Procedure GetSupplierForTesting
    (@SupplierId INT)
AS
    SELECT SuppLabel 
    FROM Supplier 
    WHERE Supplier.SupplierId = @SupplierId

I am able to call it with the exec command inside another stored procedure like this

exec GetSupplierForTesting @SupplierId = 10

I came across an article that explains how sp_executesql is faster than exec. My problem is that I don't know how to call a stored procedure that has parameters with sp_executesql. I have tried this code

DECLARE @SupplierId INT = 10;
EXEC sp_executesql N'GetSupplierForTesting', N'@SupplierId INT', @SupplierId

but I am getting an error:

Procedure or function 'GetSupplierForTesting' expects parameter '@SupplierId', which was not supplied

Answer 1

The syntax you would need would be

DECLARE @SupplierId INT = 10;

EXEC sys.sp_executesql N'GetSupplierForTesting @SupplierId=@SupplierId', 
                       N'@SupplierId INT', 
                         @SupplierId=@SupplierId

But don't do this. It is utterly pointless. There is no magic performance increase to be expected from using sp_executesql to basically wrap the same exec statement and execute it at a different scope.

Just use

exec dbo.GetSupplierForTesting @SupplierId = 10

Answer 2

1

Performance issue is based on assumption that when you are using non-parametrized ad-hoc query, it (the string representing this query) will be different every time - because specific argument values are parts of query text.

Whilst parametrized query keeps it's body unchanged because in place of where ... and title="asdf" you have where ... and title = @title. Only contents of variable @title change. But query text persists and sql server realizes that there is no need to recompile it.

Non-parametrized query will be recompiled every time you change values used in it.

2

You are getting exception because your script does not pass any arguments to the stored proc.

Your script is: 'GetSupplierForTesting' - that's it.

By passing arguments to sp_executesql you are passing them to the scipt. Not to the sp used in script, but to the script itself. E.g.:

exec sp_executesql N'print @val', N'@val int', @val = 1

this script does utilize variable @val. Your - does not. It just contains name of the proc. So your script corrected should look like

exec sp_executesql
    N'exec GetSupplierForTesting @Supplier = @Supplier_id_value', 
    N'@Supplier_id_value int',
    @Supplier_id_value = 10

1. script contains code calling your sp and this code passes argument to sp with value taken from @Supplier_id_value variable
2. @Supplier_id_value is declared as int for internals of this script
3. value of 10 is passed to the argument of the script

3

Performance issue you are talking about is about ad-hocs, not SPs.

Another face of this issue is parameter sniffing problem. Sometimes with specific param values your script or SP should use another execution plan, different from the plan it used for previously passed param values.

Every time recompiled ("slowly executed") ad-hoc would be (re)compiled for sure and probably would get better execution plan while SP or parametrized query would probably not be recompiled and would use worse, less optimal execution plan and would finally perform much slower than "slow because of recompilation" ad-hoc query.

There are no "write this - and it will work slowly", "write that - and it will hurtle like a rocket" rules in sql. It all depends on many factors. Sometimes one would probably need specifically ad-hocs, sometimes - should avoid them totally.