Stored Procedure "Parameter not supplied"

Question

I posted this with ASP.NET specific tags, but I think that was asking too broad a question, so I'm reposting the SQL part here more specifically. I have two ways of calling a procedure with output using T-SQL. The first is working fine with expected return values:

declare @p5 int
execute dbo.aspCreateQuote @customerID=13,@itemList='text goes here',@quoteID =@p5 output;
select @p5

The second is called using sp_executesql and does not work:

declare @p5 int
set @p5=NULL
exec sp_executesql N'psa.dbo.aspCreateQuote',N'@customerID int,@itemList varchar(1000),@quoteID int output'
,@customerID=13
,@itemList='[xml block of text goes here]',
@quoteID=@p5 output
select @p5
go

It gives the error that the procedure:

expects parameter '@customerID', which was not supplied.

But why would there be any difference between the two calls? The second script is generated by an ASP.NET web app.

Thank you in advance

I don't inderstand the need for dynamic SQL here, there is nothing dynamic; think about what is going on in the context of the SQL being executed by sp_executesql - you are invoking aspCreateQuote and not passing it any parameters, hence the error. — Stu, Jan 17 '22 at 21:09
@Stu - they probably aren't calling `sp_executesql` themselves. That is how a parameterised query from `ADO.NET` with command type text will show up — Martin Smith, Jan 17 '22 at 21:10
@MartinSmith thanks makes sense, presumably the appropriate ado *CommandType* fixes the syntax. — Stu, Jan 17 '22 at 21:13
yep - because then it will just call the `aspCreateQuote ` stored proc directly and pass the params to that — Martin Smith, Jan 17 '22 at 21:13
This is a duplicate of your previous question. Please do not do that. — Dale K, Jan 17 '22 at 21:54

Martin Smith · Answer 1 · 2022-01-17T21:19:47.070

The second one passes the parameters into sys.sp_executesql but then they never get used inside there and passed to the call to aspCreateQuote .

For that to happen the SQL Statement you are executing would need to be something like

N'EXEC psa.dbo.aspCreateQuote @customerID, @itemList, @quoteID output'

not just

N'psa.dbo.aspCreateQuote'

But probably you just forgot to set the command type to CommandType.StoredProcedure in the web app anyway and so just need to fix that. (See this answer for some example syntax that sets the CommandType)

Stored Procedure "Parameter not supplied"

1 Answers1