return only a subset by languageID - ???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

return only a subset by languageID - ????

Author

Topic

newbie08
Starting Member

2 Posts

Posted - 2007-01-09 : 13:16:52

I am struggling with a Stored Procedure, what I would like it to return is a subset of records where LanguageID = @LanguageID. For example our database has keywords in English, German and Spanish I want only a subset of records returned and the subset should only have the records from the specific languageID that was passed to the Stored Procedure. I have tried a few different versions of the Stored Proc below but either I get only a subset with mixed records back or no records at all. Can you help me out? Thanks, newbie

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[syl_KeywordGetSubsetSortedByLanguageID]

@LanguageID int,

@sortExpression nvarchar(50),

@startRowIndex int,

@maximumRows int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON

BEGIN TRY

IF LEN(@sortExpression) = 0

SET @sortExpression = 'KeywordID'

-- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment

SET @startRowIndex = @startRowIndex + 1

-- Issue query

DECLARE @sql nvarchar(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 WHERE LanguageID = @LanguageID'

-- Execute the SQL query

EXEC sp_executesql @sql

RETURN

END TRY

BEGIN CATCH

--Execute LogError SP

EXECUTE [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 -1

END CATCH

END

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-09 : 17:39:58

Try this

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[syl_KeywordGetSubsetSortedByLanguageID]

@LanguageID int,

@sortExpression nvarchar(50),

@startRowIndex int,

@maximumRows int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON

BEGIN TRY

IF LEN(@sortExpression) = 0

SET @sortExpression = 'KeywordID'

-- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment

SET @startRowIndex = @startRowIndex + 1

-- Issue query

DECLARE @sql nvarchar(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 = @LanguageID) AS KeywordInfo

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

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

-- Execute the SQL query

EXEC sp_executesql @sql

RETURN

END TRY

BEGIN CATCH

--Execute LogError SP

EXECUTE [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 -1

END CATCH

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources