Parameter issues for stored procedures in SQL database

1. Parameter problem of stored procedure in SQL database

How do you use parameters in a stored procedure in an SQL database as both an output variable and an output variable?

[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 parameters

@out_p,--Output parameters

@in_out_p--Input, output parameters

/*

(No column name) (No column name) (No column name)

1 3 4

*/

2. The parameter problem in the stored procedure.

Here is the question:

[SQL] View plaincopy CREATE TABLE #tableTest (ID int identity, name varchar (), age int,)

Go

INSERT INTO #tableTest

Select ' Xiaoming ', UNION ALL

Select ' Little Red ', union ALL

Select ' Small Army ', 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 equals Xiaoming, 23 years old, and the ID is in (1,3)

--How can I make an optional argument

-for example, name is not an empty time

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

--If the IDs parameter is not empty when the name argument is empty

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

-Excuse me, there are parameters are not empty when the SQL append conditions in the stored procedure, when the empty time is not appended, so with optional parameters stored procedures How to write, and how to call, please help the younger brother write an example

This type of problem is essentially a different query based on the parameters passed in, that is, the query condition behind the where is dynamic.

There are generally 2 ways to deal with, one is to write dynamic statements, but dynamic statements because of the dynamic concatenation of strings, so it is difficult to maintain, and if the stored procedures need to be executed many times, then each need to recompile, but each generation of the implementation plan, should be more optimized. But if the concatenation string part, just a few words, still can use the dynamic statement, below my solution is uses the dynamic statement to realize, the structure is clear, is easy to maintain.

The other is to judge by writing the case after the where statement, the advantage of which is not to dynamically splice the statement, but it is not easy to understand, and not easy to modify, because other people may not be able to understand the meaning of your writing. Another problem is the performance of the problem, because in the original company used this method, after a period of time, the query is very slow, could have been a few seconds of results, then a few minutes will not produce results. To be honest, this method requires a high degree of skill, and error prone, not recommended.

The following is my solution, using a dynamic statement to achieve, but consider the maintenance, testing requirements:

[SQL] View plaincopy--drop table #tableTest

CREATE TABLE #tableTest (ID int identity, name varchar (), age int,)

Go

INSERT INTO #tableTest

Select ' Xiaoming ', UNION ALL

Select ' Little Red ', union ALL

Select ' Small Army ', 27

Go

Select *from #tableTest

Go

Create proc Proctest

@name varchar (=null), @age int = null, @IDs varchar = NULL

As

declare @sql nvarchar (max);

Set @sql = ';

Set @sql = ' SELECT * from #tableTest where 1 = 1 ';

Set @sql = @sql +

Case when @name be not null

Then ' and name like ' + QuoteName (@name + '% ', ' ")

When @age was not null

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

When @ids was not null

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

Else ' "

End

--Print out statements

Select @sql as ' statement '

--EXECUTE 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 IDs in (2,3)

*/

Note: If you encounter multiple and parameter connection queries, the SQL statement can be written as follows

SET @sql = @sql +

Case @SellerNick <> '

THEN ' and Sellernick = ' + @SellerNick + '

ELSE ' "

End

SET @sql = @sql +

Case @LogisticsId <> '

THEN ' and Logisticsid = ' + @LogisticsId + '

ELSE ' "

End