How to pass a parameter into SQL Query?

Question

I have an SQLQuery:

SELECT * FROM (select ROW_NUMBER() OVER( ORDER BY @sortColumns ) AS 'RowNumber'         
FROM MyTable WHERE '@keyword' = '' OR (AppId = '@keyword')) 
atd WHERE (RowNumber between @startRecord and @endRecord);

There 4 parameters in the query:

sortColumns
keyword
startRecord
endRecord

I've used VB.NET language to open a connection and pass values into the query via 4 parameters using Command.Paremeters:

Dim sortColumns = "AppId ASC"
Dim keyword = "abc"
Dim startN As Integer = 1
Dim endN As Integer = 20

Dim ds As New DataSet()
Dim strDatabaseConnectionString As String = ConfigHelper.MainConnectionString
Using connection As New SqlClient.SqlConnection(strDatabaseConnectionString)
    Using cmd As New SqlClient.SqlCommand(query, connection)
        cmd.Parameters.Add("@sortColumns", SqlDbType.NVarChar).Value = sortColumns
        cmd.Parameters.Add("@keyword", SqlDbType.NVarChar).Value = keyword
        cmd.Parameters.Add("@startRecord", SqlDbType.Int).Value = startN
        cmd.Parameters.Add("@endRecord", SqlDbType.Int).Value = endN

        Dim adapter As New SqlClient.SqlDataAdapter(cmd)
        adapter.Fill(ds)
    End Using
End Using

When the code run into line "adapter.Fill(ds)", it generate an exception.

Exception Message: An expression of non-boolean type specified in a context where a condition is expected

My Suspicion:

Whether the way I pass the parameters' values is not correct
We cannot pass a parameter as a column name

Issue: I don't know what wrong with the code that I've used to pass the values into sqlquery. Could you give me a suggestion for this issue?

If we focus on the VB code above, it look like I'm trying to replace some places inside the query. But Command.Parameters does't do a good job in this context. — Trind 07
– Trind 07, Commented May 30, 2016 at 9:18

Jonathan Allen · Accepted Answer · 2016-05-30 21:29:18Z

1

Where you have '@keyword', that literally means the string "@keyword". Remove the single quotes if you want to use the @keyword parameter.

You cannot refer to a column name in a parameter. This is a limitation of SQL Server, as it needs to compile your query before applying parameters.

edited May 30, 2016 at 21:29

answered May 30, 2016 at 9:25

Jonathan Allen

70.6k77 gold badges276 silver badges459 bronze badges

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

1 Comment

Trind 07 Over a year ago

You speak out of what my suspicion is. Somehow we cannot pass a column name there.

TigOldBitties · Accepted Answer · 2016-05-30 09:38:48Z

0

The problem is with the query,

'@keyword' = ''

is not right. Make your query in VB, replacing @keyword in code, not in the sql, so the generated sql is

columnname = ''

answered May 30, 2016 at 9:38

TigOldBitties

1,3359 silver badges15 bronze badges

1 Comment

Trind 07 Over a year ago

Even after I remove the single mark, it still generate the same exception.

shadow · Accepted Answer · 2016-05-30 10:21:15Z

If I'm not wrong, your query will be:

SELECT 
* 
FROM (
        select 
            ROW_NUMBER() OVER( ORDER BY 'AppId ASC' ) AS 'RowNumber'         
        FROM 
            MyTable 
        WHERE 
            (''abc'' = '')
            OR (AppId = ''abc'') ) atd 
WHERE 
    (RowNumber between 1 and 20);

EDIT

After a short research I believe that Sql Server will try to execute this:

exec sp_executesql N'SELECT * FROM (select ROW_NUMBER() OVER( ORDER BY @sortColumns ) AS ''RowNumber'' FROM MyTable WHERE ''@keyword'' = '' OR (AppId = ''@keyword'')) atd WHERE (RowNumber between @startRecord and @endRecord)',
N'@sortColumns nvarchar(9),@keyword nvarchar(3),@startRecord int,@endRecord int',@sortColumns=N'AppId ASC',@keyword=N'abc',@startRecord=1,@endRecord=20

For a query of mine like

select @column from Table where @column = 1

Sql Server executed:

exec sp_executesql N'select @column from Table where @column = 1',N'@column nvarchar(10)',@column=N'contractId'

and produce the error:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'contractId' to data type int.

So you can't pass a column because Sql Server will treat it like string.

I think the ;only solution for this is to construct completely your query from code and execute it.

Something like:

Dim sortColumns = "AppId ASC"
Dim keyword = "abc"
Dim startN As Integer = 1
Dim endN As Integer = 20

Dim sql As String = string.format("SELECT * FROM (select ROW_NUMBER() OVER( ORDER BY {0} ) AS 'RowNumber'         
FROM MyTable WHERE @keyword = '' OR (AppId = @keyword)) 
atd WHERE (RowNumber between @startRecord and @endRecord);", sortColumns)

Andrew Morton · Accepted Answer · 2016-05-30 10:47:54Z

0

You are correct in thinking that you cannot pass a column name as a parameter.

You could create the query in VB instead - if you are using VS2015 then your code could look like

Dim sortColumns = "AppId ASC"
Dim query = $"SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {sortColumns}) AS 'RowNumber'
                             FROM MyTable WHERE @keyword = '' OR AppId = @keyword) AS atd
                             WHERE (RowNumber BETWEEN @startRecord AND @endRecord);"

you shouldn't put quotes around the parameters,
the $ is for an interpolated string (new with VS2015).

For adding parameters, personally I prefer the style:

cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@keyword", .SqlDbType = SqlDbType.NVarChar, .Size = 50, .Value = keyword})
cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@startRecord", .SqlDbType = SqlDbType.Int, .Value = startN})
cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@endRecord", .SqlDbType = SqlDbType.Int, .Value = endN})

(Change the value of the .Size parameter to match the size of the AppId column in the DB.)

I created a table in SQL Server and tested all the above and it worked.

edited May 30, 2016 at 10:47

answered May 30, 2016 at 9:40

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

6 Comments

Trind 07 Over a year ago

Instead of passing sort column into the query using Command.Parameters, I can use other way to pass into the query using String.Format

Trind 07 Over a year ago

There is no "AND" after atd. The query is just an outer select the inner select.

Andrew Morton Over a year ago

@Trind07 Oops! I was confused by the parentheses. Corrected.

Andrew Morton Over a year ago

@Trind07 It is better to use SQL parameters for the values rather than concatenating them in a string, but you have to put the column name in with concatentation.

Trind 07 Over a year ago

I also have tried that. There is still an exception around this vb code. Could you please give me an example of how to pass parameter into select query?

|

Collectives™ on Stack Overflow

How to pass a parameter into SQL Query?

4 Answers 4

1 Comment

1 Comment

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related