error while executing sp_executesql
-
Hi,
I am getting an error while executing the following script
--START OF SCRIPT
use pubs
go
if object_id('spGetAuthorsList') is not null drop proc spGetAuthorsList
go
create proc spGetAuthorsList
@author_id varchar(10),
@FindExact int
As
SET NOCOUNT OFF
if @FindExact = 1
select * from authors where au_id = @author_id
else
select * from authors where au_id like @author_id + '%'
return @@RowCount
go
Execute spGetAuthorsList '1' , 0
go
Execute sp_executesql N'spGetAuthorsList ''1'' , 0'
go
Execute sp_executesql N'spGetAuthorsList' , N'@author_id varchar(10), @FindExact bit' , @author_id = '1' , @FindExact = 1
go
--END OF SCRIPT
The statements in green color are running fine whereas the equivalent statement in red is giving following error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'spGetAuthorsList'.
Any input??
-
There is a small update.
Please consider the data type of @FindExact is bit in stored procedure definition.
By mistake i typed it as int.
-
Hi,
Provided you are not trying to return a value to the executing sproc you need to do this:-
Execute sp_executesql N'spGetAuthorsList ''' + @author_id + ''', ' + CAST(@FindExact as varchar(6))
Or
DECLARE @SQL nvarchar(4000)
SET @sql = ('spGetAuthorsList ''' + @author_id + ''', ' + CAST(@FindExact as varchar(6)))
Execute sp_executesql @sql
You need to make the variables part of the string you plan to execute, not try to add them at execution time
Hope this helps
Have fun
Steve
We need men who can dream of things that never were.
-
try the following:
Execute sp_executesql N'exec spGetAuthorsList @author_id, @FindExact' , N'@author_id varchar(10), @FindExact bit' , @author_id = '1' , @FindExact = 0
In your original statement you are passing the variable (@author_id = '1' , @FindExact = 0) to the "sp_executesql" stored procedure and NOT to your "spGetAuthorsList".
Moreover, if you omit exec:
Execute sp_executesql N' spGetAuthorsList @author_id, @FindExact' , N'@author_id varchar(10), @FindExact bit' , @author_id = '1' , @FindExact = 0
you'll obtain the same syntax error!!
Sql server converts the sp_executesql statement to something like this:
declare @author_id varchar(10)
declare @FindExact bit
spGetAuthorsList @author_id, @FindExact
and this will return the same syntax error (exec is required if the execution of a stored procedure is NOT the first statement in a batch)
stefano
-
Sorry Stefano,
we appear to have been typing at the same time.
This will never work matey:-
Execute sp_executesql N'exec spGetAuthorsList @author_id, @FindExact' , N'@author_id varchar(10), @FindExact bit' , @author_id = '1' , @FindExact = 0
As I said, the contents of the variables need to become part of the sql string to be executed (see my post above), not the variable names (e.g. @FindExact) themselves - as these will not be available when exec creates a new spid.
Have fun
Steve
We need men who can dream of things that never were.
-
Hi steve!
I tried. It works.
I think that sql server generates something like this:
declare @author_id varchar(10)
declare @FindExact bit
exec spGetAuthorsList @author_id, @FindExact
when you execute:
Execute sp_executesql N'exec spGetAuthorsList @author_id, @FindExact' , N'@author_id varchar(10), @FindExact bit' , @author_id = '1' , @FindExact = 0
and then it's able to replace the internal vaiable values with the external variables values based on their name
stefano
-
Thanks stefano and steve for your inputs.
Ishwar
-
No worries.
Stefano,
Yep it does work, iv'e never seen it used like that before - it must populate the variables before executing the sp_executesql line....
I have used sp_executesql in a similar fashion to output variables back to the calling sproc but never populated them on the calling line.
Thats what we are here for - to learn something new every day
Thanks for the reply.
Have fun
Steve
We need men who can dream of things that never were.
-
The statement which was giving error was actually generated by the SQLClient of the .NET Framework when i tried to call this stored proc from a windows form.
-
Just wondering why you would use sp_executesql anyway?
Couldn't just run,
exec spGetAuthorsList @author_id=1, @FindExact=0
--------------------
Colt 45 - the original point and click interface
-
This is what i did, that is i called the stored proc using the following syntax. (You should know the ADO.NET in order to understand the following code.)
Command.CommandText = "spGetAuthorList";
Command.Parameters.Add("@author_id", "AL");
Command.Parameters.Add("@FindExact" , 1);
Command.ExecuteReader();
Now after calling ExecuteReader() function the SQLClient component of ADO.NET generates the errorneous sp_executesql statement behind the scenes.
I trapped this errorneous sp_executesql statement using the SQL Profiler.
-
Ok, the reason you would have got the sp_executesql is because you haven't set the command type to StoredProcedure. I think by default the command type would just be text which would change the way the command text is interpreted and executed.
--------------------
Colt 45 - the original point and click interface
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply