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 ~
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
And check this article for some important differences between both approaches: http://www.sommarskog.se/dynamic_sql.html -- Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:http://www.insidesql.de
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 ~
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.
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
<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>
