sqlserver2005中如何定义一个存储过程，可以实现参数化查询？

[wang66111988]

sqlserver2005中如何定义一个存储过程，可以实现参数化查询？例如
select * from a where name=?

[水族杰纶]

create proc proc_name
@name varchar(10)
as
 select * from a where name=@name

[-晴天]

CREATE PROCEDURE  ProcedureName 
@Param1
AS
BEGIN
	SELECT  * from a where name=@Param1
END

[王向飞]

--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
---->建表
create table 仑库表([WarehouseID] varchar(3),[Capacity] int)
insert 仑库表
select '001',100 union all
select '002',20
GO

create proc pa_仑库表 
@cae int
as
begin
select * from 仑库表 where [Capacity]=@cae
end

--
exec pa_仑库表 100

--> 查询结果
SELECT * FROM 仑库表
--> 删除表格



--> 删除表格
--DROP TABLE 仑库表

[wang66111988]

prepare 如何用？

[wang66111988]

动态SQL

[yanguohua2008]

动态SQL 可以参考以下写法：

create table tb_stat
(
	uname varchar(20),
	date datetime,
	unum int,
	isopt tinyint
)
go
insert into tb_stat
select 'a',  '2010-12-01', 1, 0 union
go


create proc sp_proctest
(
	@uname varchar(20)
)
as
begin
	
	declare @sql nvarchar(1000)
	set @sql = N'select * from tb_stat where uname = ''' + @uname + ''''
	exec(@sql)
end
 
go
exec sp_proctest 'a'