存储过程实现集

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