?
Solved

Execute Immediate from function..

Posted on 2006-11-13
6
Medium Priority
?
1,314 Views
Last Modified: 2010-05-18
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 ~
0
Comment
Question by:homnath_sharma
  • 3
  • 2
6 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 100 total points
ID: 17936280
Hi homnath_sharma,
you can't run dynamic sql DIRECTLY inside a function, You can use OPENQUERY but not recommended


Cheers!
0
 

Author Comment

by:homnath_sharma
ID: 17936292

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

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


0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17936297
homnath_sharma,
You can create an sp to do this
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:homnath_sharma
ID: 17936331
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17936379
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
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17936638
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question