OUTPUT - help with please

I am using a dynamic t-sql string in proc1 to execute proc2, which returns an int variable named @.Fatal_Error back to proc1.

When I execute proc2 I use the syntax:

EXEC @.SQL @.Params

@.SQL is the Proc Name (varchar) and @.Params is the parameter string (nvarchar).

If I include the @.Fatal_Error variable in the dynamic creation of the @.Params string the returning value from Proc2 is unable to convert int to nvarchar.

I have declared @.Fatal_Error in proc1 as int and tried to add to the end of my dynamic t-sql EXEC but I still get 'Cannot convert int to nvarchar' .

Please help - I'm beginning to pull out hair! :-)

Thanks!

Here' s the syntax I tried when just passing it at the end of the EXEC call:

EXEC @.SQL @.Param_List = @.Fatal_Error

AND I also tried:

EXEC @.SQL @.Param_List + ' '+@.Fatal_Error+' '

You have to use the sp_executesql for parameterized dynamic sql,

Code Snippet

Declare @.idParm as int;

Declare @.nameParm as varchar(100);

Declare @.dynamicSql as nvarchar(1000);

Declare @.dynamicParamDef as nvarchar(1000);

Set @.idParm = 2;

Set @.nameParm = 'sysobjects'

Set @.dynamicSql = N'Select * from sysobjects where id=@.id or name=@.name'

Set @.dynamicParamDef = N'@.id as int, @.name as varchar(100)'

Exec sp_executesql @.dynamicSql, @.dynamicParamDef, @.idParm, @.nameParm

|||

I quote from BOL(look for sp_executesql, building statement at runtime)

"

Transact-SQL supports the following methods of building SQL statements at run time in Transact-SQL scripts, stored procedures, and triggers:

Use the sp_executesql system stored procedure to execute a Unicode string. sp_executesql supports parameter substitution similar to the RAISERROR statement.

Use the EXECUTE statement to execute a character string. The EXECUTE statement does not support parameter substitution in the executed string."|||...and Manivannan prove it|||

Thank you all for your assistance. I will give it a whirl.

I was able to finally execute with EXEC @.SQL @.Params, @.Fatal_Error = @.Fatal_Error

However, I'm sure this will come back to bite me in the long run..

Thank you ALL for your quick replies!

|||

@.SQL is my stored procedure name

@.Param_List is the list of enumerated parameters

SET @.SQL = @.SQL + IsNull(@.Param_List,'');

EXECUTE sp_executesql @.SQL;

Worked like a charm.

Thanks!

Sandy