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 (http://blog.SQLAuthority.com)
pinaldave
Hi,
I have 1 question
which will perform better as a performance issue,
suppose Sp_SomeProcedure have a same query as
Select * from SomeTable
who will perform better
EXEC Sp_SomeProcedure
and
EXEC (’Select * from SomeTable’)
This is an interview question
Please reply,
Thanks in Advaced..!!!
Kalpesh
Hi
I am having a slightly different problem using Exec().
I am trying to use Exec in a procedure to execute an SQL statement that is built dynamically, and I would like to save the results of the query to a variable.
For Example:
Declare @sqlStr NVARCHAR(MAX)
Declare @myVar varchar(100)
Declare @myTable varchar(100)
Declare @totalValue INT
.
.
.
SET @sqlStr = ‘Select @totalValue=SUM(’ + @myVar + ‘) from ‘ + @myTable
Exec(N’ SET NOCOUNT ON’ + @sqlStr)
—————————————————————————
The Exec in this case gives me the following error:
Must declare the scalar variable “@totalValue”.
Are there any work arounds for this?
RE: Dipti
>.
>SET @sqlStr = ‘Select @totalValue=SUM(’ + @myVar + ‘) >from ‘ + @myTable
>
>Exec(N’ SET NOCOUNT ON’ + @sqlStr)
>
>—————————————————————————
>
>The Exec in this case gives me the following error:
>Must declare the scalar variable “@totalValue”.
>
>Are there any work arounds for this?
Try inserting a space between ON and ‘
This is not correct explanation
Exec & execute both are same. This is feature of SQL that we can write first four characters of some command like
Procedure-> proc
Transaction->Tran
Execute-> Exec
…….
…
..
.
Regards
Dilip
what if some parameters in stored procedures can be null and we want to ignore them while calling that stored proc using ‘exec’ ?
>SET @sqlStr = ‘Select @totalValue=SUM(’ + @myVar + ‘) from ‘ + @myTable
>Exec(N’ SET NOCOUNT ON’ + @sqlStr)
Adding space works, but better to use “;” as a statement separator . It serves similar to “GO” statement.
Example: Exec(N’ SET NOCOUNT ON; ’ + @sqlStr)
Why this doesnot work?
EXEC (’Use pubs’)
The command is successfull but the database setting didnot change when I execute in Query Analyzer?
>SET @sqlStr = ‘Select @totalValue=SUM(’ + @myVar + ‘) from ‘ + @myTable
>
>Exec(N’ SET NOCOUNT ON’ + @sqlStr)
If you are going to use any variables inside EXEC() you need to declare them inside EXEC().
>EXEC (’Use pubs’)
That command would change the database within the EXEC() command. Any additional commands would use the pubs database.
Basically the command executed within EXEC() has its own context.
Hi,
How to use a GO Statement in a dynamic SQL Query.
declare @strsql varchar(max)
SELECT @strsql = ‘ Stmt1
GO
Stmt2
GO’
EXECUTE (@strsql)
This gives an error : Incorrect syntax near GO.
Regards,
Gurudatt
Ideally,
You should not need GO Statement in EXECUTE.
If you need something like that use semi-comma.
Regards,
Pinal Dave ( http://blog.sqlauthority.com )
Hi guys,
I have one problem regarding exec.
Can anyone tell me that how to use exec() inside the function or procedure.
Thanx in advance.
Hi,
How can I execute a stored procedure stored on instance A on instance B? The Variable is a database name of instance B.
Can somebody help me?
Thx
Hi,
how to fill SQL varaible if using a statement inside in EXEC command line eg.
DECALRE @Value bigint
SET @Value = EXEC (’SELECT COUNT(ID) FROM Table’)
SELECT @Value
Is this is possible in this way to capture return value from concatenated statement…or there is other way to do it.
in short what i want is to fill up a variable by return value from exec statement and not from simple select query statement.
thanks if u help me…
ALTER PROCEDURE [dbo].[GetByWhereClause]
@tablename varchar(50),
@whereclause varchar(max)
AS
BEGIN
DECLARE @sqlquery varchar(5000)
SET @sqlquery = ‘SELECT * FROM ‘+@tablename+’ WHERE 1=1 ‘+@whereclause
EXEC(@sqlquery)
END
Suppose,
@tablename = “BuyerMaster”
@whereclause = ” And username=admin”.
It give an Error “Invalid column name admin”
How can I put admin in single quotes as I am getting the values from ASP.NET page.
Please Help Me,
Please.
Thanking You,
Rahul
ALTER PROCEDURE [dbo].[GetByWhereClause]
@tablename varchar(50),
@whereclause varchar(max)
AS
BEGIN
DECLARE @sqlquery varchar(5000)
SET @sqlquery = ‘SELECT * FROM ‘+@tablename+’ WHERE 1=1 ‘+@whereclause
EXEC(@sqlquery)
END
Suppose,
@tablename = BuyerMaster
@whereclause = And username=admin
It give an Error “Invalid column name admin”
How can I put admin in single quotes as I am getting the values from ASP.NET page.
Please Help Me,
Please.
Thanking You,
Rahul
You should use where clause like this
@whereclause =’ And username=”admin”’
In above statement, multiple single quotes are used.
Hi,
To assign values dynamically we need to use sp_executesql procedure. The N’ is mandatory as the parameter should be converted to NText.
USE [MyDB]
GO
/****** Object: StoredProcedure [dbo].[selectedSortedOutput] Script Date: 01/27/2009 11:02:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[selectedSortedOutput](@sortColname nvarchar(50))
As
declare
@FName nvarchar(50),
@Sql nvarchar(1500);
begin
set @Sql = ’select @FName=FirstName from Customer order by ‘ + @sortColname;
exec sp_executesql @Sql,N’@FName nvarchar(50) OUTPUT’, @FName OUTPUT;
Print @FName;
end;
Hi,
I have a very simple query and it seems valid to me but it is not working and giving a syntax error. I looked on different sites but I couldn’t find anything. And finally have decided to ask you.
SELECT ISNULL(NULL, EXEC(’SELECT 1′))
Expected output is 1
If we run only EXEC(’SELECT 1′) then it works.
Please let me know what should I use?
Thanks,
Hitesh Savalia, MCAD
@Hitesh
how about this ?
SELECT ISNULL(NULL, 1)
Output : 1
– Why dont you try any one of this,
Tip 1:
if exists ( select cola from table1 where cola is null)
begin
set @sql = execute dynamic sql parameter, with output parameter
set cola = output value from above dynamic sql
end
Tip 2 :
select
case when cola is null then another_value
from table1
Please post complete question, what is your requirement, what is that you are trying to execute ?
~IM
Thanks Imran,
I posted simple query so that anybody can understand. My original query is
SELECT ISNULL(NULL, EXEC (’BEGIN
EXEC Inferred_CreateDimPatientMember 1
SELECT PatientKey FROM KeyMapPatientView WHERE CphPatientID = 1 END’))
I know your Tip1 and Tip2 can work but those are not helpful in my case because I want to get an output in single sql statement.
So In my above query I am expecting to have output as PatientKey.
Thanks
Hitesh Savalia.
I am tasked with moving a bunch of data from many servers to one server on a daily basis.
Basic flow is to pull a set of connection information about various servers, then work through each set making a call to a standard stored procedure which moves and transforms the data to its final resting spot in the reporting database.
I need to keep security in mind and manual configuration, hence the choice to pull connection information for data sets that are maintained and create a connection to each server. Each server is configured and setup the same and contains the same database and fields.
Is there a way to send dynamic connection information into a stored procedure or other object to move the data or am I limited to using a coded (perl, .net, php) script to make the connections and execute the same set of code?
exec MASTER..XP_CMDSHELL ‘Echo Conteudo>>c:\teste.txt’