返回动态SQL语句中执行结果值

--值,动态查询结果返回

DECLARE @query NVARCHAR(MAX)

DECLARE @testVal INT

SET @query='select @testVal=5'

EXEC sp_executesql @query,N'@testVal int out', @testVal OUT

SELECT @testVal

--表,传入变量

IF NOT EXISTS (SELECT *

FROM sys.typesst

JOIN sys.schemasss

ON st.schema_id= ss.schema_id

WHERE st.name= N'specsAndModel' AND ss.name= N'dbo')

BEGIN

CREATE TYPE [dbo].[specsAndModel]AS TABLE(

specName VARCHAR(50)

,specValVARCHAR(50)

)

END

GO

declare @t specsAndModel

insert @t VALUES('[modelNumber]','F00-B4R')

exec sp_executesql N'select specName, specVal from @var',N'@var specsAndModel readonly',@t