Stored Procedures: Description, Limits, and Examples

In this article I will talk about stored procedures. How to use them and why, limits, examples of cool procedures that I use.

Stored procedures allow executing code on the server side of the API in the same way as the execute method, but without passing the procedure code over the network.

A stored procedure is a predetermined algorithm that allows you to implement a quick call to several API methods at once, similar to the principle of the execute method, but without transmitting the code over the network (in the request you must specify only the name of the procedure and the necessary parameters).

You can create new stored procedures on the corresponding tab in the editing section of your application.

The code for stored procedures is written in the VKScript language.


VKScript for the execute method is not always small, and passing it in the request is very bold. Also for stored procedures, a convenient client is implemented directly in Vkontakte. Pull the procedure by its name, in this case, you no longer need to pass code . Without using stored procedures when sending requests from the client, your code sent to the API can be viewed.

By sending the code from the browser to the API, anyone can view it. When sending only the name of the stored procedure and parameters, the code will not be available.

Limits

My procedures

• A smart search for people without a date of birth and an exact city - it doesn’t matter which city or date of birth is specified in the user’s profile.
  execute.userSearch

  • Step 1
    
    1. Name, date of birth, city. According to these fields, we are looking for matches among Vkontakte accounts that have photos. In case we find 2 or more accounts, we use the one with the most recent login date. If nothing is found, go to Step 2.
    
    
  • Step 2
    
    1. Using data by last name and first name, date of birth, we re-search.
       If we find 2 or more accounts, go to Step 3.2. If nothing is found, go to Step 3.3.
    
    
  • Step 3
    
    1. We are looking for the 10 most popular groups of the selected city of Vkontakte.
    2. In these groups we are looking for FI, date of birth, country Russia, there is a photo. If something is found, then save the current result, otherwise go to step 3.3.
    3. We are looking for groups by FI, country Russia, there is a photo. If something is found, we save the result, otherwise it is "empty".
    
    

  var name = Args.fullname; var birth_day = Args.birth_day; var birth_month = Args.birth_month; var birth_year = Args.birth_year; var city = Args.city; var fields = "photo_id, sex, bdate, city, country, home_town, photo_max_orig, contacts, site, education, universities, schools, status, last_seen, followers_count, common_count, occupation, nickname, relatives, relation, personal, connections, exports, activities, interests, music, movies, tv, books, games, about, quotes, can_post, can_see_all_posts, can_see_audio, can_write_private_message, can_send_friend_request, is_favorite, is_hidden_from_feed, timezone, screen_name, maiden_name, career, military"; var countGroups = 10; var res = []; var item = {}; var city_id = 0; if (city != "") { city_id = API.database.getCities({"q": city, "country_id": 1}).items@.id[0]; } if (birth_day != "" && city_id > 0) { item = API.users.search({ "q": name, "country":1, "city": city_id, "has_photo":1, "fields": fields, "birth_day": birth_day, "birth_month": birth_month, "birth_year": birth_year }).items; var i = 0; while (i < item.length) { var item_ = item[i]; item_.criterion = " , , "; item_.step = "1.1"; res.push(item_); i = i +1; } } else { countGroups = countGroups + 1; } if (item.length > 0) { return res; } if (birth_day != "") { item = API.users.search({ "q": name, "country": 1, "has_photo": 1, "fields": fields, "birth_day": birth_day, "birth_month": birth_month, "birth_year": birth_year }).items; var i = 0; while (i < item.length) { var item_ = item[i]; item_.criterion = " , "; item_.step = "2.1"; res.push(item_); i = i +1; } } if (item.length > 0) { return res; } if (city.length > 0) { var groupsIDs = API.groups.search({ "q": city, "sort": 6, "type": "page", "count": countGroups }).items@.id; var count = 0; while (count < groupsIDs.length) { var item2 = {}; if (birth_day != "") { item2 = API.users.search({ "q": name, "country":1, "has_photo":1, "fields": fields, "birth_day": birth_day, "birth_month": birth_month, "birth_year": birth_year, "group_id": groupsIDs[count] }).items; } if (item2.length > 0) { var i = 0; while (i < item2.length) { var item_ = item2[i]; item_.criterion = " , ,    "; item_.step = "3.2"; item_.groupID = groupsIDs[count]; res.push(item_); i = i +1; } } else { item2 = API.users.search({ "q": name, "country":1, "has_photo":1, "fields": fields, "count": 5, "group_id": groupsIDs[count] }).items; var i = 0; while (i < item2.length) { var item_ = item2[i]; item_.criterion = ",    "; item_.step = "3.3"; item_.groupID = groupsIDs[count]; res.push(item_); i = i +1; } } count = count + 1; } } return res; 

  
  
  
  
• Get a list of user friends’s birth dates - I used this method to determine the approximate age of a person.
  execute.getFriendsBDates

   var response = ""; var bdates = API.friends.get({"user_id": Args.user_id, "fields": "bdate", "v": "5.103"}).items@.bdate; response = response + bdates; if (!bdates.length || bdates[0].length == 5000) { response = response + API.friends.get({"user_id": Args.user_id, "fields": "bdate", "offset": "5000", "v": "5.103"}).items@.bdate; } return response; 

  
  
  
  
• Get a list of community members.
  execute.getMembers

   var members = API.groups.getMembers({"group_id": Args.group_id, "v": "5.27", "sort": "id_asc", "count": "1000", "offset": Args.offset}).items; //       var offset = 1000; //      while (offset < 25000 && (offset + Args.offset) < Args.total_count) //    20000       { members = members + "," + API.groups.getMembers({"group_id": Args.group_id, "v": "5.27", "sort": "id_asc", "count": "1000", "offset": (Args.offset + offset)}).items; //    offset +   offset = offset + 1000; //    1000 }; return members; 

  
  
  
  
• Get a list of friends of users.
  execute.getFriends

   var user_ids = Args.user_ids.split(','); var friends = API.friends.get({"user_id": user_ids[0], "v": "5.27", "sort": "id_asc", "count": "10000"}).items; var i = 1; while (user_ids.length > i && i < 25) { friends = friends + "," + API.friends.get({"user_id": user_ids[i], "v": "5.27", "sort": "id_asc", "count": "10000"}).items; i = i + 1; } return friends; 

  
  
  
  
• Search for people in communities on demand, for example, whether users are in groups called “Online Movies”.
  execute.isMemberGroups

   var groups = API.groups.search({"q": Args.q, "v": "5.27", "offset": Args.offset, "count": "24"}).items; var members = []; var i = 0; while (groups.length > i) { var groupIsMember = []; groupIsMember.members = API.groups.isMember({"group_id": groups[i].id, "user_ids": Args.user_ids, "v": "5.27"}); groupIsMember.group_id = groups[i].id; members.push(groupIsMember); i = i + 1; } return members;