Execute Immediate from function..

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 ~
homnath_sharmaAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Hi homnath_sharma,
you can't run dynamic sql DIRECTLY inside a function, You can use OPENQUERY but not recommended


Cheers!
0
 
homnath_sharmaAuthor Commented:

oK, Could you please provide some simple code example...

Its would be easy for me to understand in better way..


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
homnath_sharma,
You can create an sp to do this
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
homnath_sharmaAuthor Commented:
Aneesh,

I have to migrate SybaseSQL Anywhere Trigger which has row level. So i dont find any thing like

for each row in mssql server.

Could you let me know how do i do it..

~HNS ~
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
If it is in sql server 2000, then you can use a cursor or temp table or table variable
In case it is 2005, you can use ROW_NUMBER() to do the same
0
 
Gautham JanardhanCommented:
check sp_executesql in BOL in SQL SERVER for executing dynamic queries.

eg

CREATE PROCEDURE InsertSales @OrdID INT, @CustID INT,
                 @OrderDate DATETIME, @DeliveryDate DATETIME
AS
DECLARE @SQLInsString NVARCHAR(500)
DECLARE @OrderMonth INT

-- Build the INSERT statement.
SET @SQLInsString = 'INSERT INTO ' +
       /* Build the name of the table. */
       SUBSTRING( DATENAME(mm, @OrderDate), 1, 3) +
       CAST(DATEPART(yy, @OrderDate) AS CHAR(4) ) +
       'Sales' +
       /* Build a VALUES clause. */
       ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
       ' @InsOrdMonth, @InsDelDate)'

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @OrderDate)

EXEC sp_executesql @SQLInsString,
     N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
     @OrdID, @CustID, @OrderDate,
     @OrderMonth, @DeliveryDate
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.