SQL Server dynamic SQL

 

Test Table/Data

Create table test_dysql (

Id INT,

Name VARCHAR (10 ),

Val INT

);

Insert into test_dysql (id, name, val) VALUES (1, 'A', 10 );

Insert into test_dysql (id, name, val) VALUES (2, 'B', 20 );

Insert into test_dysql (id, name, val) VALUES (3, 'C', 30 );

Insert into test_dysql (id, name, val) VALUES (4, 'D', 40 );

Insert into test_dysql (id, name, val) VALUES (5, 'E', 50 );

Direct execution: simple execution

1> BEGIN

2>EXECUTE('Select * FROM test_dysql WHERE id = 1 ');

3> END

4> go

Id name val

--------------------------------

1 A 10

(One row is affected)

 

Obtain through cursor

1> BEGIN

2> DECLARE @ SQL varchar (200 );

3> DECLARE @ name varchar (10 );

4> SET @ SQL = 'maid CURSOR for select [name] from test_dysql ';

5>EXEC(@ SQL );

6>

7> OPEN cur;

8> FETCH next FROM cur INTO @ name;

9> WHILE @ FETCH_STATUS = 0

10> BEGIN

11> PRINT @ name;

12> FETCH next FROM cur INTO @ name;

13> END

14> CLOSE cur;

15> DEALLOCATE cur;

16> END

17> go

A

B

C

D

E

Simple execution of Stored Procedures

Note: SP_EXECUTESQL of SQL Server requires the NVARCHAR type when the parameter is set.

Therefore, N is added to the front of the SQL statement.

1> BEGIN

2> EXECSP_EXECUTESQL N'Select * FROM test_dysql WHERE id = 1 ';

3> END

4> go

Id name val

--------------------------------

1 A 10

(One row is affected)

 

Parameter input

1> BEGIN

2> DECLARE @ SQL NVARCHAR (200 );

3> DECLARE @ id INT;

4> DECLARE @ name VARCHAR (10 );

5> SET @ id = 1;

6> SET @ name = 'C ';

7> SET @ SQL = 'select * FROM test_dysql WHERE id = @ id OR name = @ name ';

8> PRINT @ SQL;

9> EXEC SP_EXECUTESQL @ SQL,N' @ id INT, @ name VARCHAR (10) ', @ id, @ name;

10> END

11> go

SELECT * FROM test_dysql WHERE id = @ id OR name = @ name

Id name val

--------------------------------

1 A 10

3 C 30

(2 rows affected)

 

Obtain a single result

SQL Server

Pass

SP_EXECUTESQL

To define the parameters.

OUTPUT plus OUTPUT

The following parameters are the data of specific parameters during execution. OUTPUT plus OUTPUT

1> BEGIN

2> DECLARE @ SQL NVARCHAR (200 );

3> DECLARE @ name VARCHAR (10 );

4> SET @ SQL = 'select @ name = name FROM test_dysql WHERE id = 1 ';

5> PRINT @ SQL;

6> EXEC SP_EXECUTESQL @ SQL,N' @ name VARCHAR (10) output', @ name OUTPUT;

7> PRINT @ name;

8> END

9> go

SELECT @ name = name FROM test_dysql WHERE id = 1

A