CODEWITHSUNDEEP
sql-server-2005
Rico
Rico

Reputation: 1298

Incorrect syntax error using sp_executesql

Here is the statement:

exec sp_executesql N'CRM_ValidateUser @p0, @p1, @p2',
    N'@p0 nvarchar(5),@p1 nvarchar(4),@p2 int',@p0=N'login',@p1=N'pass',@p2=15

Why am I getting this error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CRM_ValidateUser'.

Upvotes: 1

Views: 883

Answers (2)

Umut Derbentoğlu
Umut Derbentoğlu

Reputation: 1196

DECLARE @p0 NVARCHAR(5);
DECLARE @p1 NVARCHAR(4);
DECLARE @p2 INT;

SET @p0 =N'login';

SET @p1 = N'pass';

SET @p2 = 15;
EXECUTE sp_executesql N'CRM_ValidateUser', @p0, @p1, @p2;

Upvotes: -1

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

You need to say EXEC in case any other commands are transmitted as part of the batch. In this case, it will be parameter definitions, so the batch actually sent back into SQL Server will be something like:

DECLARE @p0 NVARCHAR(5);
...
CRM_ValidateUser ...

And this is why your error message occurs. EXEC can only be omitted if the stored procedure call is the first statement in the batch, and IMHO should never be excluded anyway. Try:

EXEC sp_executesql N'EXEC CRM_ValidateUser...

Also a good idea to specify schema prefix.

Upvotes: 4

Related Questions