sp_executesql 用法

--1.
declare @var1 varchar(20)
set @var1='white'
select * from authors where au_lname =  @var1

--2.
declare @var1 varchar(20)
set @var1='white'
declare @s nvarchar(4000)
set @s='select * from authors where au_lname = @var1'
exec sp_executesql @s,N'@var1 varchar(20)',@var1
go

--3.待测试
declare @var1 varchar(20)
set @var1='white'
declare @var2 varchar(20)
declare @s nvarchar(4000)
set @s='select '+cast(@var2 as varchar)+'=au_lname from authors where au_lname = @var1'
exec sp_executesql @s,N'@var1 varchar(20),@var2 varchar(20)',@var1,@var2
select @var2
print @var2
go

/*
create proc queryhose
@employee_id varchar(5),
@top int
as
declare @s nvarchar(4000)
set @s='
select top '+cast(@top as varchar)+' *  --少了*
from  house_contract join house_state on house_contract.house_id=house_state.house_id
where  employee_id=@employee_id and whether_rent=''0'''
exec sp_executesql @s,N'@employee_id varchar(5)',@employee_id
go

*/
--4.待测试

CREATE PROCEDURE dbo.GetTimeStamp
@PAT0000 int,@ts1 timestamp output,@tablename nvarchar(10)
AS
declare @sql nvarchar(500)
set @sql=N'Select @ts1 = TS From '+@tableName
     +'where PAT0000='+convert(nvarchar,@PAT0000)
execute sp_executesql @sql,N'@ts1 timestamp output',@ts1 output