CODEWITHSUNDEEP
sqlsql-server
quicklaunch2009
quicklaunch2009

Reputation: 9

select statement blocking another select statement

I'm not quite sure what is happening here. I have an alert that goes out if @StoreOrderID >1.

It goes like this:

declare @message varchar(1000)
@StoreID --(retrieves info from StoreOrders tables)

BEGIN
    select @message =  'store' + storename 
                                from StoreOrders as so
                                join Distributors as ds
                                on ds.DistributorID = so.StoreOrderID
                                where ds.SDistributorID = @StoreID


    select @message =   brandID
                                from StoreOrders a 
                                join Brandtitles as b
                                on b.branddistributorID = a.StoreOrderID
                                where b.brandNum = @DistributorNum and b.branddistributorID = @StoreID

select @message = 'date' + ISNULL(convert(varchar, @Date),'') 
select @message = 'cost' + ISNULL(Convert(varchar,@Cost),'')

Also for some reason if i try to concatenate a string unto the 'brand' select it throws an error. I can do it for the 'storename'. I think this may have something to do with it.

If I comment out the storename select it will send the alert for brandID, if I comment out the other one it does the other one. However, if I leave both of them, it will only show one of them. What am I doing wrong here?

Upvotes: 0

Views: 55

Answers (2)

M.Ali
M.Ali

Reputation: 69524

As a user mentioned in the comments, you are overwriting the @message variable everytime you assign it a new value, you are not concatenating any of the values. A much simpler and cleaner way would be something like....

declare @message varchar(1000)
        @StoreID [DataType]--(retrieves info from StoreOrders tables)


BEGIN

Declare  @storename  VARCHAR(100)
       , @brandID    VARCHAR(100)
       , @Date       VARCHAR(100)
       , @Cost       VARCHAR(100);

    select @storename =  'store' + ISNULL(storename , 'Unknown') 
    from StoreOrders as so
    join Distributors as ds
    on ds.DistributorID = so.StoreOrderID
    where ds.SDistributorID = @StoreID;


    select @brandID =   ISNULL(CAST( brandID AS VARCHAR(100) , 'Unknown')
    from StoreOrders a 
    join Brandtitles as b
    on b.branddistributorID = a.StoreOrderID
    where b.brandNum = @DistributorNum 
      and b.branddistributorID = @StoreID;

select @Date = 'date' + ISNULL(convert(varchar, @Date),'');
select @Cost = 'cost' + ISNULL(Convert(varchar,@Cost),'');

SET @message = @storename + @brandID + @Date + @Cost;

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

I Assume the code "alerting" the message is after the snipped you showed.

Then, as you use the same variable @message as target for both assignments, you overwrite the contents you assigned the first time when doing the second assignment. Only the values from the second assignment make it to the "alerting" command.

Use two different variables or "alert" your message after each assignment.

Upvotes: 0

Related Questions