String and variables in SQL query

Question

I know that in C#, we can use variable in a string.

For example:

int num = 3;
string str = "hello, I have " + num + " apple";

How are we going to do that in an insert statement in a SQL query?

Let's say I have 2 columns in my table and they are name and remarks, and I want to write an insert statement in my stored procedure.

For example:

Declare @emp_no int;

Insert into employee (name, remarks) 
Values ('Joe', 'Employee's number' + @emp_no)

I am wondering if I can do something like this in my query?

Parameterized query is always prefered than standard string operation. Related: http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements — Thariq Nugrohotomo, Apr 12 '16 at 01:43
@Shnugo, Sorry, I forgot to accept the answer, thanks for your help, problem solved :) — RedRocket, Apr 14 '16 at 03:00

score 3 · Accepted Answer · answered Apr 12 '16 at 01:46

In this part there are several flaws:

Declare @emp_no int;
    Insert into employee (name, remarks) Values ('Joe', 'Employee's number' + @emp_no)

One after the other...

You declare your @emp_no as int but there is no value assigned... If you concatenate SQL Strings and there is one of them NULL, the whole string will be NULL... So better use DECLARE @emp_no INT=1 or something like DECLARE @emp_no INT; SET @emp_no=(SELECT EmpNo FROM Employee WHERE Something unique is true)
The single qoute in "Employee's number" will break the string you want to set. Within a SQL String you have to double the qoutes.
in SQL Server - other as in C# - you cannot conactenate a string and an int without a cast
If there's no space behind the word "number", the employee's number will sit close to the word...

You might try something like this:

Declare @emp_no int=1;
    Insert into employee (name, remarks) 
    Values ('Joe', 'Employee''s number ' + CAST(@emp_no AS VARCHAR(10)))

1 Answers1