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'
