SQL stored procedure BASICS (variables)

In the previous article, we talked about the basic definition of SQL stored procedures, how to create and use them. This article describes how to use variables.

Variable: local variable and global variable

The local variable starts with @ and the global variable starts with @. Here we will mainly talk about local variables. We will talk about global variables later.

In c #, it is easy to define a variable, for example

Copy codeThe Code is as follows: int I = 0; -- defines adding a value.

In SQL, the keyword DECLARE is required to define a variable, and a special symbol mark (@) is required to indicate that it is a variable.

Let's look at the simple declaration Syntax:

Copy codeThe Code is as follows: Declare @ Local_Var data_type

@ Local_Var is a whole, indicating a variable.

Data_type is the data type, which everyone is familiar with, such as int, decimal, float, and text.

How can I assign values when declaring variables? Like this

Copy codeThe Code is as follows: declare @ ID = 2 varchar (20 );
This is not acceptable, but it does.
Copy codeThe Code is as follows:
Declare @ ID varchar (20) = 2
Print @ ID -- this statement indicates that the variable value is printed in the SQL server window.
The result is correct.
---------
2

The statement can be assigned a value. After the statement is declared, the value can be assigned again,
There are two ways to assign values.
Set, select, first look at the basic usage, and then the difference

I. Basic usage

Copy codeThe Code is as follows:
Declare @ ID varchar (20) -- defines a variable named @ ID
Set @ ID = 3 -- the variable value is 3.
Print @ ID -- print
Select @ ID = 1 -- the variable value is 1.
Print @ ID -- print

View results
-------------

From the above we can see that both Set and select can assign values to variables.

Then let's take a look at the variable operation. The operation is actually very simple. Next let's look at addition and subtraction.

Copy codeThe Code is as follows:
Declare @ ID varchar (20)
Set @ ID = 3
Print @ ID
Select @ ID = 1 + @ ID -- add the variable @ id to 1
Print @ ID
Select @ ID = (select 1 + 5) -- similar to @ ID = 1 + 5
Print @ ID
Select @ ID = (select 1-@ ID) -- similar to @ ID = 1-@ ID
Print @ ID

Result
-----------

3
4
6
-5

Let's look at multiplication and division.

Copy codeThe Code is as follows:
Declare @ ID int
Set @ ID = 3
Print @ ID
Select @ ID = 2 * @ ID -- multiply by 2
Print @ ID
Select @ ID = (@ ID/2) -- divide by 2
Print @ ID
Select @ ID = (@ ID * @ ID) -- Multiplication
Print @ ID

Result
-----------
3
6
3
9

Finally, let's take a look at the modulo operation %

Copy codeThe Code is as follows:
Declare @ ID int
Set @ ID = (10% 3)
Print @ ID
Select @ ID = (10% 2)
Print @ ID

Result
---------

Ii. Differences

1. When the expression returns multiple values

Copy codeThe Code is as follows:
When the expression returns multiple values, use SET to assign values.

Declare @ name varchar (128)
Set @ name = (select username from userinfo)
Print @ name
/*
-- The error message is
Server: Message 512, level 16, status 1, Row 2
The subquery returns more than one value. When the subquery follows in = ,! =, <, <=,>,> =, Or use a subquery as an expression.
*/

When the expression returns multiple values, use SELECT to assign values.
Declare @ name varchar (20)
Select @ name = username from userinfo
Print @ name -- Value of the last username column in The result set
Result:
---------
Wangwu

2. When the expression does not return a value

Copy codeThe Code is as follows:
-- If the expression does not return a value, use SET to assign a value.
Declare @ name varchar (20)
Set @ name = 'jack'
Set @ name = (select username from userinfo where username = 'not ')
Print @ name -- Null Value

Result
--------

-- If the expression does not return a value, use SELECT to assign a value.
Declare @ name varchar (20)
Set @ name = 'jack'
Select @ name = username from userinfo where username = 'not'
Print @ name -- jack, save the original value

Result
-------
Jack

Here is a summary.

The following table lists the differences between SET and SELECT.

Condition	Set	Select
The expression returns multiple values.	Error	Assign the last returned value to the variable.
The expression does not return a value.	The variable is assigned a null value.	Keep original variable value

Here is a comprehensive example.
Copy codeThe Code is as follows:
Create procedure UserLogin1 -- CREATE a stored PROCEDURE to determine Logon
@ Name varchar (20 ),
@ Password varchar (20 ),
@ InReturn int output -- output parameter

AS
-- Define a variable to save the password.
Declare @ strPwd varchar (20)
BEGIN

Select @ strPwd = userPass from userinfo where userName = @ name -- assign a value to the variable @ strPwd Through select
If (@ password = @ strPwd)
Begin
Set @ inReturn = 1
-- Assume that the update time is used.
Update userinfo set registertime = getdate () where userName = @ name
End
Else
Set @ inReturn =-1 -- if the password is incorrect,-1 is returned.
END
GO

-- Test Method
Declare @ test int
Exec UserLogin1 'admin', 'admin', @ test output -- if no output keyword exists, the value of @ test is NULL.
Print @ test
Result:
-------------

(One row is affected)
1
-- Or call it like this:
Declare @ test int
EXEC UserLogin1 @ name = 'admin', @ password = 'admin', @ inReturn = @ test output
Print @ test

-- The result is the same as above

-- If the logon is incorrect

Declare @ test int
EXEC UserLogin1 @ name = 'admin1', @ password = 'admin', @ inReturn = @ test output
Print @ test results
-------------
-1 -- the value here is the value set in the stored procedure. If it fails,-1 is returned, which is the role of output.