Declare variable string
In SQL Server, you can declare and initialize a string variable using the DECLARE statement along with the VARCHAR or NVARCHAR data type. Here's an example of how to declare a string variable in SQL Server:
-- Declare a VARCHAR variable
DECLARE @myVariable VARCHAR(50);
-- Declare an NVARCHAR variable
DECLARE @myNVariable NVARCHAR(50);
-- Initialize the VARCHAR variable
SET @myVariable = 'Hello, SQL Server!';
-- Initialize the NVARCHAR variable
SET @myNVariable = N'Unicode string';
-- Display the values of the variables
SELECT @myVariable AS MyVarcharVariable, @myNVariable AS MyNvarcharVariable;
Let's break down the code:
DECLARE @myVariable VARCHAR(50);: This line declares a VARCHAR variable named @myVariable with a maximum length of 50 characters. You can adjust the length according to your requirements.
DECLARE @myNVariable NVARCHAR(50);: Similarly, this line declares an NVARCHAR variable named @myNVariable with a maximum length of 50 characters. NVARCHAR is used for storing Unicode characters.
SET @myVariable = 'Hello, SQL Server!';: This line initializes the VARCHAR variable with the string 'Hello, SQL Server!'. Make sure to enclose the string in single quotes.
SET @myNVariable = N'Unicode string';: Here, the NVARCHAR variable is initialized with the Unicode string 'Unicode string'. The N prefix is used to indicate a Unicode string.
SELECT @myVariable AS MyVarcharVariable, @myNVariable AS MyNvarcharVariable;: This line displays the values of the variables using the SELECT statement.
Example
To declare a string variable, use the DECLARE keyword, then type the @variable_name and variable type: char, varchar.
To assign a value to a variable, use the keyword SET.
The CHAR is a data type with fixed length and loads empty spaces. To remove the empty spaces uses functions LTRIM(remove leading spaces) and RTRIM(remove trailing spaces).
The VARCHAR is a data type with variable length and uses only the length loaded with characters.
USE model;
GO
DECLARE @char AS char(10);
DECLARE @varchar AS varchar(10);
SET @char = 'test 1';
SET @varchar = 'test 2';
PRINT LTRIM(RTRIM(@char)) + ', ' + @varchar;
GO
Result:
test 1, test 2
Declare variable varchar
USE model;
GO
DECLARE @myString AS varchar(4000);
SET @myString = 'select name, description from Certifications where id in (2,3)';
EXECUTE(@myString);
GO
Result:
| name | description |
|---|---|
| SQL certification | Basic of SQL |
| T-SQL certification | Basic of T-SQL |
Keep in mind that the actual maximum length of the string may be limited by the database column or the operation you perform with the variable. If you are working with column values, ensure that the variable's length matches the column's length to avoid data truncation issues.