SQL Server : sp_executesql parameters not working

Question

I have a VALID sp_executesql code, generated from C# ADO.NET, but the parameters are not passed to the stored procedure (SQL Server issue).

This is what I found with SQL Profiler:

declare @p3 StockSyncType

insert into @p3 values(3, 17594, 73471, 20, 5, 100, N'', N'', N'', N'')
insert into @p3 values(3, 17593, 73470, 20, 5, 100, N'', N'', N'', N'')

exec sp_executesql N'EXECUTE [dbo].[sp_SyncInventory] ',N'@Details [dbo].
[StockSyncType] READONLY',@Details=@p3

Here you can find the table type and stored procedure:

CREATE TYPE [dbo].[StockSyncType] AS TABLE(
    [OperationTypeId] [int] NOT NULL,
    [Product_ID] [int] NOT NULL,
    [ProductAttribute_ID] [int] NOT NULL,
    [Location_ID] [int] NOT NULL,
    [StockType_ID] [int] NOT NULL,
    [Quantity] [decimal](18, 0) NOT NULL,
    [RowOrIsle] [nvarchar](10) NULL,
    [Bay] [nvarchar](10) NULL,
    [Shelf] [nvarchar](10) NULL,
    [Bin] [nvarchar](55) NULL
 )
GO

CREATE PROCEDURE [dbo].[sp_SyncInventory]   
    @Details StockSyncType READONLY
AS
BEGIN
    SELECT * 
    FROM @Details
END

Please help ?! I don.t understand why no error is raised running the sp_executesql, but the parameters are not sent.

The first argument should read `N'EXECUTE [dbo].[sp_SyncInventory] @Details'` if thats not what you see in a Profiler trace from some C# code you need to share the C# code. — Alex K., Apr 18 '17 at 16:52
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Apr 18 '17 at 18:36

score 2 · Answer 1 · answered Apr 19 '17 at 01:13

Thanks Alex K. the issue is really in C#, but I find very strange that SQL doesn't signal the error.

The correct call is(bold is the missing part):

exec sp_executesql N'EXECUTE [dbo].[sp_SyncInventory] @Details',N'@Details [dbo].[StockSyncType] READONLY',@Details=@p3

Sean Lange · Answer 2 · 2017-04-18T16:48:37.437

0

You don't need dynamic sql here at all. Just call your procedure.

declare @p3 StockSyncType
insert into @p3 values(3,17594,73471,20,5,100,N'',N'',N'',N'')
insert into @p3 values(3,17593,73470,20,5,100,N'',N'',N'',N'')

EXECUTE [dbo].[sp_SyncInventory] @p3

I would caution you that using sp_ as a prefix is not a good idea. I prefer no prefix at all as they tend to just be noise that make coding more difficult. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix

edited Apr 18 '17 at 16:48

answered Apr 18 '17 at 16:42

Sean Lange

33,028
3
25
40

Please read the post more carefully, I mentioned that the code is "generated from C# ADO.NET", and this means I cannot call it how I want. Thanks – Pacurar Stefan Apr 19 '17 at 00:59
@PacurarStefan can you share the c# code so we aren't guessing and making assumptions? – Sean Lange Apr 19 '17 at 13:07

SQL Server : sp_executesql parameters not working

2 Answers2