这样做:
if (exists (select name
from sysobjects
where (name = N'up_DeleteRepeat') and (type='P')))
drop procedure up_DeleteRepeat
go
CREATE PROCEDURE [dbo].up_DeleteRepeat
@tableName varchar (50)
AS
declare @sql varchar(500)
BEGIN
set @sql='select * from '+@tableName
exec(@sql)
END
if (exists (select name
from sysobjects
where (name = N'up_DeleteRepeat') and (type='P')))
drop procedure up_DeleteRepeat
go
CREATE PROCEDURE [dbo].up_DeleteRepeat
@tableName varchar (50)
AS
declare @sql varchar(500)
BEGIN
set @sql='select * from '+@tableName
exec(@sql)
END
解决方案 »
- Sql表格设计问题
- 请问如何让数据库全文索引,支持数字和字母查询,CONTAINS(column,'SearchKey')
- 使用inner join查询的结果如何保证null的项目也能被正确显示为null?
- SQLSERVER的远程连接问题
- SQL Server 2008 视图高手请进
- SQL SERVER中如何完成定时任务?
- 如何模仿日历表
- SQL客户端和之间服务器的问题
- 征集sql语句!!!!!20分!!!
- ###又有谁知道将ms sql的存储过程转为oracle存储过程的工具###
- access的upsizing问题(导出为sql server)
- 请问如何在触发器里获得当前被修改的记录某个字段的值
@tableName varchar (50)
AS
BEGIN
EXEC('select * from '+@tableName)
END
go
{
@tableName varchar(50),--表名
@columnName varchar(50),--重复字段名
@keyColumn varchar(50),--关键字
@orderColumn varchar(50),--排序字段
@orderMethod int --0:升序;1:降序
}
AS
BEGIN
declare @orderMethodName varchar(10)--排序方式
declare @sql varchar(8000)
case when @orderMethod ='0' then
@orderMethodName='asc'
else
@orderMethodName='desc'
end set @s = 'select * into #temp
from '+@tableName+' as a
where a.@keyColumn =
(select top 1 '+@keyColumn'+
from '+@tableName+'
where '+@columnName+' = a.'+@columnName+'
order by '+@orderColumn+' '+@orderMethodName+' )
order by a.'+@orderColumn+' '+@orderMethodName
set @s = @s + ' drop table '+@tableName
set @s = @s + ' select * into '+@tableName+' from #temp'
set @s = @s + ' drop table #temp'
exec(@s)
END
go
(
@intepid int,
@txtemname varchar(20),
@txtemmobile varchar(20),
@tracetimestart varchar (50),
@traceduration varchar(50),
@tracesolt varchar(50),
@isShishi int
)
AS
begin
insert into employeetb (intepid,txtemname,txtemmobile,txtempwd) values (@intepid,@txtemname,@txtemmobile,'123456')
insert into trace_timer (corpid,mobileid,starttime,traceslot,traceduration,mutesms,weekend_trace) values (@intepid,@txtemmobile,@tracetimestart,@tracesolt,@traceduration,@isshishi,'1')
endGO
from sysobjects
where (name = N'up_DeleteRepeat') and (type='P')))
drop procedure up_DeleteRepeat
go
CREATE PROCEDURE [dbo].up_DeleteRepeat
@tableName varchar (50)
AS
declare @sql varchar(500)
BEGIN
set @sql='select * from '+@tableName --修改之处,这里是不允许用变量
exec(@sql)
END
下面的为啥不需要拼凑SQL语句后执行
CREATE PROCEDURE dbo.addemployee
(
@intepid int,
@txtemname varchar(20),
@txtemmobile varchar(20),
@tracetimestart varchar (50),
@traceduration varchar(50),
@tracesolt varchar(50),
@isShishi int
)
AS
begin
insert into employeetb (intepid,txtemname,txtemmobile,txtempwd) values (@intepid,@txtemname,@txtemmobile,'123456')
insert into trace_timer (corpid,mobileid,starttime,traceslot,traceduration,mutesms,weekend_trace) values (@intepid,@txtemmobile,@tracetimestart,@tracesolt,@traceduration,@isshishi,'1')
endGO
可现在还有一个问题--为什么不能使用临时表@aaa呢?不使用临时表而使用aaa是可以的
请赐教.
--**************************************************************************
--up_DeleteRepeat
--表中一个字段重复的记录只保留一条.
--**************************************************************************
if (exists (select name
from sysobjects
where (name = N'up_DeleteRepeat') and (type='P')))
drop procedure up_DeleteRepeat
go
create proc up_DeleteRepeat @tableName varchar(50),--表名
@columnName varchar(50),--重复字段名
@keyColumn varchar(50),--关键字
@orderColumn varchar(50),--排序字段
@orderMethod int --0:升序;1:降序ASBEGIN
declare @strSql varchar(8000)
declare @orderMethodName varchar(10)--排序方式
if @orderMethod =0
set @orderMethodName='asc'
else if @orderMethod=1
set @orderMethodName='desc'
else
set @orderMethodName='asc'
set @strSql='select * into #aaa from '+@tableName+' as a where a.'+@keyColumn +'='
set @strSql=@strSql+'(select top 1 '+@keyColumn+' from '+@tableName+' where
'+@columnName+'=a.'+@columnName+' order by '+@orderColumn+' '+@orderMethodName+') order by '+@orderColumn +' '+@orderMethodName
exec (@strSql)
exec ('drop table '+@tableName)
exec ('select * into '+@tableName+ ' from #aaa')
exec ('drop table #aaa')
END
go
--**************************************************************************