# Create a 5 star rating

• 145 views

Userlevel 4 +4
• Moderator
• 37 replies

## Goal

Show a 5 star rating in form and list.

This function creates a star rating up to 5 stars based on the used parameter

## Solution

Create a subroutine that gets a numeric(2,1) as input and retuns a varbinary(max). This would look somelike this:

``/* Drop function get_stars_rating first. */if exists (select 1 from sysobjects           where name = 'get_stars_rating' and (type = 'FN' or type = 'TF' or type = 'IF'))   drop function get_stars_ratinggocreate function get_stars_rating(   @amount_of_stars  numeric(2,1)   )returns varbinary(max)asbegin    --control_proc_id:      function_get_stars_rating    --template_id:          function_get_stars_rating    --prog_object_item_id:  function_get_stars_rating    --template_description: Returns an icon with max 5 stars based on the amount of stars inputted    return cast('<?xml version="1.0" encoding="UTF-8"?><svg xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" viewBox="0 0 120 24" width="120px" height="24px"><defs>    <linearGradient id="star_1">        <stop offset="0%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 0, 0.0, 100 * (@amount_of_stars - 0)) as varchar(10)) + '%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 0, 0.0, 100 * (@amount_of_stars - 0)) as varchar(10)) + '%" stop-color="white"/>                    <stop offset="100%" stop-color="white" />    </linearGradient>    <linearGradient id="star_2" x1="0" x2="100%" y1="0" y2="0">        <stop offset="0%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 1, 0.0, 100 * (@amount_of_stars - 1)) as varchar(10)) + '%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 1, 0.0, 100 * (@amount_of_stars - 1)) as varchar(10)) + '%" stop-color="white"/>        <stop offset="100%" stop-color="white" />    </linearGradient>    <linearGradient id="star_3" x1="0" x2="100%" y1="0" y2="0">        <stop offset="0%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 2, 0.0, 100 * (@amount_of_stars - 2)) as varchar(10)) + '%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 2, 0.0, 100 * (@amount_of_stars - 2)) as varchar(10)) + '%" stop-color="white"/>        <stop offset="100%" stop-color="white" />    </linearGradient>    <linearGradient id="star_4" x1="0" x2="100%" y1="0" y2="0">        <stop offset="0%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 3, 0.0, 100 * (@amount_of_stars - 3)) as varchar(10)) + '%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 3, 0.0, 100 * (@amount_of_stars - 3)) as varchar(10)) + '%" stop-color="white"/>        <stop offset="100%" stop-color="white" />    </linearGradient>    <linearGradient id="star_5" x1="0" x2="100%" y1="0" y2="0">        <stop offset="0%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 4, 0.0, 100 * (@amount_of_stars - 4)) as varchar(10)) + '%" stop-color="#FFD54F"/>        <stop offset="' + cast(iif(@amount_of_stars < 4, 0.0, 100 * (@amount_of_stars - 4)) as varchar(10)) + '%" stop-color="white"/>        <stop offset="100%" stop-color="white" />    </linearGradient></defs><path id="B" d="M21.983,8.916h-6.784c-0.204,0-0.386-0.13-0.453-0.323l-2.292-6.621c-0.149-0.43-0.758-0.43-0.907,0L9.254,8.594 C9.187,8.787,9.006,8.917,8.802,8.917L2.016,8.934c-0.458,0.001-0.654,0.581-0.291,0.86l5.392,4.145 c0.157,0.121,0.224,0.326,0.168,0.516l-1.941,6.576c-0.128,0.433,0.359,0.787,0.732,0.531l5.654-3.877 c0.164-0.112,0.379-0.112,0.543,0l5.654,3.877c0.372,0.255,0.859-0.098,0.732-0.532l-1.941-6.576 c-0.056-0.19,0.01-0.395,0.167-0.516l5.394-4.165C22.638,9.496,22.441,8.916,21.983,8.916z" stroke-width="1" stroke="black"/><use width="120px" height="24px" xlink:href="#B" fill="url(#star_1)"/><use width="120px" height="24px" xlink:href="#B" x="20%" fill="url(#star_2)"/><use width="120px" height="24px" xlink:href="#B" x="40%" fill="url(#star_3)"/><use width="120px" height="24px" xlink:href="#B" x="60%" fill="url(#star_4)"/><use width="120px" height="24px" xlink:href="#B" x="80%" fill="url(#star_5)"/></svg>'    as varbinary(max))endgogrant execute on get_stars_rating to publicgo``

Then add this to your form as a calculated field query or to a view.

``dbo.get_stars_rating(coalesce(t1.amount_of_stars, 0.0))``

I've used a yellow color. If you want to change the backgroundcolor you can find and replace #FFD54F with your preferred color. And replace “white” for the background. +4