0

I have the following code:

Dim sqlQuery As String = "IF NOT EXISTS ( SELECT * FROM myTable WHERE columnId = @columnId AND sourceId = @sourceId AND value = @value ) "
sqlQuery += "BEGIN "
sqlQuery += "INSERT INTO myTable SELECT @columnId, @sourceId, @value, " & data & " FROM otherTable WHERE sourceId = @sourceId AND @query END"

Using cn As New SqlConnection(connectStr), _
    cmd As New SqlCommand(sqlQuery, cn)

    cmd.Parameters.Add("@columnId", Data.SqlDbType.Int).Value = columnId
    cmd.Parameters.Add("@sourceId", Data.SqlDbType.Int).Value = sourceId
    cmd.Parameters.Add("@value", Data.SqlDbType.NVarChar).Value = value
    cmd.Parameters.Add("@query", Data.SqlDbType.NVarChar).Value = query

    cn.Open()
    cmd.ExecuteNonQuery()
End Using

All of the other parameterized queries work except for the @query. I get an exception that says "An expression of non-boolean type specified in a context where a condition is expected, near 'END'." I'm not sure what I'm doing wrong. Using SQL Server 2008 R2.

kevlar90
  • 820
  • 2
  • 10
  • 14
  • What sorts of values are you sending to @query? – Kyle Hale May 12 '14 at 16:52
  • Things like "category = 1" or "country = US" – kevlar90 May 12 '14 at 16:55
  • Well, country = US would need to be country = 'US' (for example). Basically that error is saying your SQL syntax in sqlQuery is invalid, so perhaps posting the malformed value of sqlQuery could help us (or you) debug it. – Kyle Hale May 12 '14 at 17:02
  • Actually, I think your problem is @query can be blank and so your query looks like "IF NOT() BEGIN SELECT * FROM TABLE WHERE A=1 AND END" and "AND END" throws an error. – Kyle Hale May 12 '14 at 17:04
  • Yeah I realized the country = US syntax was incorrect after I commented, it was just an example nothing ever gets passed in like that. Also, I have a check to see if @query is empty and it does not attempt it if it is empty. The query gives me this error every time. I have executed the query in management studio, replacing the parameterized queries with the actual values I pass in and it works fine. – kevlar90 May 12 '14 at 17:10
  • 1
    That's not exactly a rigorous testing method. Please post the value of sqlQuery when this errors. – Kyle Hale May 12 '14 at 17:12

1 Answers1

1

AND @Query indicates the contents of @Query must be resolvable to a boolean condition.

For instance,

DECLARE @Query NVARCHAR(MAX);
DECLARE @cmd NVARCHAR(MAX);
SET @Query = '''TEST'' = ''MONKEY''';
SET @cmd = 'SELECT 1 WHERE ' + @Query + ';';

EXEC sp_executesql @cmd;

GO

DECLARE @Query NVARCHAR(MAX);
DECLARE @cmd NVARCHAR(MAX);
SET @Query = '''TEST''';
SET @cmd = 'SELECT 1 WHERE ' + @Query + ';';

EXEC sp_executesql @cmd;

The first query is valid, since we are checking to see if 'TEST' = 'MONKEY'; however the second query is invalid since we are checking to see if 'TEST' without specifying what we are comparing 'TEST' to.

Hannah Vernon
  • 3,367
  • 1
  • 26
  • 48