1.一个简单的存储过程(可动态实现表名的检索)
2.目录结构的父子关系打印(存储过程递归调用)
3.在存储过程中使用形参(OUTPUT类型参数)
4.将制定表的列名转化为字符串(COL1,COL2,COL3)
5.变横向表为纵向表
1.一个简单的存储过程(可动态实现表名的检索)
ALTER PROCEDUR DoSelect(
@tbname varchar(20))
AS
declare @sql varchar(100)
set @sql='SELECT * FROM '+@tbname
exec (@sql)
注意此处的AS后面没有BEGIN关键字(SQL SERVER2000)
2.目录结构的父子关系打印(存储过程递归调用)
A.执行SQL文:
if object_id(N'tt','U') is not null
drop table tt
go
create table tt(id int primary key, name nvarchar(100), father int, layer int)
go
insert tt values(1,'a',null,0);
insert tt values(2,'aa',1,1);
insert tt values(3,'ab',1,1);
insert tt values(4,'aaa',2,2);
insert tt values(5,'aba',3,2);
insert tt values(6,'abaC',5,3);
if object_id(N'fun','fn') is not null
drop function fun
go
create function fun(@id int)
returns nvarchar(1000)
as
begin
declare @layer int,@name nvarchar(100),@father int
declare @result nvarchar(1000)
select @layer = layer, @name=name, @father=father from dbo.tt where id=@id;
if @layer = 0
set @result = '/' + @name;
else
set @result = dbo.fun(@father) + '/' + @name;
return @result
end
go
select * from tt
select id,path=dbo.fun(id) from tt
DROP table tt
执行结果:
id name father layer
1 a NULL 0
2 aa 1 1
3 ab 1 1
4 aaa 2 2
5 aba 3 2
6 abaC 5 3
id path
1 /a
2 /a/aa
3 /a/ab
4 /a/aa/aaa
5 /a/ab/aba
6 /a/ab/aba/abaC
3.在存储过程中使用形参(OUTPUT类型参数)
A.SQL文:
CREATE PROCEDURE myProc
@outparm int OUTPUT,
@inparm int
AS
set @outparm=100
set @inparm=200
return 300
GO
DECLARE @outparm INT
DECLARE @inparm INT
DECLARE @TMP INT
SET @outparm=0
SET @inparm=0
SET @TMP=0
EXEC @TMP=myProc @outparm OUTPUT,@inparm
PRINT '结果:'
PRINT @TMP
PRINT @outparm
PRINT @inparm
DROP PROCEDURE myProc
B.结果:
结果:
300
100
0
4.将制定表的列名转化为字符串(COL1,COL2,COL3)
CREATE FUNCTION TableColsNameToString
(@TABLE_NAME VARCHAR(20))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @total_Cols INT
DECLARE @str_ColName VARCHAR(10)
DECLARE @str_Result VARCHAR(1000)
IF EXISTS(SELECT * FROM sysobjects WHERE name = @TABLE_NAME)
BEGIN
-- Create an unnamed cursor.
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT name FROM syscolumns WHERE id=object_id(@TABLE_NAME)
OPEN @MyCursor
FETCH NEXT FROM @MyCursor INTO @str_ColName
SET @str_Result=@str_ColName
WHILE @@fetch_status=0
BEGIN
FETCH NEXT FROM @MyCursor INTO @str_ColName
SET @str_Result=@str_Result+','+@str_ColName
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
END
RETURN @str_Result
END
5.变横向表为纵向表
A.实现SQL文:
--Declare variables
DECLARE @SQL VARCHAR(5000)
DECLARE @total INT
DECLARE @sel_result VARCHAR(3000)
--Create table for test
DECLARE @T TABLE( A INT, B INT, C INT)
DECLARE @cnt int
DECLARE @count int
SET @cnt=1
SET @count=1
WHILE @cnt<100
BEGIN
INSERT INTO @T SELECT @count,@count+1,@count+2
SET @cnt=@cnt+1
SET @count=@count+3
END
--Get the total rows about test table
SELECT @total=COUNT(*) FROM @T
EXEC CreateTableByCol 'Temp_Table',@total
--Do data insert
SET @sel_result=''
SELECT @sel_result=@sel_result+',' + CAST(A AS VARCHAR) FROM @T
SELECT @sel_result = STUFF(@sel_result, 1, 1, '')
SET @SQL='INSERT INTO Temp_Table VALUES('+@sel_result+')'
EXEC(@SQL)
SET @sel_result=''
SELECT @sel_result=@sel_result+',' + CAST(B AS VARCHAR) FROM @T
SELECT @sel_result = STUFF(@sel_result, 1, 1, '')
SET @SQL='INSERT INTO Temp_Table VALUES('+@sel_result+')'
EXEC(@SQL)
SET @sel_result=''
SELECT @sel_result=@sel_result+',' + CAST(C AS VARCHAR) FROM @T
SELECT @sel_result = STUFF(@sel_result, 1, 1, '')
SET @SQL='INSERT INTO Temp_Table VALUES('+@sel_result+')'
EXEC(@SQL)
--Print the result
SELECT * FROM @T
SELECT * FROM Temp_Table
DROP TABLE Temp_Table
B.创建临时表
CREATE PROCEDURE CreateTableByCol
(@TABLE_NAME VARCHAR(10),
@col_cnt int)
AS
BEGIN
DECLARE @SQL VARCHAR(5000)
DECLARE @SQL_COL VARCHAR(3000)
DECLARE @count INT
SET @count=1
IF EXISTS(SELECT * FROM sysobjects WHERE name = @TABLE_NAME)
BEGIN
SET @SQL='DROP TABLE '+@TABLE_NAME
EXEC(@SQL)
END
--SET @TABLE_NAME='TEST'+CAST(@col_cnt AS VARCHAR)
SET @SQL='CREATE TABLE '+@TABLE_NAME
SET @SQL_COL=' '
WHILE @count <= @col_cnt
BEGIN
SET @SQL_COL=@SQL_COL+'COL'+CAST(@count AS VARCHAR)+' INT,'
SET @count = @count+1
END
SET @SQL_COL=SUBSTRING(@SQL_COL,0,LEN(@SQL_COL))
--SET @SQL=@SQL+'('+'COUNT INT,'+@SQL_COL+')'
SET @SQL=@SQL+'('+@SQL_COL+')'
EXEC(@SQL)
END