Q:

Get one Value From multiple Queries SQL

I want to get ONE value from different SELECT queries, I have created the following store procedure, but it keeps giving me an error, I added this into a SQLDATAADAPTER, when i test the query adding the values, it gives me conversion error from nvarchar to int, but I'm not using int in the store procedure. 
So I'm doing 4 small selects to get one final value (an item number), every parameter will be bind to a Dropdownlist which will pass the IDs, however I don't know if this is the best approach, or if I should do something different. 
        @Group as nvarchar(50),
        @Type as nvarchar(50),
        @Brand as nvarchar(50),
        @Art as nvarchar(50),
        @Final as nvarchar(50) OUTPUT
        AS
    begin
        SET NOCOUNT ON;
        SELECT @group = codigo_group FROM item_group WHERE     (group_art_id = @group)
        SELECT @type = codigo_type FROM item_type WHERE (type_art_id = @type)
        SELECT @brand = codigo_brand FROM item_brand WHERE (brand_id = @brand)
        SELECT @Art = format(COUNT(items.brand_id) +1, '0000')  From items where brand_id=@brand

        Select @Final= @group + '-' + @type + '-' + @brand + @Art
        RETURN @Final

        END

A:

I am guessing that you have a stored procedure, because there is no return declaration. 
Just remove the final return, because the value goes into @final. 
You can replace it with return 0 or some other integer.   Stored procedures return a status value that has to be an integer. 
If you want to return a value, then use create function and remove the output parameter.