OUTPUT - help with please

sskaar07

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+' '

Manivannan.D.Sekaran

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

ggciubuc

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."

maybe you can have succes with sp_executesql .

ggciubuc

...and Manivannan prove it

sskaar07

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!

sskaar07

@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