Re: Need to make Sort ORDER in SP defined by Input variable
From: Steve Kass (skass_at_drew.edu)
Date: 06/26/04
- Next message: Mike John: "Re: Altering fields in production"
- Previous message: Larry Woods: "Re: Need to make Sort ORDER in SP defined by Input variable"
- In reply to: Larry Woods: "Re: Need to make Sort ORDER in SP defined by Input variable"
- Next in thread: Larry Woods: "Re: Need to make Sort ORDER in SP defined by Input variable"
- Reply: Larry Woods: "Re: Need to make Sort ORDER in SP defined by Input variable"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 26 Jun 2004 13:53:55 -0400
Larry Woods wrote:
>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.
>
>
What does that mean??? Where are you executing this from?
>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!!!!!
>
>
These two SET statements appear to be identical if ISBN is the actual
parameter, whether or not quotes are used around the parameter. Run
this, and you'll see that the @sql value is the same in each case.
CREATE 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
select @SQL
SET @SQL = 'SELECT ISBN FROM tblISBNs WHERE ISBN = ''0021465614'' ORDER
BY ISBN'
select @SQL
GO
exec cbe_ISBNActivityHits ISBN
exec cbe_ISBNActivityHits 'ISBN'
GO
drop proc cbe_ISBNActivityHits
In any case, an ORDER BY clause to sort by ISBN makes no difference
here, since all returned rows have the same ISBN value.
SK
>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!
>>>
>>>
>>>
>.........................................
>
>
>
>
- Next message: Mike John: "Re: Altering fields in production"
- Previous message: Larry Woods: "Re: Need to make Sort ORDER in SP defined by Input variable"
- In reply to: Larry Woods: "Re: Need to make Sort ORDER in SP defined by Input variable"
- Next in thread: Larry Woods: "Re: Need to make Sort ORDER in SP defined by Input variable"
- Reply: Larry Woods: "Re: Need to make Sort ORDER in SP defined by Input variable"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
