Create PROC SP_TEST1(@ID Int, @Name Varchar(10))
As
Select @ID As ID, @Name As Name
GO
Create PROC SP_TEST2
As
Begin
Declare @ID Int, @Name Varchar(10), @S Varchar(8000)
Select @ID = 1 , @Name = 'A'
--得到SP_TEST1中的結果集, 並傳入參數
Select @S = 'Select * From OpenRowSet(''sqloledb'',''Trusted_Connection=yes'',''exec TEST..SP_TEST1 ' + Cast(@ID As Varchar) + ', ''''' + @Name + ''''''')'
EXEC(@S)
End
GO
EXEC SP_TEST2
GO
Drop PROC SP_TEST1, SP_TEST2
--Result
/*
ID Name
1 A
*/
Create PROC SP_TEST1(@ID Int, @Name Varchar(10))
As
Select @ID As ID, @Name As Name
GO
Create PROC SP_TEST2
As
Begin
Declare @ID Int, @Name Varchar(10), @S Varchar(8000)
Select @ID = 1 , @Name = 'A'
--得到SP_TEST1中的結果集, 並傳入參數
Select @S = 'Select * From OpenRowSet(''sqloledb'',''Trusted_Connection=yes'',''exec TEST..SP_TEST1 ' + Cast(@ID As Varchar) + ', ''''' + @Name + ''''''')'
Print @S
EXEC(@S)
End
GO
EXEC SP_TEST2
GO
Drop PROC SP_TEST1, SP_TEST2
--Result
/*
ID Name
1 A
*/
Create PROC SP_TEST1(@ID Int, @Name Varchar(10))
As
Select @ID As ID, @Name As Name
GO
Create PROC SP_TEST2
As
Begin
--得到SP_TEST1中的結果集,並且有一個字符型的參數(注意控制單引號,參考以下示例)
Select * From OpenRowSet('sqloledb','Trusted_Connection=yes','exec TEST..SP_TEST1 1, ''A''')
End
GO
EXEC SP_TEST2
GO
Drop PROC SP_TEST1, SP_TEST2
--Result
/*
ID Name
1 A
*/