>9,999 times out of 10,000 there will be a row.
>How can I avoid the second query here?
>
>
>

>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
>

Don't use scalar functions.

You may use

select @StoreNameAndAddress = rtrim(store_name) + ' ' + ... from store where storeCode like @storeCode + '%'

if @@ROWCOUNT = 0
   select @StoreNameAndAddress = 'Unknown Store'

BTW, it should return varchar and not char unless you want to save spaces.

--------------------------------------
Since scalar functions don't play well in SQL Server, I suggest to incorporate it into your main query using OUTER APPLY, then you would be able to use ISNULL function to get the 'Unknown Store'