1

As of now I have a SQL statement like this:

SELECT 
    y.ID, y.STATUS, y.CONTROLID, y.TRCDE
FROM 
    YTDTRNI AS y 
LEFT JOIN 
    VND AS v ON y.COMNO = v.COMNO 
WHERE 
    TRCDE ='RC' 
ORDER BY 
    ID DESC

I am trying to use this query inside a stored procedure using dynamic SQL. So far, my stored procedure looks like this:

SET @query =  N'SELECT y.ID, y.STATUS, y.CONTROLID, y.TRCDE
                FROM YTDTRNI AS y LEFT JOIN VND AS v ON y.COMNO = v.COMNO 
                WHERE TRCDE = ' + @searchtrtype + ' 
                ORDER BY ' + @orderbycondition

The variables @searchtrtype and @orderbycondition are of type nvarchar.

I am using a ASP.NET/C# program to call the stored procedure. However, it breaks with an exception:

An expression of non boolean type in a context where a condition is expected near 'ORDER'

I think I am getting error because the string values are not properly concatenated or formatted in inside the @query variable.

Open to any advice.

EDIT: my stored procedure looks like this at the moment:

enter image description here

When I execute the stored procedure, it returns the result set that I want but it also shows an error message :

Must declare the scalar variable "@dsearchtrtype".

I tried declaring it inside the BEGIN body and declaring it as part of a parameter for the stored procedure but it still shows that same message.

3
  • 1
    have you got missing apostrophes? Commented Jul 6, 2018 at 10:30
  • Could you show the variable declaration? or the full stored proc code? Commented Jul 6, 2018 at 10:31
  • 3
    Be careful with SQL injection Commented Jul 6, 2018 at 10:32

4 Answers 4

4

Learn to use parameters with sp_executesql. You can do this for @searchtrtype, but not @orderbycondition:

SET @query =  N'
SELECT y.ID, y.STATUS,y.CONTROLID,y.TRCDE
FROM YTDTRNI y LEFT JOIN
     VND v
     ON y.COMNO = v.COMNO 
WHERE TRCDE = @searchtrtype 
ORDER BY @orderbycondition';

SET @query = REPLACE(@query, '@orderbycondition', @orderbycondition);

EXEC sp_executesql @query,
                   N'@searchtrtype NVARCHAR(255)',   -- or whatever the right type is
                   @searchtrtype=@searchtrtype;

You cannot pass in identifiers, only values, so this doesn't work for @orderbycondition.

Sign up to request clarification or add additional context in comments.

1 Comment

Looks like you had the same concern about @orderbycondition that i did.
1

You need to parametrise your SQL. Concatenating string for SQL is an awful idea; as it leaves you open to injection. The way you want to do it would be:

DECLARE @query nvarchar(MAX);
SET @query =  N'SELECT y.ID,' + NCHAR(10) + 
              N'       y.STATUS,' + NCHAR(10) + 
              N'       y.CONTROLID,' + NCHAR(10) + 
              N'       y.TRCDE' + NCHAR(10) + 
              N'FROM YTDTRNI AS y' + NCHAR(10) + 
              N'     LEFT JOIN VND AS v ON y.COMNO = v.COMNO ' + NCHAR(10) + 
              N'WHERE TRCDE = @dsearchtrtype' + NCHAR(10) + 
              N'ORDER BY ' + QUOTENAME(@orderbycondition) + N';';
PRINT @SQL;

EXEC sp_executesql @query, N'@dsearchtrtype nvarchar(100)', @dsearchtrtype = @searchtrtype;

This, however, probably isn't going to work, due to your variable @orderbycondition. I don't know, however, what type of value that has, however, if it's something like 'ID desc', this it would become ORDER BY [ID desc];.

If this assumption is correct, I would suggest using 2 variables; one for the sort column and one for the direction, and replace the final line of the dynamic SQl with:

N'ORDER BY ' + QUOTENAME(@orderbycolumn) + N' ' + CASE WHEN @orderbydirection NOT IN(N'ASC',N'DESC') THEN N'' ELSE @orderbydirection END + N';';

If your value of @orderbycondition can be more complex than that, post a comment to let me know (and update your question with more detail), and I'll be happy to explain how to create a more dynamic ORDER BY clause using a table type parameter, along with adding it to your dynamic SQL with use of STUFF and FOR XML PATH.

2 Comments

I have tried out your method and I am almost there. When I execute the stored procedure it will return the result set that I want but it also shows an error message: Must declare the scalar variable "@dsearchtrtype".
Sounds like you're trying to reference @dsearchtrtype outside of the dynamic SQL.
0

Based on the example provided it appears that the OP simply wants to filter on a single column and perform a sort on a user specified column. This can be achieved with a parametrized query without the need of dynamic SQL.

Within the where clause the TRCDE field can be directly assigned to the @searchtrtype parameter. The dynamic sorting requirement is a little trickier but can be achieved by using a CASE statement within the ORDER BY clause. An additional parameter will have to be included to indicate if the sorting should be ascending or descending. The following query demonstrates:

DECLARE @orderByColumn  NVARCHAR(50) = 'ID'; -- The column to sort by
DECLARE @sortAscending BIT = 1; -- Indicate sorting order 1 == Ascending, 0 == Descending
DECLARE @searchtrtype NVARCHAR(10) = 'RC'

IF @sortAscending = 1 -- Sort data in ascending order
BEGIN

    SELECT       y.[ID]
                ,y.[STATUS]
                ,y.[CONTROLID]
                ,y.[TRCDE]
    FROM        YTDTRNI AS y 
    LEFT JOIN   VND AS v ON y.[COMNO] = v.[COMNO]
    WHERE       TRCDE = @searchtrtype 
    ORDER BY    (
                    -- Create case statement for every column user may want to filter on
                    CASE
                        WHEN @orderByColumn = 'ID' THEN [ID]
                        ELSE NULL -- Simply return null if we are not ordering by this column, effectively ignore the column.
                    END
                 ),
                 (
                    CASE
                        WHEN @orderByColumn = 'STATUS' THEN [STATUS]
                        ELSE NULL -- Simply return null if we are not ordering by this column, effectively ignore the column.
                    END
                 ),
                             (
                    CASE
                        WHEN @orderByColumn = 'CONTROLID' THEN [CONTROLID]
                        ELSE NULL -- Simply return null if we are not ordering by this column, effectively ignore the column.
                    END
                 ) ASC;
END
ELSE
BEGIN
    SELECT       y.[ID]
                ,y.[STATUS]
                ,y.[CONTROLID]
                ,y.[TRCDE]
    FROM        YTDTRNI AS y 
    LEFT JOIN   VND AS v ON y.[COMNO] = v.[COMNO]
    WHERE       TRCDE = @searchtrtype 
    ORDER BY    (
                    CASE
                        WHEN @orderByColumn = 'ID' THEN [ID]
                        ELSE NULL -- Simply return null if we are not ordering by this column, effectively ignore the column.
                    END
                 ),
                 (
                    CASE
                        WHEN @orderByColumn = 'STATUS' THEN [STATUS]
                        ELSE NULL -- Simply return null if we are not ordering by this column, effectively ignore the column.
                    END
                 ),
                             (
                    CASE
                        WHEN @orderByColumn = 'CONTROLID' THEN [CONTROLID]
                        ELSE NULL -- Simply return null if we are not ordering by this column, effectively ignore the column.
                    END
                 ) DESC;
END

This method is more work but will be easier to troubleshoot, maintain and will protect you from SQL Injection.

Comments

-1

do you just need to include escaped apostrophes. if @searchtrtype can contain apostrophes, you might have to escape them with REPLACE(@searchtrtype, '''', '''''')

SET @query =  N'SELECT y.ID, y.STATUS,y.CONTROLID,y.TRCDE
FROM YTDTRNI AS y LEFT JOIN VND AS v ON y.COMNO = v.COMNO 
WHERE TRCDE = ''' + @searchtrtype + ''' 
ORDER BY ' + @orderbycondition

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.