need a subsetsorted by languageid can somebody help with stored proc..............

Here is part of my stored procedure. I would like to get a subsetsorted back from my query where languageid = @.languageid but I am lost. Can somebody help me out, thanks Newbie

-- Issue query

DECLARE @.sqlnvarchar(4000)

SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

WHERE RowNum BETWEEN '+CONVERT(nvarchar(10), @.startRowIndex)+

' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '

+CONVERT(nvarchar(10), @.maximumRows)+') - 1 AND [LanguageID] = @.LanguageID'

-- Execute the SQL query

EXECsp_executesql @.sql

If this answers your question... please mark this as the "answer" :)

DECLARE

@.SQLVARCHAR(8000)

SELECT @.SQL='
WITH _temp AS
(SELECT
KeywordID,
Keyword,
LanguageID,
ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+' ) AS RowNumber
FROM
syl_Keywords
WHERE
LanguageID = '''+ @.LanguageID+''')

SELECT TOP '+CONVERT(VARCHAR, @.maximumRows)+' *
FROM _temp WHERE RowNumber > '+CONVERT(VARCHAR, @.startRowIndex)

EXECsp_executesql @.SQL

Peace,

|||

Hi Nullable, thank you for your reply. I can't get it to work with your example. Here is my original stored procedure that returns a subset sorted by sortexpression which works. What I would like to accomplish is to sort it by LanguageID as well.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[syl_KeywordGetSubsetSorted]

@.sortExpression

nvarchar(50),

@.startRowIndex

int,

@.maximumRows

int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTONBEGINTRYIFLEN(@.sortExpression)= 0SET @.sortExpression='KeywordID'-- Since @.startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), incrementSET @.startRowIndex= @.startRowIndex+ 1-- Issue queryDECLARE @.sqlnvarchar(4000)SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '

+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

WHERE RowNum BETWEEN '

+CONVERT(nvarchar(10), @.startRowIndex)+' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '+CONVERT(nvarchar(10), @.maximumRows)+') - 1'

-- Execute the SQL queryEXECsp_executesql @.sqlRETURNENDTRY

BEGIN

CATCH--Execute LogError SPEXECUTE [dbo].[syl_LogError];--Being in a Catch Block indicates failure.--Force RETURN to -1 for consistency (other return values are generated, such as -6).RETURN-1ENDCATCH

END

|||

newbie06:

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+') AS RowNum

FROM syl_Keywords) AS KeywordInfo

Change that to:

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '+ @.sortExpression+', LanguageID) AS RowNum

FROM syl_Keywords) AS KeywordInfo

|||

Here is the working portion of the Stored Proc:

-- Issue query

DECLARE @.sqlnvarchar(4000)SET @.sql='SELECT [KeywordID], [Keyword], [LanguageID]

FROM

(SELECT [KeywordID], [Keyword], [LanguageID], ROW_NUMBER() OVER(ORDER BY '

+ @.sortExpression+') AS RowNum

FROM syl_Keywords

WHERE LanguageID = '

+CONVERT(nvarchar(10), @.LanguageID)+' )

AS KeywordInfo

WHERE RowNum BETWEEN '

+CONVERT(nvarchar(10), @.startRowIndex)+' AND ('+CONVERT(nvarchar(10), @.startRowIndex)+' + '+CONVERT(nvarchar(10), @.maximumRows)+') - 1'