Sql中变量的运用

Sql中变量的使用
Create Procedure Test
  @TestID As Int
As

Declare @s Varchar(800)
Set @s=' '
Set @s='Create View DataL As Select CValue,Dvalue,RValue From TblRecord Where TestID=@TestID'
exec(s)

@TestID该怎么传？

[解决办法]
Set @s='Create View DataL As Select CValue,Dvalue,RValue From TblRecord Where TestID=@TestID'
改成
Set @s='Create View DataL As Select CValue,Dvalue,RValue From TblRecord Where TestID='||@TestID

[解决办法]

SQL code

Create Procedure  Test   @TestID As IntAsDeclare @s Varchar(800)Set @s=''Set @s='Create View DataL As Select CValue,Dvalue,RValue From TblRecord Where TestID='+  ''''+cast(@TestID as varchar)+''''  exec(s)
[解决办法]
create Procedure  Test
  @TestID  int
as
declare @s Varchar(800)
Set @s=''
Set @s='Create View DataL As Select CValue,Dvalue,RValue From TblRecord Where TestID='''+cast(@TestID as varchar)+'''' 
exec(@s)

[解决办法]
运用northwind数据库
Create Procedure Test
  @TestID  int
As
Declare @s nvarchar(800)
Set @s='Select * From dbo.Categories where CategoryID='''+cast(@TestID as varchar)+''''
print @s
exec(@s)  

exec  Test  @TestID=1

在sql 2005上可正确执行。
[解决办法]
动态SQL中，int类型的参数必须转换成varchar。