CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
Sako73
Sako73

Reputation: 10137

SQL Server's "sp_executesql" is not recognizing parameters

I have the following code:

create procedure my_sp_name_here
    @param1 int,
    @param2 nvarchar(128)
as
begin
    ...<sql here>...;
end

declare @paramDef nvarchar(500) = N'@param1 int, @param2 nvarchar(128)';
execute sp_executesql my_sp_name_here, @paramDef, @param1=1, @param2=N'p2';

I keep getting an error:

Procedure or function 'my_sp_name_here' expects parameter @param1, which was not supplied.

I have tried several different configurations and searched, but I haven't found an answer. Any help would be appreciated.

Upvotes: 1

Views: 2157

Answers (2)

Andomar
Andomar

Reputation: 238078

In T-SQL, exec is a built-in function that can run a SQL statement or call pretty much any type of function or procedure. It does not accept parameters for a SQL statement, only for a stored procedure.

The stored procedure sp_executesql does support SQL statements with parameters. The first argument to sp_executesql is a SQL statement. But you're using a stored procedure name as the first argument:

execute sp_executesql my_sp_name_here, @paramDef, @param1=1, @param2=N'p2';

That's not ok! To call a stored procedure using sp_executesql, you could:

exec sp_executesql 
    N'exec my_sp_name_here @param1, @param2', 
    N'@param1 int, @param2 nvarchar(128)',
    @param1 = 42,
    @param2 = 'Answer to The Ultimate Question of Life, the Universe, and Everything';

But it's much easier to call a stored procedure using exec:

exec my_sp_name_here 42, '6*7';

You don't have have to supply the parameter names to exec, it will accept them in order.

Upvotes: 6

Jahirul Islam Bhuiyan
Jahirul Islam Bhuiyan

Reputation: 799

write 

exec my_sp_name_here @param1=1, @param2=N'p2';

instead of 

execute sp_executesql my_sp_name_here, @paramDef, @param1=1, @param2=N'p2';

Upvotes: 2

Related Questions