如何在SQL SELECT查询中赋值与添加字符串变量？附示例

Hey there! Let's break down how to work with your string variables (tw.local.subBarCode and tw.local.caseID) in the SQL query you're building.

1. Concatenating String Variables into the WHERE Clause #

Since your variables hold string values, you must wrap them in single quotes when adding them to the WHERE clause—SQL requires all string literals to be enclosed in single quotes to distinguish them from column names.

Here's how to finish your existing SQL statement correctly:

tw.local.sql="select CS.ORIGINALCHECK_ID AS checkId, "+
"CS.CHECKTYPE_ID AS checkTypeId, "+
"CS.CHECKTYPE_ID AS componentID, "+
"CT.NAME AS componentName "+
"from CHECK_SUMMARY CS INNER JOIN CHECKTYPE CT "+
"ON CT.ID=CS.CHECKTYPE_ID "+
"where CS.ORIGINALCHECK_ID='"+tw.local.caseID+"' "+
"AND CS.SUBBARCODE='"+tw.local.subBarCode+"'";

Pro tip: The single quotes around tw.local.caseID and tw.local.subBarCode are non-negotiable—skip them, and SQL will throw an error or treat your values as invalid column references.

2. Assigning String Variables in the SELECT Clause #

You can also inject these string variables directly into your query's result set to return them as part of the output. Here are two common use cases:

a. Return the variable as a standalone column #

Add a new column to your SELECT list that outputs the raw value of your string variable:

tw.local.sql="select CS.ORIGINALCHECK_ID AS checkId, "+
"CS.CHECKTYPE_ID AS checkTypeId, "+
"CS.CHECKTYPE_ID AS componentID, "+
"CT.NAME AS componentName, "+
"'" + tw.local.subBarCode + "' AS subBarCodeValue, "+ // Returns subBarCode as a dedicated column
"'" + tw.local.caseID + "' AS caseIdValue "+ // Returns caseID as a dedicated column
"from CHECK_SUMMARY CS INNER JOIN CHECKTYPE CT "+
"ON CT.ID=CS.CHECKTYPE_ID "+
"where CS.ORIGINALCHECK_ID='"+tw.local.caseID+"'";

b. Combine variables with static text or column values #

If you want to merge your variable with fixed text or existing column data, use your database's string concatenation tool (like CONCAT() for MySQL/SQL Server, or || for Oracle). For example:

tw.local.sql="select CS.ORIGINALCHECK_ID AS checkId, "+
"CS.CHECKTYPE_ID AS checkTypeId, "+
"CS.CHECKTYPE_ID AS componentID, "+
"CT.NAME AS componentName, "+
"CONCAT('Associated Case: ', '" + tw.local.caseID + "') AS caseDetails, "+ // Static text + variable
"CONCAT(CT.NAME, ' - Barcode: ', '" + tw.local.subBarCode + "') AS componentFullInfo "+ // Column value + variable
"from CHECK_SUMMARY CS INNER JOIN CHECKTYPE CT "+
"ON CT.ID=CS.CHECKTYPE_ID "+
"where CS.ORIGINALCHECK_ID='"+tw.local.caseID+"'";

Critical Note: Avoid SQL Injection #

Direct string concatenation like this carries a risk of SQL injection if your variables might contain untrusted input (e.g., user-entered text). If your platform supports parameterized queries (using placeholders like ? or @variable), that's always the safer route. For example:

// Hypothetical parameterized example (varies by platform)
tw.local.sql="select CS.ORIGINALCHECK_ID AS checkId, ... where CS.ORIGINALCHECK_ID=? AND CS.SUBBARCODE=?";
tw.local.parameters = [tw.local.caseID, tw.local.subBarCode];

If parameterization isn't an option, make sure to sanitize your variables first (e.g., escape single quotes) to block malicious input.

内容的提问来源于stack exchange，提问作者kranti