exec versus exec sp_executesql

Question

exec versus exec sp_executesql

Tallboy

SSCrazy

Points: 2457
More actions
January 25, 2016 at 6:06 pm

#307288

Hi,
I have to call a stored procedure from within another procedure.
I have to pass the proc 2 parameters
@ClientID int,
@StartDate date
But I dont know which is the correct syntax for calling the sp and passing the parameters.
At the moment I just do
EXEC myProc, @ClientID, @StartDate and it seems to work OK.
I have also tried
EXEC sp_executesql myProc, @ClientID, @StartDate --which doesn't work!
I have also seen examples like
EXEC sp_executesql myProc, N'@ClientID int , @StartDate date, @p1, @p2
Which doesn't make much sense to ,e at the moment.
Can anyone offer any explanation as to which is best use and why and in particular if I need to use N' etc in the third example?
Thanking you

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Tallboy (1/25/2016)
Hi,
I have to call a stored procedure from within another procedure.
I have to pass the proc 2 parameters
@ClientID int,
@StartDate date
But I dont know which is the correct syntax for calling the sp and passing the parameters.
At the moment I just do
EXEC myProc, @ClientID, @StartDate and it seems to work OK.
I have also tried
EXEC sp_executesql myProc, @ClientID, @StartDate --which doesn't work!
I have also seen examples like
EXEC sp_executesql myProc, N'@ClientID int , @StartDate date, @p1, @p2
Which doesn't make much sense to ,e at the moment.
Can anyone offer any explanation as to which is best use and why and in particular if I need to use N' etc in the third example?
Thanking you

You do not need to use dynamic SQL to call a stored proc. Try it this way:

DECLARE @p1 int,

@p1 date;

SELECT @p1 = 1,

@p2 = '1/25/2016';

EXEC myProc

@ClientID = @p1,

@StartDate = @p2;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

read Books Online to understand about sp_executesql.

Just call a sproc with EXEC

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service