SQL SERVER – Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always

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)

About these ads

72 thoughts on “SQL SERVER – Difference Between EXEC and EXECUTE vs EXEC() – Use EXEC/EXECUTE for SP always

  1. 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 kalpesh,

      In this scenario, i had observed the difference in client statistics.

      I had one table

      that have 82 rows.

      After i execute both ways i found the below difference.

      In first scenario Bytes sent from client taken 52bytes.

      In second Scenario Byte sent from client taken 140bytes.

      so as per that statistics, First scenario is good.

      If i wrong, Correct me.

      Regards
      sreeram

      • Hi guys,
        can anybody hepl me what is exact stored procedure? differnce between procedure and SP,and can I use SP on DB2.

        Thanks & Regards
        Sunny V

  2. 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?

  3. 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 ‘

  4. 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

  5. what if some parameters in stored procedures can be null and we want to ignore them while calling that stored proc using ‘exec’ ?

  6. >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)

  7. Why this doesnot work?

    EXEC (‘Use pubs’)

    The command is successfull but the database setting didnot change when I execute in Query Analyzer?

  8. >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.

  9. 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

  10. 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.

  11. 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…

  12. 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

  13. 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

  14. You should use where clause like this

    @whereclause =’ And username=”admin”’

    In above statement, multiple single quotes are used.

  15. 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;

  16. 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

    • > 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?

      Hi Hitech,

      The exec() statement does not work that way. When using exec() with brackets it will not return a scalar value, unlike “exec @Result = someStoredProcedure”

      You can still use the result set than comes back from exec(), though, like this:

      declare @sqlStr nvarchar(max)
      declare @myVar varchar(max)
      declare @myTable varchar(max)
      declare @totalValue int

      set @myTable = ‘tableName’
      set @myVar = ‘columnName’

      set @sqlStr = ‘select fooBarBaz=sum(‘ + @myVar + ‘) from ‘ + @myTable
      print @sqlStr
      declare @tempTable table (fooBarBaz int)
      insert @tempTable exec(@sqlStr)
      select @totalValue = fooBarBaz from @tempTable
      print @totalValue

      As you can see, though, it’s very messy having to go through a temporary table to hold the result set.

  17. @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

  18. 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.

  19. 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?

    • Hi Shelly,

      Have you looked at using OPENQUERY or OPENROWSET to access remote data via Linked Servers? This is fast and easy for small volumes of data.

      If you’re talking large volumes of data it may be faster (and much easier on the indexes of your destination server) if you use BCP.EXE to dump the data from your source servers in “native format”, transfer it with ftp/scp/rsync, and then BULK INSERT/BCP.EXE to read it back in on the destination server. This is how I used to move millions of rows of IP traffic flows between servers.

      Good luck.

  20. 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’

  21. 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….

      • @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.

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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??

  28. 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

  29. 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]
      */

  30. 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

  31. 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

  32. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  33. 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

  34. 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.

  35. I am using entity framework , with same page somewhere i am using SP, SP takes too much time compare to Entity framework.
    so my question is that , is entity framework better than Stored procedure ? if no then why i face this type of issue ?

    • I’d start by using SQL Profiler to see what SQL is being submitted by Entity Framework to the server and what SET OPTIONS it is using.

      A couple of possibilities:

      (a) EF may be using a different configuration of SET OPTIONS than the stored procedure is internally. SET OPTIONS will change the query planner and caching behaviour and every combination of SET OPTIONS caches its own query plan, even if the SQL code was byte-for-byte identical.

      (b) Maybe the stored procedure is just dumb. Using the exact same SET OPTIONS used by EF in (a) inspect the Actual Execution Plan of your stored procedure and see where the highest costs are. Perhaps your joins are doing too much work? Perhaps you are missing one or more indexes?

      (c) EF will only be selecting the columns that it needs whereas the stored procedure will always select all of the columns that it is instructed to. Computed columns implemented by User-Defined Functions can be very expensive. [Google for discussions on RBAR… Row By Agonizing Row.] Consider replacing the stored procedure with a view as query planner will automatically exclude columns and joins not required for the set of columns being selected from a view.

  36. This my question
    In Stored procedure we exec spname
    but for me I want
    create one procedure
    while I execute all we use exec SPName
    but I need EXEC(select * from tablename)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s