CODEWITHSUNDEEP
c#sql-serverado.netsp-executesql
Pacurar Stefan
Pacurar Stefan

Reputation: 255

SQL Server : sp_executesql parameters not working

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.

Upvotes: 2

Views: 1154

Answers (2)

Pacurar Stefan
Pacurar Stefan

Reputation: 255

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

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 0

Related Questions