mercredi 3 décembre 2014

Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql


Is there any way to access the variables in a dynamic sql which is declared outside the dynamic sql.


I need to do the following




  1. Query the information schema to get the parameter names to an sp SELECT PARAMETER_NAME, DATA_TYPE from information_schema.parameters where specific_name=@SPName




  2. Create a dynamic query using this names like select @param1+","+@param2 in a function.Which accepts @SPName as the parameter




3.call this function in my sps to get the dynamic sql



  1. Execute this sql in my procedure and get the param 1 and param 2 value (param 1 +param2) 5 .Update this value in my spcallTracking Table SpCallDetails field


I have tried this method ,but getting error like Must declare the scalar variable (param 1) and the reason i Guess is Dynamic sql runs in a different session and therefore variables defined outside the dynamic query will not be available to the dynamic query


http://ift.tt/1zPHjio


Regards,


Jeena





Aucun commentaire:

Enregistrer un commentaire