9

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

Improve this question
5
  • 2
    Always run a stored procedure with EXEC spName if possible, the performance comments only relate to the EXEC <SQL string here> usage of EXEC - i.e. if you built an SQL statement into a string or you had a procedure name in a variable. Commented Jul 26, 2016 at 15:47
  • 1
    And also note you still have ’EXEC` anyway before sp_executesql as you are calling that stored procedure. Commented Jul 26, 2016 at 15:55
  • Can you add the link to the article to your question, it sounds highly unlikely that in any circumstance sp_executesql would be faster way to execute procedures. Commented Jul 26, 2016 at 15:58
  • @JamesZ msdn.microsoft.com/en-us/library/ms175170.aspx, but as Martin Smith explained it is faster only if you built an SQL statement into a string Commented Jul 26, 2016 at 16:06
  • Yes the article only means the re-use of query plans -- and that only applies for dynamic SQL Commented Jul 26, 2016 at 17:22

2 Answers 2

Reset to default
16

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

The sad thing is that there is not error/warning info for this kind of "mistakes" @SupplierId=@SupplierId
3

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.