SQL database stored procedure parameters

1. SQL database stored procedure parameters

How can I use parameters in the stored procedures of SQL database as both output variables and output variables?

[SQL] view plaincopy -- drop proc proc_test

-- Go

Create proc dbo. proc_test

@ In int,

@ Out int out,

@ In_out int output

As

Select @ out = @ in + @ in_out, -- 1 + 2 = 3

@ In_out = @ out + 1 -- 3 + 1 = 4

Go

Declare @ in_p int

Declare @ out_p int

Declare @ in_out_p int

Set @ in_p = 1;

Set @ in_out_p = 2

Exec dbo. proc_test @ in_p,

@ Out_p out,

@ In_out_p output

Select @ in_p, -- input parameter

@ Out_p, -- output parameter

@ In_out_p -- Input and Output Parameters

/*

(No column name)

1 3 4

*/

2. Parameters in the stored procedure.

The following are the issues:

[SQL] view plaincopy create table # tableTest (id int identity, name varchar (20), age int ,)

Go

Insert into # tableTest

Select 'xiaoming ', 23 union all

Select 'small red', 28 union all

Select 'xiaojun ', 27

Go

Select * from # tableTest

Go

Create proc procTest

@ Name varchar (20 ),

@ Age int,

@ IDs varchar (30)

As

Begin

Select * from # tableTest where 1 = 1

End

-- When I pass in the @ name parameter that is equal to Xiaoming, 23 years old, and the ID is (1, 3)

-- How can I create optional parameters?

-- For example, when name is not empty

Select * from # tableTest where 1 = 1 and name like 'xiaoming'

-- If the name parameter is null and the IDs parameter is not empty

Select * from # tableTest where 1 = 1 and id in (1, 3)

-- If the parameter is not blank, the SQL append condition in the stored procedure is not added when the parameter is not blank. In this way, how to write and call the stored procedure with optional parameters, please help me write an instance

In essence, different queries are performed based on different input parameters, that is, the query conditions after where are dynamic.

Generally, there are two processing methods. One is to write dynamic statements, but dynamic statements are difficult to maintain because they are dynamically concatenated strings. If the stored procedure needs to be executed multiple times, the re-compilation is required each time, but the execution plan generated each time should be relatively optimized. However, if you splice a few strings, you can still use dynamic statements. The following solution is implemented using dynamic statements. The structure is clear and easy to maintain.

Another method is to write case when after the where statement to determine whether to use it. The advantage of this method is that it does not need to dynamically splice statements, but is not easy to understand or modify, because others may not understand what you mean. Another problem is the performance problem, because the original company used this method. After a while, the query was very slow, and the results could be produced in a few seconds, but the results could not be returned in a few minutes. To be honest, this method requires a high level of skill and is prone to errors. It is not recommended.

The following is my solution, which is implemented using dynamic statements, but the maintenance and testing requirements are taken into account:

[SQL] view plaincopy -- drop table # tableTest

Create table # tableTest (id int identity, name varchar (20), age int ,)

Go

Insert into # tableTest

Select 'xiaoming ', 23 union all

Select 'small red', 28 union all

Select 'xiaojun ', 27

Go

Select * from # tableTest

Go

Create proc procTest

@ Name varchar (20) = null, @ age int = null, @ IDs varchar (30) = null

As

Declare @ SQL nvarchar (max );

Set @ SQL = '';

Set @ SQL = 'select * from # tableTest where 1 = 1 ';

Set @ SQL = @ SQL +

Case when @ name is not null

Then' and name like '+ QUOTENAME (@ name +' % ','''')

When @ age is not null

Then' and age = '+ cast (@ age AS varchar)

When @ ids Is not null

Then' and id in ('+ @ ids + ')'

Else''

End

-- Print out the statement

Select @ SQL as 'Statement'

-- Execute the statement

-- Exec (@ SQL)

Go

Exec procTest

/*

Statement

Select * from # tableTest where 1 = 1

*/

Exec procTest 'xiaoming ', 23

/*

Statement

Select * from # tableTest where 1 = 1 and name like 'xiaoming %'

*/

Exec procTest @ ids = '2, 3'

/*

Statement

Select * from # tableTest where 1 = 1 and id in (2, 3)

*/

NOTE: If multiple and parameters are required for connection query, the SQL statement can be written as follows:

SET @ SQL = @ SQL +

Case when @ SellerNick <>''

Then' and SellerNick = '+ @ SellerNick +''

ELSE''

END

SET @ SQL = @ SQL +

Case when @ LogisticsId <>''

Then' and LogisticsId = '+ @ LogisticsId +''

ELSE''

END