Order by / Case

[chueke]

Iniciado por Libras

puedes poner tu codigo cuando realizas el string?

Código SQL:

ALTER PROCEDURE [dbo].[bus_pal] 
    -- Add the parameters for the stored procedure here
    @clave NVARCHAR(50),
    @PageNumber INT,
    @marc NVARCHAR(50) = NULL,
    @prc NVARCHAR(50) = NULL
   
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
 
    -- Insert statements for procedure here
    DECLARE @SQLString NVARCHAR(500)
    DECLARE @ParmDefinition NVARCHAR(500)
 
    /* Cadena */
    SET @SQLString = N'SELECT RXFAM1, RXFAM2, RXFAM3, PSPREC, VMVNNM FROM (SELECT RXFAM1, RXFAM2, RXFAM3, RXFAM4, PSPREC, VMVNNM, ROW_NUMBER() OVER (ORDER BY CASE WHEN @marc = ''ok'' THEN VMVNNM END ASC, CASE WHEN @prc = ''desc'' THEN PSPREC END DESC, CASE WHEN @prc = ''asc'' THEN PSPREC END ASC) AS Recursos FROM Recursos WHERE CPA01 LIKE  @clave + ''%'') AS Resultados WHERE Recursos BETWEEN 8 * @PageNumber  + 1 AND 8 * (@PageNumber + 1)'
    /* Formato del/los parámetros */
    SET @ParmDefinition = N'@clave NVARCHAR(50),@PageNumber INT,@marc NVARCHAR(50),@prc NVARCHAR(50)'
 
    EXEC sp_executesql @SQLString, @ParmDefinition, @clave, @PageNumber, @marc, @prc;
 
END