Re: Dynamic SQL Execution

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 11:59:05 -0500

Declare @sql as NVARCHAR. Also, you'll have to use the N' on the second
parameter as well (or use another NVARCHAR variable); I forgot to put it
there too. 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"exBK" <exBK@discussions.microsoft.com> wrote in message
news:DBE70382-3298-4AF6-B3BE-BAB0236EA3AE@microsoft.com...
> 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: Dynamic SQL Execution
    ... the string: ... > @rOUT OUTPUT ... > SQL Server MVP ... >> In a cursor, as I loop through, I create a dynamic SQL each time. ...
    (microsoft.public.sqlserver.programming)
  • 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)
  • Re: Reg bind variables
    ... end loop; ... code fragment 1 is an example of *static sql* in an anonymous block. ... Dynamic sql can use bind variables, it is hard parsed at least once. ...
    (comp.databases.oracle.server)
  • Re: Reg bind variables
    ... SQL> declare ... end loop; ... If i use bind variables it is taking nearly 4min where as without bind ... Dynamic sql can use bind variables, it is hard parsed at least once. ...
    (comp.databases.oracle.server)
  • Re: Newbie: VB-ADO help
    ... I Do have some SQL knowledge and because the calculations are a bit ... the first loop ... are too numerous for memory storage. ... I do not want to deal with SQL and the like. ...
    (microsoft.public.vb.general.discussion)