SQL Server Performance

Execute Immediate syntax

Discussion in 'General DBA Questions' started by homnath_sharma, Nov 7, 2006.

  1. homnath_sharma New Member

    Hi,

    Hope to get the solution for the below from expert asap..

    SET @query = @query + ' FROM DBO.' + @sTable + ' WHERE ' + @sTable +'.ComponentId = ' + CONVERT(CHAR, @id);

    EXECUTE IMMEDIATE @query;

    Whats wrong with the above syntax, which i am trying to implement inside my cursor. I am getting error as:

    Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'IMMEDIATE'. The stored procedure will still be created.

    Awaiting to hear your support on this asap.

    Thanks in advance.
    ~ HNS ~
  2. Roji. P. Thomas New Member

    EXECUTE IMMEDIATE is PL/SQL syntax. In SQL server, you only have to write

    EXECUTE(@query). Also look up BOl for sp_executesql



    Roji. P. Thomas
    http://toponewithties.blogspot.com
  3. FrankKalis Moderator

  4. homnath_sharma New Member

    Hi,

    I am trying to execute below code within MSSQL function..

    SET @query = @query + ' FROM DBO.' + @sTable + ' WHERE ' + @sTable +'.ComponentId = ' + CONVERT(CHAR, @id);
    EXECUTE IMMEDIATE @query;
    EXECUTE (@query);

    Getting error as:
    Invalid use of 'EXECUTE' within a function.

    Please let me know how should i achieve this..
    I tried using execute and execute immediate. But its giving error.

    Thanks in advance.
    ~ HNS ~
  5. Adriaan New Member

    Stop repeating questions.

    Read the answers: the keyword IMMEDIATE does not exist in T-SQL.
  6. Adriaan New Member

    Other than that, I wouldn't be surprised if you cannot use EXECUTE in a UDF.

    Looks like you need a STORED PROCEDURE. Stop thinking you need functions.
  7. Madhivanan Moderator

    I think he is basically from ORACLE backgroud moved to SQL Server and he tries to simulate everything.

    homnath_sharma,
    you need to make use of sql server help file for all basic systaxes.
    Also read the replies fully. It was suggested that IMMEDIATE cant be used in SQL Server and if you ask the same question again, you will be out of help

    Also I have noted that you posted only probablamatic quesries and ask for help. In addition to that explain what you want and there may be better way of doing than you are trying to do.

    Madhivanan

    Failing to plan is Planning to fail
  8. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Other than that, I wouldn't be surprised if you cannot use EXECUTE in a UDF.<br /><br />Looks like you need a STORED PROCEDURE. Stop thinking you need functions.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Correct. you cannot use dynamic SQL inside a UDF. Simple as that. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page