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
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
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
- 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
Regards,
Jeena
Aucun commentaire:
Enregistrer un commentaire