I'm trying to find the proper syntax to do the following:
SELECT a, b, c FROM foo WHERE foo.id = :id;
(the query above is written as in SQLite).
The "normal" query would be written as:
SELECT a, b, c FROM foo WHERE foo.id = ?;
The first query is written with a named parameter (
:id); the second with unnamed (?).
Searching the internet gives me the syntax for stored procedures and/or functions, but what I am looking for is a SELECT query with a named parameter. When I write my C++ code I will be using BindParameter() ODBC syntax.
For the first query in MS SQL Server, what syntax is correct?
---
As I understand the comment responses, the proper SQL Server syntax is:
SELECT a, b, c FROM foo WHERE foo.id = @id;
This means I will need to declare a variable named @id, bind it to the prepared query, then execute it.
The question is specifically about SQL Server. My question is independent of the client tool.
I am using a plain SELECT query, not a stored procedure or a function. What I am looking for is how to properly write such a query (not procedure or function) for SQL Server so the DB engine will understand it.
---
My full code will look like this:
std::string query = "SELECT a, b, c FROM foo WHERE foo.id = @id;";
SQLExecute( stmt, "DECLARE @id" ... );
SQLPrepare( stmt, query.c_str()... );
SQLBindParameter();
SQLExecute();
Let me give an example of what I'm looking for. If I have a query like the following:
IF NOT EXISTS (
SELECT *
FROM
WHERE table_name = ?
AND table_schema = ?)
INSERT INTO
(table_name, table_schema, param1, param2,...)
VALUES
( ?, ?, ...);
With SQLite and PostgreSQL I can write it like this:
IF NOT EXISTS (
SELECT *
FROM
WHERE table_name = ?
AND table_schema = ?)
INSERT INTO
(table_name, table_schema, param1, param2,...)
VALUES
( ?1, ?2, ...);
Can I do something similar with SQL Server and ODBC? Or I will have to write repeating code for every parameter I'm passing to the query?
In fact, SQLite can refer to a parameter either by name or by number in the query. Not sure if either way exists in SQL Server.
Asked by Igor
(247 rep)
Jan 5, 2022, 03:42 AM
Last activity: Feb 12, 2025, 07:47 AM
Last activity: Feb 12, 2025, 07:47 AM