# SQL Scalar value function Example

**URL:** <https://forum.kirupa.com/t/sql-scalar-value-function-example/218701>\
**Category:** programming\
**Created:** [March 9, 2007, 9:18pm UTC](https://forum.kirupa.com/t/sql-scalar-value-function-example/218701 "2007-03-09T21:18:03Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![broneah](https://yyz1.discourse-cdn.com/flex011/user_avatar/forum.kirupa.com/broneah/32/1000_2.png) [@broneah](https://forum.kirupa.com/u/broneah)\
**Post date:** [March 9, 2007, 9:18pm UTC](https://forum.kirupa.com/t/sql-scalar-value-function-example/218701/1 "2007-03-09T21:18:03Z")

</div>

[SIZE=2][COLOR=#0000ff]Not sure if anyone can use this, its somewhat high level but for those of you intersted, heres how 🙂

SET[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]ANSI\_NULLS[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]ON  
[/COLOR][/SIZE][SIZE=2]GO  
[/SIZE][SIZE=2][COLOR=#0000ff]SET[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]QUOTED\_IDENTIFIER[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]ON  
[/COLOR][/SIZE][SIZE=2]GO  
[/SIZE][SIZE=2][COLOR=#008000]-- =============================================  
– Author: \<Ryan Roberts\>  
– Create date: \<3-09-07\>  
– Description: \<Returns product image url for product search based on prefered child sku in department.\>  
– =============================================  
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]CREATE[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]FUNCTION[/COLOR][/SIZE][SIZE=2] dbo[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]fn\_ProductImageURL  
[/SIZE][SIZE=2][COLOR=#808080](  
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]-- Add the parameters for the function here  
[/COLOR][/SIZE][SIZE=2]@Sku [/SIZE][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][SIZE=2]COLOR=#808080  
)  
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]RETURNS[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][SIZE=2]COLOR=#808080  
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]AS  
BEGIN  
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]-- Declare the return variable  
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]DECLARE[/COLOR][/SIZE][SIZE=2] @ResultVar [/SIZE][SIZE=2][COLOR=#0000ff]varchar[/COLOR][/SIZE][SIZE=2]COLOR=#808080  
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#008000]-- Add the T-SQL statements to compute the return value here  
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]top[/COLOR][/SIZE][SIZE=2]COLOR=#808080[/COLOR][/SIZE][SIZE=2] @ResultVar [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff0000]’~/App\_Common/Images/’[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff00ff]REPLACE[/COLOR][/SIZE][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][SIZE=2]PreferredChildSku[/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff0000]’:’[/COLOR][/SIZE][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff0000]’’[/COLOR][/SIZE][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#ff0000]’\_sm.jpg’  
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][SIZE=2] product\_HeaderDepartment  
[/SIZE][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][SIZE=2] ProductSku [/SIZE][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][SIZE=2] @Sku  
[/SIZE][SIZE=2][COLOR=#008000]-- Return the result of the function  
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]RETURN[/COLOR][/SIZE][SIZE=2] @ResultVar  
[/SIZE][SIZE=2][COLOR=#0000ff]END

How to call in stored procedure:  
[SIZE=2]select dbo[/SIZE][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][SIZE=2]fn\_ProductImageURL[/SIZE][SIZE=2]COLOR=#808080 as ImageFileSmall[/COLOR][/SIZE]  
[SIZE=2][COLOR=#808080][/COLOR][/SIZE]  
[SIZE=2][COLOR=#808080]Payyyyceee  
[/COLOR][/SIZE][/COLOR][/SIZE]