SQL Scalar value function Example

programming
1

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

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]