IF EXISTS (
SELECT
*
FROM
Teacher
WHERE
SAL < 1200)
BEGIN
SELECT
NO
,
NAME
,SAL
FROM
Teacher
WHERE
SAL <1200
SELECT
'以上教师工资<1200,需考虑加薪'
提示
END
ELSE
PRINT
'所有教师工资在计以上,暂时不考虑加薪'
IF DB_ID(N
'Mytest'
)
IS
NOT
NULL
BEGIN
PRINT
'数据库存在'
IF OBJECT_ID(
'Mytest..Teacher'
,
'U'
)
IS
NOT
NULL
PRINT
'数据表存在'
ELSE
PRINT
'数据表不存在'
END
ELSE
PRINT
'数据库不存在'
DECLARE
@I
INT
, @
SUM
INT
SELECT
@I=1, @
SUM
=0
WHILE @I < 10
SELECT
@
SUM
= @
SUM
+ @I, @I = @I + 1
PRINT @
SUM
USE Mytest
GO
CREATE
PROC TEST1
WITH
RECOMPILE
AS
SELECT
*
FROM
Teacher
DROP
PROC TEST1
EXEC
TEST1
EXEC
sp_helptext TEST1
EXEC
sp_help TEST4
USE Mytest
GO
CREATE
PROC TEST2
@TNAME
VARCHAR
(12)
AS
IF(@TNAME
IS
NULL
)
BEGIN
RAISERROR(
'NULL VALUE ARE INVALID'
,5,5)
RETURN
END
ELSE
BEGIN
SELECT
*
FROM
Teacher
WHERE
Tname = @TNAME
RETURN
END
DROP
PROC TEST2
DECLARE
@RETURN_VALUE
INT
EXEC
@RETURN_VALUE=TEST2
'李'
SELECT
@RETURN_VALUE
USE Mytest
GO
CREATE
PROC TEST3
@TNAME
VARCHAR
(12)
OUTPUT
AS
BEGIN
SELECT
@TNAME = Tname
FROM
Teacher
WHERE
T# = 50050
END
DROP
PROC TEST3
DECLARE
@
NAME
VARCHAR
(12)
EXEC
TEST3 @
NAME
OUTPUT
SELECT
@
NAME
USE Mytest
GO
CREATE
PROC TEST4
@TNAME
VARCHAR
(12),
@
NAME
VARCHAR
(12)
OUTPUT
AS
BEGIN
UPDATE
Teacher
SET
Tname = @TNAME
WHERE
T# = 50050
SELECT
@
NAME
= Tname
FROM
Teacher
WHERE
Tname = @TNAME
END
DROP
PROC TEST4
DECLARE
@tNAME
VARCHAR
(12)
EXEC
TEST4
'李'
,@tNAME
OUTPUT
SELECT
@tNAME
CREATE
PROC SP_CREATE_TABLE
@TableName SYSNAME
AS
EXEC
(
'CREATE TABLE '
+@TableName+
' (column1 varchar(100), column2 varchar(100))'
);
GO
DROP
PROC SP_CREATE_TABLE
EXEC
SP_CREATE_TABLE testT
DROP
TABLE
testT
CREATE
PROC SP_RENAME_TABLE
@OLD_TABLE_NAME SYSNAME,
@NEW_TABLE_NAME SYSNAME
AS
IF EXISTS(
SELECT
*
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA =
'dbo'
AND
TABLE_TYPE =
'BASE TABLE'
AND
TABLE_NAME = @OLD_TABLE_NAME)
BEGIN
EXEC
sp_rename @OLD_TABLE_NAME, @NEW_TABLE_NAME
END
DROP
PROC SP_RENAME_TABLE
EXEC
SP_RENAME_TABLE
'testT'
,
'testTable'