表a
cno year cs
001 2005 15
001 2005 16
001 2006 13
002 2008 12
002 2009 13
003 2007 18
表b
cno year cs
001 2005 15
001 2005 16
001 2006 13
002 2008 12
002 2009 13
003 2007 18想通过一个存储过程来检索表中的记录
带三个参数
exec proc_name a,001,2008
exec proc_name b,001,2008应该怎么样写
cno year cs
001 2005 15
001 2005 16
001 2006 13
002 2008 12
002 2009 13
003 2007 18
表b
cno year cs
001 2005 15
001 2005 16
001 2006 13
002 2008 12
002 2009 13
003 2007 18想通过一个存储过程来检索表中的记录
带三个参数
exec proc_name a,001,2008
exec proc_name b,001,2008应该怎么样写
cno year cs
001 2005 15
001 2005 16
001 2006 13
002 2008 12
002 2009 13
003 2007 18
表b
cno year cs
001 2005 15
001 2005 16
001 2006 13
002 2008 12
002 2009 13
003 2007 18
表a和表b结构完全一致,只是表名不同通过存储过程 运行exec proc_name a,001,2008 得到a表中cno=001 year=2008的所有记录
运行exec proc_name b,002,2005 得到a表中cno=002 year=2005的所有记录
@tb varchar(10),
@cno varchar(10),
@year int
as
exec('select * from '+@tb+' where cno='''+@cno+''' and year='+@year)
@tb varchar(10),
@cno varchar(10),
@year int
as
exec('select * from '+@tb+' where cno='''+@cno+''' and year='+@year)
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-13 21:37:02
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:aIF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a]([cno] NVARCHAR(10),[year] INT,[cs] INT)
INSERT [a]
SELECT '001',2005,15 UNION ALL
SELECT '001',2005,16 UNION ALL
SELECT '001',2006,13 UNION ALL
SELECT '002',2008,12 UNION ALL
SELECT '002',2009,13 UNION ALL
SELECT '003',2007,18
GO
--SELECT * FROM [a]--> 生成测试数据表:bIF OBJECT_ID('[b]') IS NOT NULL
DROP TABLE [b]
GO
CREATE TABLE [b]([cno] NVARCHAR(10),[year] INT,[cs] INT)
INSERT [b]
SELECT '001',2005,15 UNION ALL
SELECT '001',2005,16 UNION ALL
SELECT '001',2006,13 UNION ALL
SELECT '002',2008,12 UNION ALL
SELECT '002',2009,13 UNION ALL
SELECT '003',2007,18
GO
--SELECT * FROM [b]-->SQL查询如下:
create proc myproc
@tb varchar(10),
@cno varchar(10),
@year varchar(4)
as
exec('select * from '+@tb+' where cno='+@cno+' and [year]='+@year)
go
--调用:
exec myproc 'a',002,2008
/*
cno year cs
---------- ----------- -----------
002 2008 12(1 行受影响)*/
exec myproc 'b',001,2005
/*
cno year cs
---------- ----------- -----------
001 2005 15
001 2005 16(2 行受影响)
*/
as
exec ('select * from '+@tb+' where cno='''+@cno+''' and [year]='+@year)
go
@type varchar(10),
@cno varchar(10),
@year varchar(10)
as
select * from (
select *,[type]='a' from a
union all
select *,[type]='b' from b)t
where [type]=@type and cno=@cno and year=@year
go
exec proc_name 'a','001','2008'