Thanks alotHi there,
a standard Pentium 4 with 256 RAM will do i guess, take care my fren.. :)
a standard Pentium 4 with 256 RAM will do i guess, take care my fren.. :)
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)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
intAS
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 RowNumFROM 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-1ENDCATCHEND
|||
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 RowNumFROM 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'