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:
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.
