Re: Need to make Sort ORDER in SP defined by Input variable

From: Larry Woods (larry_at_NOSPAMlwoods.com)
Date: 06/26/04 

Date: Sat, 26 Jun 2004 10:26:20 -0700

Nope!

If I enter 'ISBN' (w/o quotes) when I execute the SP below, it executes,
showing the correct number of lines, but I get NO fields displayed.

Original SELECT line:

SET @SQL = 'SELECT ISBN FROM tblISBNs WHERE ISBN = ''0021465614'' ORDER BY '
+ @sortorder

If I modify toe SELECT from this to this:

SET @SQL = 'SELECT ISBN FROM tblISBNs WHERE ISBN = ''0021465614'' ORDER BY
ISBN'

it works fine!!!!!

Here is the complete SP:

ALTER PROCEDURE dbo.cbe_ISBNActivityHits
(
@sortorder nvarchar(50)
)
AS
SET NOCOUNT ON
DECLARE @SQL nvarchar(400)
SET @SQL = 'SELECT ISBN FROM tblISBNs WHERE ISBN = ''0021465614'' ORDER BY '
+ @sortorder
EXEC sp_executesql @SQL

"Pablo" <Pablo@discussions.microsoft.com> wrote in message
news:BD76972A-DC2F-406A-AC2A-0AECA2DA681C@microsoft.com...
> Use Northwind
> go
> DECLARE @SQL NVARCHAR(1024)
> DECLARE @Column NVARCHAR(64)
>
> Set @Column='LastName'
> SET @SQL='SELECT * FROM dbo.Employees ORDER BY '+@Column
>
> exec sp_executesql @SQL
>
>
> Pablo
>
>
> "Larry Woods" wrote:
>
> > Help.... I'm back!
> >
.........................................

Relevant Pages

  • Re: Application role to access xp_cmdshell
    ... This necessitates that your user procs be owned ... See Cross-database chaining in the SQL 2000 ... You will also need to allow non-sysadmin users to execute xp_cmdshell. ... EXEC sp_dboption 'MyDatabase', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • xp_sqlagent_proxy_account: Specified user cannot login
    ... I am trying to configure SQL so that a user without admin privileges can ... execute xp_cmdshell. ... EXEC master..xp_sqlagent_proxy_account N'SET' ...
    (microsoft.public.sqlserver.security)
  • Re: Need to make Sort ORDER in SP defined by Input variable
    ... and you'll see that the @sql value is the same in each case. ... exec cbe_ISBNActivityHits ISBN ... >ALTER PROCEDURE dbo.cbe_ISBNActivityHits ... >SET NOCOUNT ON ...
    (microsoft.public.sqlserver.programming)
  • Re: use of INSERT with Dynamic SQL
    ... > I supose the only way is to use dynamic SQL. ... > Is there a way to execute an INSERT statemente with dynamic SQL? ... > EXEC spInsertMyTable ...
    (microsoft.public.sqlserver.programming)
  • Passing a file path to a SP
    ... SET NOCOUNT ON; ... DECLARE @SQL nvarchar ... EXEC ...
    (comp.databases.ms-sqlserver)