Re: SELECT TOP with variable not working

From: Paul (Paul_at_discussions.microsoft.com)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 14:01:02 -0700

Hi thanks for the additional information. Was not aware of the sp_exeutesql
command but looks to be pretty powerful and has additional capability over
EXEC.

"David G." wrote:

> Paul wrote:
> > Or just put the dynamic SQL statement in an nvarchar variable and
> >> execute it using sp_executesql instead inside the stored procedure.
> >
> > Hi Dave, just wondering if you could provide a brief example as most
> > the SQL stuff I have done is just interfacing with stored procedures.
> > Thanks. "David G." wrote:
>
> Sure. Here's an example of a stored procedure that can accept a variable
> that determines the number of TOP X rows to return:
>
> Create Proc MyTest
> @iTopX int
> as
> Declare @nvcSql nvarchar(1000)
>
> Set @nvcSql = N' Select TOP ' + CAST(@iTopX as nvarchar(10)) + N'
> ColList From MyTable'
>
> Exec sp_executesql @nvcSql
> Go
>
> Another nice feature of sp_exeutesql is the ability to use parameterized
> queries with it 9see BOL for more information).
>
> You can certainly execute the SQL with just the EXEC command inthe
> stored procedure, but I prefer using sp_executesql, especially since I
> use it often with parameters (not supported by Exec).
>
>
>
>
> --
> David G.
>
>

Relevant Pages

  • Re: passing calculated parameter to a stored procedure
    ... EXEC sp_MyStoredProcedure @datetofind = @dt ... > this stored procedure when I launch a job. ... > EXEC sp_MyStoredProcedure @datetofind = DatePart(dy, ... when I parse the command. ...
    (microsoft.public.sqlserver.programming)
  • Re: Sorting data in a stored procedure
    ... David wrote: ... > I have some stored procedure which selects some data, ... @SortBy nVarChar= Null ... exec usp_Something 'f_ID Desc' ...
    (microsoft.public.sqlserver.server)
  • Re: COBOL stored procedure for DB2
    ... The makefile will include appropriate build instructions for the stored procedure, ... exec sql begin declare section end-exec. ... Dim cmCallSP As Command ...
    (comp.lang.cobol)
  • Re: COBOL stored procedure for DB2
    ... for building and executing COBOL SP's using Object COBOL 4.1, ... Before building a stored procedure on AIX 4.2.1 using the Micro Focus 4.1 ... confirm the cob command used for creating the SP module. ... exec sql begin declare section end-exec. ...
    (comp.lang.cobol)
  • Re: COBOL stored procedure for DB2
    ... I've gone back to the IBM documentation, to remind myself of the requirements for building and executing COBOL SP's using Object COBOL 4.1, rather than Server Express. ... The stored procedure is loaded under the control of DB2 -- as opposed to the client-side environment -- hence DB2 will need to inherit the COBOL environment for the purposes of executing the stored procedure. ... confirm the cob command used for creating the SP module. ... exec sql begin declare section end-exec. ...
    (comp.lang.cobol)