存储过程实现集

关注

转载最新推荐文章于 2026-09-03 13:21:39 发布 414 阅读 AI 写同款· GEO 优化 ›

JAVA调用sqlserver存储过程的实现（返回多个结果集的实现） 1、存储过程 USE [YZC_BI] GO /****** Object: StoredProcedure [dbo].[CalYlfwColumnData] Script Date: 08/20/2014 09:24:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ===================== 阅读详情

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

收录于