CODEWITHSUNDEEP
sql-server
omini data
omini data

Reputation: 427

Order by from a stored procedure

How do I do a order by from my stored procedure?

ALTER PROCEDURE [Data].[filtering_param]
    @param1 NVARCHAR(1000) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Sql Nvarchar(MAX);  

    SET @Sql = N'SELECT
                     ps.Mærket AS Mærke, P.DataID,
                     PS.Billed, P.Model, P.vendor, 
                     P.Årgang, P.[Motor Type], 
                     P.Krydsmålet, P.Centerhul, 
                     P.ET, P.Bolter, P.Dæk, P.Fælge, PS.Krydsmålene 
                 FROM
                     Data.Hjuldata P  
                 INNER JOIN
                     Data.Mærke PS ON P.MærkeID = PS.MærkeID
                 ORDER BY 
                     ps.Mærket, P.model
                 WHERE 1 = 1 '
        + CASE WHEN @param1 IS NOT NULL 
         THEN N' AND Krydsmålet = @param1 ' ELSE N'' END

    EXEC sp_executesql @Sql, N'@param1  NVARCHAR(1000)', @param1 
END

I get this error:

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll Additional information: Incorrect syntax near the keyword 'WHERE'.

Upvotes: 0

Views: 41

Answers (2)

Cee McSharpface
Cee McSharpface

Reputation: 8725

The ORDER BY goes after the WHERE clause. There is no need to compose a string and pass it to sp_executesql, just write the T-SQL code into the procedure.

ALTER PROCEDURE [Data].[filtering_param]

@param1 nvarchar(1000)=NULL

AS
SET NOCOUNT ON

SELECT
    ps.Mærket AS Mærke,
    P.DataID,
    PS.Billed,
    P.Model,
    P.vendor,
    P.Årgang,
    P.[Motor Type],
    P.Krydsmålet,
    P.Centerhul,
    P.ET,
    P.Bolter,
    P.Dæk,
    P.Fælge,
    PS.Krydsmålene
FROM Data.Hjuldata P  
INNER JOIN Data.Mærke PS ON P.MærkeID=PS.MærkeID
WHERE @param1 IS NULL OR Krydsmålet=@param1
ORDER BY
    ps.Mærket,
    P.model

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

The Order by clause must be after the where clause. Also, there is no need for dynamic sql here. You can write it like this:

Select ps.Mærket AS Mærke
         , P.DataID
         , PS.Billed
         , P.Model
         , P.vendor
         , P.Årgang
         , P.[Motor Type]
         , P.Krydsmålet
         , P.Centerhul
         , P.ET
         , P.Bolter
         , P.Dæk
         , P.Fælge 
         , PS.Krydsmålene 
from Data.Hjuldata P  
inner join Data.Mærke PS on P.MærkeID = PS.MærkeID
WHERE @param1 IS NULL 
OR Krydsmålet = @param1
ORDER BY ps.Mærket, P.model

Upvotes: 2

Related Questions