Re: Dynamic SQL Execution

From: exBK (exBK_at_discussions.microsoft.com)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 08:54:44 -0800

Thank you so much for your reply. I have been trying exactly the same thing
... my only problem is, the string:
'SELECT @rOUT =[114x] FROM tblName WHERE eid=100'
is assigned to a variable each time within my cursror loop. In otherwords,
I have a:
SET @sql = 'SELECT @rOUT =[' + @cName + '] FROM tbl WHERE eid=' + @eID
and when I print my @sql, I get
'SELECT @rOUT =[114x] FROM tblName WHERE eid=100'

so when I use
sp_executesql N@sql,
    '@rOUT (datatype) OUTPUT',
     @rOUT OUTPUT
I don't get erors following:
* if I use N@sql .. it says "Incorrect syntax near 'N@sql'"
* if I use @sql .. it says "Procedure expects parameter '@statement' of
type 'ntext/nchar/nvarchar'"

Not sure what I am doing wrong...TIA for your time.

"Adam Machanic" wrote:

> Use sp_executesql with an OUTPUT parameter:
>
> declare @rOUT (datatype)
>
> sp_executesql N'SELECT @rOUT =[114x] FROM tblName WHERE eid=100',
> '@rOUT (datatype) OUTPUT',
> @rOUT OUTPUT
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "exBK" <exBK@discussions.microsoft.com> wrote in message
> news:B5A0D99D-5FF6-46B7-A2BD-72EF93FC560E@microsoft.com...
> > I asked a similar question yesterday but still not sure how to move on ..
> > hence posting it agin:
> >
> > In a cursor, as I loop through, I create a dynamic SQL each time.
> > My dynamic SQL looks something like this:
> >
> > SELECT @rOUT =[114x] FROM tblName WHERE eid=100
> > i.e, the above is assigned to a variable and when I print the variable, I
> > get the statement.
> > I am interested in getting the value for @rOUT so that I can use it in an
> > insert statment within the cursor's loop. I am not sure how to achieve
> this.
> > Any tips are appreciated. TIA.
> >
> >
>
>
>

Relevant Pages

  • Re: Slow performance with SQL and stmt.executeUpdate()
    ... method call, using SQL insert. ... Statement stmt = conn.createStatement; ... String phone = md.getPhone; ... after the loop is finished. ...
    (comp.lang.java.databases)
  • Re: Can you use dynamic data in a request form
    ... I am am trying to write some code to perform a INSERT in SQL ... > table using a loop. ... How that string is built is irrelevant. ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.general)
  • Re: Loop through names of controls
    ... Didn't scroll down to the string of the sql. ... thought he was having trouble getting an object in his loop. ... >> I'm running some SQL to insert the value in each control into a table. ...
    (microsoft.public.access.formscoding)
  • Newbee - how to loop through table and delete unwanted records
    ... Note it is always the 1st record of the new groupNo ... So I have decided to try to loop though the table and compare the ... Then the next line of code runs the SQL delete string I made in step 1. ...
    (microsoft.public.access.gettingstarted)
  • Re: Dynamic SQL Execution
    ... @rOUT OUTPUT ... SQL Server MVP ... > In a cursor, as I loop through, I create a dynamic SQL each time. ...
    (microsoft.public.sqlserver.programming)