CREATE PROCEDURE Article_SyTable
@top int,
@CName VarChar(50)
AS
exec('select top '+rtrim(@top)+' * from Tinfo_Categories,Tinfo_Article
where C_Article_CategoriesID=N_Categories_ID
and C_Categories_Name = '''+@CName+''' order by N_Article_ID desc')
GO
@top int,
@CName VarChar(50)
AS
exec('select top '+rtrim(@top)+' * from Tinfo_Categories,Tinfo_Article
where C_Article_CategoriesID=N_Categories_ID
and C_Categories_Name = '''+@CName+''' order by N_Article_ID desc')
GO
解决方案 »
- 面试题,数据流的相关知识
- 如果表中没有某几条记录,就插入进去,该SQL怎么写?
- 如何获得存储过程返回的值?
- 批处理和存储过程有什么区别
- sqlserver2000数据库复制同步,distribution数据库过大问题??急!!!
- 请问 这个查询怎么写?
- 如果在group by后distinct多列汇总?
- 请教一个存储过程,看不明白意思啊.
- 如何不安装SQL Server2000企业管理器就可以使用DTS功能
- SQL Server 7.0中的全文索引只能创建在基于char类型的字段上吗?
- 如何查询所有1在一个字段中的1,2,3的记录
- 在SqlServer2000的存储过程中"Alter table_name Modify column_name date_type(..)" 语句怎么写??
CREATE PROCEDURE Article_SyTable
@top int,
@CName VarChar(50)
AS
exec('select top '+@top+'
*
from
Tinfo_Categories,Tinfo_Article
where
C_Article_CategoriesID=N_Categories_ID
and
C_Categories_Name = '+@CName+'
order by N_Article_ID desc
')GO
@top int,
@CName VarChar(50)
AS
exec('select top '+@top+' *
from Tinfo_Categories,Tinfo_Article
where C_Article_CategoriesID=N_Categories_ID
and C_Categories_Name = '''+@CName+'''
order by N_Article_ID desc ')GO
set rowcount @top
select here
set rowcount 0具体的解释请参考book online-------------------
http://chinadba.cn
最具实战经验的数据库优化,管理,设计,培训网
@top int,
@CName VarChar(50)
ASset rowcount @top
select * from
Tinfo_Categories,Tinfo_Article
where
C_Article_CategoriesID=N_Categories_ID
and
C_Categories_Name = @CName
order by N_Article_ID descset rowcount 0
GO
CREATE PROCEDURE Article_SyTable
@top int,
@CName VarChar(50)
ASset rowcount @top
select a.* from
Tinfo_Categories a,Tinfo_Article b
where
a.C_Article_CategoriesID=b.N_Categories_ID
and
a.C_Categories_Name = @CName
order by b.N_Article_ID descset rowcount 0
GO
http://www.aspfaq.com/show.asp?id=2521
@top int,
@CName VarChar(50)
ASdeclare @s varchar(8000)--需要把@top转换为字符型
set @s=
'select top ' + cast(@top as varchar(20)) + ' *
from Tinfo_Categories,Tinfo_Article
where C_Article_CategoriesID=N_Categories_ID
and C_Categories_Name = ''' + @CName + '''
order by N_Article_ID desc'exec (@s)go动态sql语句基本语法
------------------------------------------------------------------------------------------------------------
1、:普通SQL语句可以用Exec执行
eg:
Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N
2、字段名,表名,数据库名之类作为变量时,必须用动态SQL
eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意,加号前后的单引号的边上加空格 --当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确
3、 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls) --如何将exec执行结果放入变量中?
declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num