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
