declare @sql nvarchar(4000),@id int,@name varchar(20),@sex varchar(2),@age int set @name='xxx' set @sql=N'select @id=id,@sex=sex,@age=age from aa where name=@name' exec sp_executesql @sql, N'@id int out,@sex varchar(2) out,@age int out,@name varchar(20)' @id out,@sex out,@age out,@name
--方法5:输入输出--用sp_executesql declare @i int,@con int,@s nvarchar(1000) set @i=5 select @s='select @con=count(1) from sysobjects where ID>@i' exec sp_executesql @s,N'@con int output,@i int',@con output ,@i select @con
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[getaa] ( @name nvarchar(10) ) AS BEGIN SET NOCOUNT ON; SELECT id,sex,age from aa where name=@name END
if object_id('tb') is not null drop table tb go create table tb(id int identity(1,1),name varchar(10),sex varchar(10),age int) go insert into tb select '张三','男',24 union all select '李四','女',26 union all select '王五','男',28 declare @id int,@name varchar(10),@sex varchar(10),@age int set @name='李四' exec sp_executesql N'select @id=id,@sex=sex,@age=age from tb where name=@name', N'@id int output,@sex varchar(10) output,@age int output,@name varchar(10)', @id output,@sex output,@age output,@name select @id,@sex,@age
set @name='xxx'
set @sql=N'select @id=id,@sex=sex,@age=age from aa where name=@name'
exec sp_executesql @sql,
N'@id int out,@sex varchar(2) out,@age int out,@name varchar(20)'
@id out,@sex out,@age out,@name
declare @i int,@con int,@s nvarchar(1000)
set @i=5
select @s='select @con=count(1) from sysobjects where ID>@i'
exec sp_executesql @s,N'@con int output,@i int',@con output ,@i
select @con
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getaa]
(
@name nvarchar(10)
)
AS
BEGIN
SET NOCOUNT ON; SELECT id,sex,age from aa where name=@name
END
drop table tb
go
create table tb(id int identity(1,1),name varchar(10),sex varchar(10),age int)
go
insert into tb
select '张三','男',24 union all
select '李四','女',26 union all
select '王五','男',28
declare @id int,@name varchar(10),@sex varchar(10),@age int
set @name='李四'
exec sp_executesql
N'select @id=id,@sex=sex,@age=age from tb where name=@name',
N'@id int output,@sex varchar(10) output,@age int output,@name varchar(10)',
@id output,@sex output,@age output,@name
select @id,@sex,@age