Sintax problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Sintax problem

Author

Topic

lordtemon
Starting Member

1 Post

Posted - 2009-12-10 : 02:35:50

Hi,

i'm trying to do this:



ALTER PROCEDURE [dbo].[sp_UL_GetLicenses]
	@PartnerId int,
	@PageIndex int,
	@RowsPerPage int,
	@SortField varchar(50),
        @SortOrder varchar(4)

set @query = 'Select LIC_Id, LIC_Cod
              FROM Licenses
              ORDER BY LIC_Date ASC'

insert into @TempTable 
EXEC (@query)

if (@SortField = 'LIC_Id')
BEGIN
     WITH Result as (
     SELECT ROW_NUMBER() OVER (Order by LIC_Id) AS ROW_ID, 
     LIC_Id, LIC_Cod
     FROM @TempTable
END
		     )	
if (@SortField = 'LIC_Cod')
BEGIN
     WITH Result as (
     SELECT ROW_NUMBER() OVER (Order by LIC_Cod)
     AS ROW_ID, LIC_Id, LIC_Cod
     FROM @TempTable
END
		    )

SELECT LIC_Id, LIC_Cod
FROM Result WHERE ROW_ID BETWEEN
((@PageIndex * @RowsPerPage) + 1) AND ((@PageIndex + 1) * @RowsPerPage)
ORDER BY ROW_ID

But gives me a sintax error near 'END'.

Whats the correct sintax?

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-10 : 02:42:17

Hi

Try this...but am not understand your code logic why u have done like this...


ALTER PROCEDURE [dbo].[sp_UL_GetLicenses]
	@PartnerId int,
	@PageIndex int,
	@RowsPerPage int,
	@SortField varchar(50),
    @SortOrder varchar(4)
AS 
DECLARE @query NVARCHAR(MAX)

DECLARE @TempTable TABLE(LIC_Id INT, LIC_Cod VARCHAR(50)) --Datatype size u can choose

set @query = 'Select LIC_Id, LIC_Cod
              FROM Licenses
              ORDER BY LIC_Date ASC'

insert into @TempTable 
EXEC (@query)

if (@SortField = 'LIC_Id')
BEGIN
     WITH Result as (
     SELECT ROW_NUMBER() OVER (Order by LIC_Id) AS ROW_ID, 
     LIC_Id, LIC_Cod
     FROM @TempTable
	)
		SELECT	* FROM Result	
END
		     	
if (@SortField = 'LIC_Cod')
BEGIN
     WITH Result as (
     SELECT ROW_NUMBER() OVER (Order by LIC_Cod)
     AS ROW_ID, LIC_Id, LIC_Cod
     FROM @TempTable
	) 
	SELECT	* FROM Result
END
	

SELECT LIC_Id, LIC_Cod
FROM Result WHERE ROW_ID BETWEEN
((@PageIndex * @RowsPerPage) + 1) AND ((@PageIndex + 1) * @RowsPerPage)
ORDER BY ROW_ID

-------------------------
R...

Sachin.Nand

2937 Posts

Posted - 2009-12-10 : 04:21:06

[code]
WITH Result as (
SELECT ROW_NUMBER() OVER (Order by case @SortField when 'LIC_Id' then LIC_Id
when 'LIC_Cod' then LIC_Cod end) AS ROW_ID,
LIC_Id, LIC_Cod
FROM @TempTable
)
SELECT LIC_Id, LIC_Cod
FROM Result WHERE ROW_ID BETWEEN
((@PageIndex * @RowsPerPage) + 1) AND ((@PageIndex + 1) * @RowsPerPage)
ORDER BY ROW_ID
[/code]
PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources