What is the difference between EXEC and EXECUTE?
They are the same. Both of them executes stored procedure when called as
EXEC sp_help
GO
EXECUTE sp_help
GO
I have seen enough times developer getting confused between EXEC and EXEC(). EXEC command executes stored procedure where as EXEC() function takes dynamic string as input and executes them.
EXEC('EXEC sp_help')
GO
Another common mistakes I have seen is not using EXEC before stored procedure. It is always good practice to use EXEC before stored procedure name even though SQL Server assumes any command as stored procedure when it does not recognize the first statement. Developer learns while working with Query Editor in SSMS that EXEC is not necessary before running any stored procedure. However, consider following two test and you will see why EXEC or EXECUTE is necessary in many cases and good practice to use it.
TEST 1 : No Errors
USE AdventureWorks;
GO
----Try this first independently this will throw an error
sp_helptext 'dbo.uspPrintError'
GO
----Try this second independently this will work fine
EXEC sp_helptext 'dbo.uspPrintError'
GO
TEST 2 : EXEC prevents error
USE AdventureWorks;
GO
----Try this first independently this will throw an error
SELECT *
FROM Sales.Individual
sp_helptext 'dbo.uspPrintError'
GO
----Try this second independently this will work fine
SELECT *
FROM Sales.Individual
EXEC sp_helptext 'dbo.uspPrintError'
GO
Test 2 indicates that using EXEC or EXECUTE is good practice as it always executes the stored procedure, when not using EXEC can confuse SQL SERVER to misinterpret commands and may create errors.
Reference : Pinal Dave (https://blog.sqlauthority.com)
72 Comments. Leave new
exec MASTER..XP_CMDSHELL ‘Echo Conteudo>>c:\teste.txt’
ALTER PROCEDURE [dbo].[SP_TEMANAGMENT_DELETEDOCUMENT_FROM_REPORTS](@ID VARCHAR(25),@EMPID VARCHAR(20),@TABLENAME VARCHAR(50))
AS
BEGIN
declare @SQL VARCHAR(50)
SET @SQL=’DELETE ‘+@TABLENAME+’ WHERE profile_id=’+@EMPID+’ AND assignment_id= ‘+@ID
EXEC(@SQL)
END
whem i am trying to execute it it is giving error
i tried like this
exec SP_TEMANAGMENT_DELETEDOCUMENT_FROM_REPORTS 400,36,’emp_assignment’
What was the error?
Hi,
i have scheduled the backup of the database and the backup is saved in my local drive C:/backup/,,,,,my problem is that i want the backup to be saved not in the local drive but in the remote computer suppose the IP address of other machine is \\10.189.42.123\c$\TEST_BAK_SQL ,,,and the backup is to be scheduled and be saved in the folder \\10.130.18.123\c$\TEST_BAK_SQL ?????
Thanks in advance….
It is possible as long as you have access to the network drive
@Madhivanan:
It is possible as long as you have access to the network drive
There’s more to it than that. Specifically:
1. The Windows account that the SQL Server service is running under needs access to the remote server and share (and the file system the share provides access to). You can check which account this is via the Services snap-in (services.msc). By default it is the LOCAL SYSTEM user but more secure installations should have a least-privileged local user account instead.
2. Using (built-in) SQL Backup over a network connection is unreliable, at best. Far better to continue backing-up to your local file system followed by an XCOPY (or better, NCOPY, SCP, etc.) to copy or move the .BAK files when the backups have completed.
3. Test your backups occasionally by restoring them to an offline/staging server. You should be doing this anyway, but its even more important after moving your backup files across a network.
Hi
I have a dynamic SQL query returning a single output. Like,
exec(@stmt) where @stmt holds the dynamic query)
if executed it displays the output but i need to capture that in a variable. How can i do?
Sivaganesh,
You can’t return a single scalar value from exec(@stmt), but you can get a result set.
It’s very messy having to go through a temporary table to hold the result set, but you can return a single value by doing something like this:
declare @sqlStr nvarchar(max)
declare @myVar varchar(max)
declare @myTable varchar(max)
declare @totalValue int
set @myTable = ‘dbo.tableName’
set @myVar = ‘columnName’
set @sqlStr = ’select fooBarBaz=sum(‘ + @myVar + ‘) from ‘ + @myTable
–DEBUG: print @sqlStr
declare @tempTable table (fooBarBaz int)
insert @tempTable exec(@sqlStr)
select @totalValue = fooBarBaz from @tempTable
print @totalValue
Read about sp_executesql in SQL Server help file. It has example code
What is the purpose of Execute Keyword.
What I mean, it execute the query and returns something.
Regards
Praveen
The main purpose of EXECUTE is to execute the procedure and dynamic SQL
hello mates, I need help with this code can not get to run this code in the database has no problems with a select, but when put on a wiew create, get the error “‘CREATE VIEW’ must be the first statement in a batch of queries. “Could help me please
DECLARE @Nom_Cliente VARCHAR (50)
DECLARE @Execu nVARCHAR (4000)
DECLARE Cursor_Cambios CURSOR FOR
SELECT name
FROM master..sysdatabases
where name like ‘INTEG_%’
OPEN Cursor_Cambios
FETCH NEXT FROM Cursor_Cambios
INTO @Nom_Cliente
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Execu= ‘CREATE VIEW [dbo].[vPersona_Relacion]
AS
SELECT DISTINCT
dbo.Persona.Numero_Empresa, dbo.Persona.Numero, dbo.Persona.General, dbo.Persona.Nombre_Completo, dbo.Persona.Paterno,
dbo.Persona.Materno, dbo.Persona.Nombre, dbo.Persona.Nombre_Corto, dbo.Persona.RFC, dbo.Persona.CURP, dbo.Persona.Personalidad_juridica,
dbo.Persona.Telefono, dbo.Persona.Fax, dbo.Persona.Celular, dbo.Persona.correo_electronico, dbo.Persona.Numero_Confia, dbo.Persona.Fecha_Alta,
dbo.Persona.Fecha_Cambio, dbo.vDomicilio.Desc_Tipo, dbo.vDomicilio.Domicilio, dbo.vDomicilio.Colonia, dbo.vDomicilio.Delegacion_municipio,
dbo.vDomicilio.Estado, dbo.vDomicilio.Codigo_postal, dbo.vDomicilio.Telefono AS Telefono_Domicilio, dbo.vDomicilio.Fax AS Fax_Domicilio,
dbo.fn_Obten_Tipos_Persona(dbo.Persona.Numero_Empresa, dbo.Persona.Numero) AS ucaseDes_Tipos_Persona
FROM dbo.vDomicilio RIGHT OUTER JOIN
dbo.Persona ON dbo.vDomicilio.Numero_Persona = dbo.Persona.Numero’
set @Execu= ‘USE [‘+ @Nom_Cliente +’]‘+ char(13)+’go’ + char(13)+@Execu
–print @Execu
exec ( @Execu)
FETCH NEXT FROM Cursor_Cambios INTO @Nom_Cliente
END
CLOSE Cursor_Cambios
DEALLOCATE Cursor_Cambios
Fulgore,
The error message says it all, “CREATE VIEW must be the first statement in a batch…” If you remove the “USE [database] GO” from the start of your statement it should work fine.
However, in this case, it looks as though you’re trying to create these views in other databases, which probably isn’t going to work (unless the currently executing user is SA-equivalent). Changing the create statement to ‘CREATE VIEW [Database].[dbo].[vPersona_Relacion]’ may work. Otherwise, you may need to look into other solutions such as OPENQUERY, OPENROWSET or perhaps even a .NET Assembly.
Good luck,
AlwaysLearning
hello AlwaysLearning
if you’re right if I delete the use [] there is no problem, but actually want to do that view in all databases on the server, I have unprivileged SA but still does not work: (I try the solution ‘CREATE VIEW [Database ]. [dbo]. [vPersona_Relacion] sends me an error but”CREATE / ALTER VIEW can not specify the name of the database prefix the name of the object.’ll try the recommended solutions ‘OPENQUERY, OPENROWSET’ thanks for the help
good day
Fulgore2099
pd: sorry my English is not very good XD
Fulgore,
I have a better answer for this now. In Microsoft SQL land the create function/procedure/view statements are all limited to the current database – create table is the only exception and this is considered to be a hack for temp tables. Even using OPENROWSET is unable to work around this.
To create views in other databases you’re going to be stuck with two options: (1) create a .NET assembly that does the work for you using input parameters such as the remote database name and the create view SQL, or (2) use xp_cmdshell to launch and external process that does the same.
xp_cmdshell has security issues, and needs to be specifically enabled on the SQL server, but here is an example to do what you need…
use tempdb
go
declare @Database nvarchar(4000) = ‘TargetDatabase’
declare @SQL nvarchar(4000)
set @SQL = ‘CREATE VIEW [dbo].[vPersona_Relacion] as select [Foo]=1, [Bar]=2’
declare @Cmd nvarchar(4000)
set @Cmd =
‘sqlcmd.exe’
+’ -S(local) -E’ — Local server (may need an CLICONFG alias)
+’ -E’ — Trusted connection (SQL Service account in this case)
+’ -d”‘ + @Database + ‘”‘
+’ -Q”‘ + @SQL + ‘”‘ — Note: -Q exits, -q does not, use -Q
print @Cmd
exec xp_cmdshell @Cmd, no_output
go
select * from [TargetDatabase].[dbo].[vPersona_Relacion]
go
/*
use TargetDatabase
drop view [dbo].[vPersona_Relacion]
go
*/
Good luck,
AlwaysLearning
I WANT TO EXECUTE THIS QUERY:
–update ac001part001 set dob=null where dob like –‘%xx/xx/%’ or dob like ‘%XX/XX%’
THERE ARE 100 TABLES WHICH IS NAMED AS AC001PART001,AC001PART002….AND AC001PART093
– I CAN ABLE TO EXECUTE ABOVE QUERY IF I CHANGED THE TABLE NAME ONE BY ONE…BUT I WANT TO MAKE POSSIBLE THIS QUERIES FOR 1 TO 100 TABLES WITHIN ONE SQL SYNTAX..
–========================================
DECLARE @intFlag INT;
declare @str1 nvarchar(100);
DECLARE @qry nvarchar(2000);
SET @intFlag = 5;
set @str1=”;
WHILE (@intFlag <=93)
BEGIN
PRINT @intFlag;
select @str1 = 'ac001part' + convert(nvarchar(10),CAST(REPLICATE(0,3-LEN(@intflag)) AS VARCHAR(3)) + CAST(@intflag AS VARCHAR(3)));
print @str1;
set @qry = 'update ' + @str1 + ' set dob= ' + NULL + ' where dob like ''%XX/XX/%'' or dob like ''%xx/xx/%''';
execute sp_executesql @qry ;
print @qry;
SET @intFlag = @intFlag + 1;
END
GO
–========================================
PLEASE HELP ME IMMEDIATELY AS SOON AS POSSIBLE…AS I SPOILED MY HALF OF THE DAY TO SOLVE THIS..ABOVE SQL SYNTAX IS CORRECT..NO ERROR..BUT IT'S OUTPUT NOT AFFECTS THE ROW…
Hi Lata,
You’re concatenating a string with NULL and getting a NULL result. i.e.: @qry is NULL.
Instead of:
set @qry = ‘update ‘ + @str1 + ‘ set dob= ‘ + NULL + ‘ where dob like ”%XX/XX/%” or dob like ”%xx/xx/%”’;
You probably want:
set @qry = ‘update ‘ + @str1 + ‘ set dob=NULL where dob like ”%XX/XX/%” or dob like ”%xx/xx/%”’;
Also, unless your database is running with a case-sensitive collation, you could probably shorten it to this:
set @qry = ‘update ‘ + @str1 + ‘ set dob=NULL where dob like ”%XX/XX/%”’;
Good luck,
AlwaysLearning
Hi All,
I have a situation –
I have a program which extracts correspondence data of approx 7500 clients. It takes id of first 750 client and calls a stored procedure passing those 750 ids.
In that Stored Proc data is extracted from 2 tables using Union All.
Now I have a task to speed up the process.
One thing that I have in my mind is to store all the 7500 ids in a table and then call stored proc and extracting the data based on ids stored in that table.
Any other work around or suggestion is highly appreciated.
Thanks
Puneet
Is it possible to use the dynamic sql statement as a data set to be joined with other tables
i.e.
declare @vvSQL as varchar(max)
set @vvSQL = ‘select * from table_name’
select * from (exec (@vvSQL)) a, table_name_b b
where a.id = b.id
I want something similar to the above and do not want to create a temp table as the number of column would be different everytime.
Can You suggest some solution??
Move the dynamic result to a temp table and join with that table
How can i, write a “IF” statment and inside of it run “EXEC”?
SET @Query = ‘SELECT * FROM sys.columns WHERE Name = ”CombineConnectTime” AND Object_ID = Object_ID(”’+@OurTableMirorName+”’)’
IF EXISTS(EXEC(@Query))
BEGIN
….
END
thanks
Have you considered using sp_executesql?
declare @Column nvarchar(max) = ‘CombineConnectTime’
declare @Table nvarchar(max) = ‘Foo’
declare @Exists int
declare @SQLString nvarchar(max) = N’select @e=1 from sys.columns where name=@c and object_id=object_id(@t)’
declare @ParmDefinition nvarchar(max) = N’@e int output, @c nvarchar(max), @t nvarchar(max)’
— Test existance
create table dbo.Foo (
CombineConnectTime datetime
)
set @Exists = 0
execute sp_executesql @SQLString, @ParmDefinition, @e=@Exists output, @c=@Column, @t=@Table
if (@Exists = 1)
begin
print ‘Exists’
end
else
begin
print ‘Doesn”t exist’
end
— Test nonexistance
drop table dbo.Foo
set @Exists = 0
execute sp_executesql @SQLString, @ParmDefinition, @e=@Exists output, @c=@Column, @t=@Table
if (@Exists = 1)
begin
print ‘Exists’
end
else
begin
print ‘Doesn”t exist’
end
While doing a conversion, I’m using a cursor to move data into an intermediate table (that’s a given). That tablename is NOW required to be variable, for sending to different receivers. Since that isn’t possible in a direct sense, I’m considering building the insert dynamically, but I’m not sure how to get the variables into the EXEC clause.
Basically, my starting point is:
ALTER PROCEDURE [dbo].x AS
DECLARE @i1 INT, @i2 INT, @c1 CHAR, @c2 CHAR, @v1 VARCHAR(20),….
Declare @xcursor CURSOR
Set @xcursor = cursor for
SELECT yada FROM wada
Open @xcursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM @policy_cursor INTO @c1,@i1,@v1,@i2,@c2,…
IF @@FETCH_STATUS = 0
BEGIN
{yadayada operations on data}
INSERT INTO @VARIABLE-TABLE-NAME –actually just the database
VALUES (dozens of variables and constants)
END END
So, while I can’t use @VARIABLE-TABLE-NAME directly, I know I can create an INSERT statement dynamically and EXECUTE it. The problem is passing in all the variables. Is there a way to do it within the query? Even if I created another external procedure, I’d be back to the same problem of making the tablename variable.
Thanks
Go the RBAR (Row By Agonizing Row). Do you have sufficient access to create views on the fly? You could create a view for the destination table and eliminate the cursor as well…
— Create some test data
set nocount on
if object_id(‘[dbo].[srcTable]’, N’U’) is not null
drop table [dbo].[srcTable]
create table [dbo].[srcTable] (
[id] int not null primary key identity
, [name] nvarchar(50) not null
, [value] int not null
)
declare @counter int = 0
while (@counter < 1000)
begin
insert [dbo].[srcTable] ([name], [value]) select newid(), floor(rand() * 1000)
set @counter = 1 + @counter
end
if object_id('[dbo].[dstTable]', N'U') is not null
drop table [dbo].[dstTable]
create table [dbo].[dstTable] (
[id] int not null primary key –NO: identity
, [name] nvarchar(100) not null
, [value] decimal(18,2) not null
)
— This could be a stored proc parameter…
declare @tableName nvarchar(255) = '[dbo].[dstTable]'
— Separate the DROP and CREATE statements because sp_executesql can't handle batches.
declare @sql nvarchar(max) =
'if object_id(''[dbo].[targetView]'', N''V'') is not null
drop view [dbo].[targetView]'
exec sp_executesql @sql
set @sql =
'create view [dbo].[targetView] as
select id, name, value from '+@tableName
exec sp_executesql @sql
— Now do your conversions on the view…
set nocount off
insert [dbo].[targetView] (id, name, value)
select [id], [name], 33.3 * [value]
from [dbo].[srcTable]
select * from [dbo].[srcTable]
select * from [dbo].[dstTable]
select * from [dbo].[targetView]
/*
— Clean up
drop view [dbo].[targetView]
drop table [dbo].[dstTable]
drop table [dbo].[srcTable]
*/
Can i Use a Exec Function through Stored procedure Parameters???
Conversion failed when converting the varchar value ‘and c.man_code in(01,02)’ to data type int.
According to https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017 both EXEC and EXECUTE behave the same and both can take string literals and variables, what causes you to say that there is a difference?
Declare @CPQuery nvarchar(max)
Declare @NVTableName nvarchar(255)
Declare @NVFeederId nvarchar(255)
Set @CPQuery=’ ‘
@CPQuery= N ‘ Declare cpid_cursor cursor for select ‘+ @NVIndexColName+’ as id from ‘+@NVTableName+’ with (no lock) where feederid=’+@NVFeederId
Fetch from cpid_cursor
While @@FetchStatus
Execute sp_executeSQL @CPQuery
Sir this query doesnot know compiler that cpid_cursor is created when execute
please help me immediately
Everything should be part of a dynamic sql
good one thanks.
Hi Pinal,
Which one is better for UDFs, e.g. GetCampArea –
EXECUTE @var = dbo.GetCampArea @WardNum;
OR
SET @var = dbo.GetCampArea (@WardNum);
and why?
Thanks,
Gaurav
Hi everyone ..
here is the output i am getting after executing my procedure but i want to run this at run time ..
can you guys help to execute during Run time ..
select stg.Name,tgt.Name,+ (case when stg.Name=tgt.Name then ‘true’ else ‘false’ end ) Status ,stg.Designation,tgt.Designation,+ (case when stg.Designation=tgt.Designation then ‘true’ else ‘false’ end ) Status ,stg.joiningDate,tgt.joiningDate,+ (case when stg.joiningDate=tgt.joiningDate then ‘true’ else ‘false’ end ) Status ,stg.sex,tgt.sex1,+ (case when stg.sex=tgt.sex1 then ‘true’ else ‘false’ end ) Status ,stg.test,tgt.test,+ (case when stg.test=tgt.test then ‘true’ else ‘false’ end ) Status from @SourceTab stg inner join @TargetTab tgt on stg.ID=tgt.ID
Hi..
Please give me the solution for this..
DECLARE @SQL nVARCHAR(max)
SET @SQL = ‘
DECLARE @TEMPENT TABLE (ID INT IDENTITY(1,1) ,ENTITY_ID BIGINT) ….
‘
This is how my dynamic sql is. But while executing it throws error like “Unclosed quotation mark after the character string ‘ = ‘) ”
Please help me. Thanks in advance.