>>CREATE FUNCTION dbo.GetStoreNameAndAddress -- function name >>(@storecode CHAR(11)) -- input parameter name and data type >>RETURNS CHAR(50) -- return parameter data type >>AS >>BEGIN -- begin body definition >>DECLARE @StoreNameAndAddress CHAR(50) >>IF EXISTS (select * FROM store where LEFT(storecode,11) = @storecode) >> SELECT @StoreNameAndAddress = ( select RTRIM(store_name) + ' ' + RTRIM(f_address) from store where LEFT(storecode,11) = @storecode) >>ELSE >> SELECT @StoreNameAndAddress = 'Unknown Store' >>RETURN @StoreNameAndAddress >>END >>GO >>>
>select @StoreNameAndAddress = rtrim(store_name) + ' ' + ... from store where storeCode like @storeCode + '%' > >if @@ROWCOUNT = 0 > select @StoreNameAndAddress = 'Unknown Store'>