Executing Stored Procedures using COM API methods

This is developer specific technical tip which can be used in CRM customizations. In customizations we usually execute sql statements using CreateQueryObj function.
For Eg.
SqlQuery = “Select comp_name from company (nolock) where comp_compname=”+sCompId;
SqlObj = eWare.CreateQueryObj(SqlQuery);
SqlObj.SelectSQL();
Here to retrieve the value of comp_name we can use SqlObj(“comp_name”).
The above example is for the usual select query. Similarly to execute a stored procedure we use the below ,
SqlQuery = “Exec <Stored Procedure name>  “+sVariable;
SqlObj = eWare.CreateQueryObj(SqlQuery);
SqlObj.SelectSQL();
Here the stored procedure does not return any value. It could be either updating or inserting records.
What if the stored procedure is returning a value that you need to capture and use in the customization? We can use the below script.
SqlQuery = “Declare @id int exec @id = <Stored Procedure>  select @id as id”;
SqlObj = eWare.CreateQueryObj(SqlQuery);
SqlObj.SelectSQL();
Here the id value can be retrieved by using SqlObj(“id”).