SQL question about a procedure with temporary tables

Question

I have one record in [Users] table and one record with the same UserID in [ShoppingCart] table,
I want to get two lines or more in the result - one is of the user in [Users] table and second otr more rows are from the same user('ID) in [UsersCart] table.

ShoppingCart is to indicate if there is or there are records in the [UsersCart]table with that share the sameUserID` (int)

But I get this error -

(1 row affected)

(0 rows affected)
Msg 205, Level 16, State 1, Procedure Login, Line 16 [Batch Start Line 0]
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

2. Why I get an error here, if the first DROP is inside an IF statement-

ELSE
BEGIN
SELECT * FROM #TemporaryTable -- here there's a red line underneath #TemporaryTable
DROP TABLE #TemporaryTable;
END

CREATE TABLE [Users](  
    UserID INT IDENTITY(1,1) PRIMARY KEY,  
 EmailAddress NVARCHAR(320) UNIQUE,  
 PasswordHash VARCHAR(32),  
 Salt VARCHAR(36),  
    FirstName VARCHAR(256) NOT NULL,  
    LastName VARCHAR(256) NOT NULL,  
 City VARCHAR(50) NULL DEFAULT NULL,  
 Street VARCHAR(50) NULL DEFAULT NULL,  
    Country VARCHAR(50) NULL DEFAULT NULL,  
)  

CREATE TABLE [ShoppingCart]  
(  
    OrderID INT IDENTITY (1,1) PRIMARY KEY,  
 UserID INT FOREIGN KEY REFERENCES [Users](UserID),  
    CreatedDate DATETIME DEFAULT(GETDATE())  
)  

CREATE TABLE [UsersCart]   
(  
  OrderID INT FOREIGN KEY REFERENCES [ShoppingCart](OrderID),  
     UserID INT FOREIGN KEY REFERENCES [Users](UserID),  
     ProductID INT FOREIGN KEY REFERENCES [Products](ProductID),  
  ItemPrice DECIMAL(4,2),  
     ItemCount INT NOT NULL  
)  

The Procedure - UPDATED

   ALTER PROC [dbo].[Login]  
        @emailAddress NVARCHAR(320),   
        @password VARCHAR(32)  
        AS  
        BEGIN  
        SELECT * INTO #TemporaryTable FROM [Users] WHERE EmailAddress = @emailAddress  AND   
                     PasswordHash = HASHBYTES('SHA2_256', @password +CAST(Salt AS NVARCHAR(36)))  
        ALTER TABLE #TemporaryTable DROP COLUMN Salt, PasswordHash  
         DECLARE @userId AS INT;  
        SET @userId = (SELECT UserID FROM #TemporaryTable)  
        IF EXISTS (SELECT * FROM ShoppingCart WHERE UserID = @userId)  
         BEGIN  
           DECLARE @orderId AS INT;  
           SET @orderId = (SELECT OrderID FROM [ShoppingCart] WHERE UserID = @userId)  
           SELECT * INTO #TemporaryCart FROM [UsersCart] WHERE UserID = @userId AND OrderID = @orderId  
            SELECT * FROM #TemporaryTable , #TemporaryCart  
           DROP TABLE #TemporaryTable, #TemporaryCart;  
        END  
        ELSE   
         BEGIN  
           SELECT * FROM #TemporaryTable -- error red line  
           DROP TABLE #TemporaryTable;  
           END  
    END  

1. Here - "EXEC [Login] @emailAddress = 'elado@gmail.com', @password = '123456';

This gives me in the results an empty row with only column names.. -

   BEGIN  
      DECLARE @orderId AS INT;  
      SET @orderId = (SELECT OrderID FROM [ShoppingCart] WHERE UserID = @userId)  
      SELECT * INTO #TemporaryCart FROM [UsersCart] WHERE UserID = @userId AND OrderID = @orderId  
      SELECT * FROM #TemporaryTable, #TemporaryCart  
      DROP TABLE #TemporaryTable, #TemporaryCart;  
      END  

Thanks
and A Happy New Year !

Answer 1

Hi，@Elado

1. About the red line underneath #TemporaryTable.
   You may Delete the temporary table after ‘IF EXIST　. . . ELSE. . .’
   Using this:

   IF OBJECT_ID('#TemporaryTable')IS NOT NULL   
   DROP TABLE #TemporaryTable;  
   IF OBJECT_ID('#TemporaryCart')IS NOT NULL   
   DROP TABLE #TemporaryCart;  
   

2. About “Here - "EXEC [Login] @emailAddress = 'elado@Stuff .com', @Lee = '123456'; This gives me in the results an empty row with only column names.. -”
   The problem may be in this statement: SET @orderId = (SELECT OrderID FROM [ShoppingCart] WHERE UserID = @userId)
   According to my guess,in Table [ShoppingCart],the same UserID may correspond to multiple OrderIDs, resulting in @orderId returned more than 1 value，
   which leads to this statement SELECT * INTO #TemporaryCart FROM [UsersCart] WHERE UserID = @userId AND OrderID = @orderId 0 rows affected.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

I have one record in [Users] table and one record with the same UserID in [ShoppingCart] table,

Instead of trying to provide poor description, if you provide queries to CREATE the tables then we will have all the information about the tables structure.

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Using tabular database like SQL Server, the result SET which return from query should fit a tabular structure which means set of rows which have the same structure for the columns. In fact, the columns data type is set in the metadata of the table and each column can have only one definition. This mean that (1) you cannot have a table with column that includes two different data types like INT in three rows and VARCHAR in 3 rows. (2) You cannot have a table which several rows have x columns and other rows have different number of columns like x+1.

Note! There is a special dynamic data type which can be used for columns that should store different type - SQL_VARIANT

The above error raise when the result SET include different data types or different number of columns. For example when you use UNION between queries that does not return the same structure.

Note! If you provide the queries to create the tables then we will be able to see exactly whet is the issue with the result set of these queries

Why I get an error here, if the first DROP is inside an IF statement-

The issue is in this part probably as I explained above

SELECT * FROM #TemporaryTable   
UNION   
SELECT * FROM #TemporaryCart  

If this does not solve your issue and cover the questions then please provide

1. Queries to create the relevant tables
2. Queries to insert some sample data which reproduce the issue

Answer 3

You have:

SELECT * FROM #TemporaryTable 
UNION 
SELECT * FROM #TemporaryCart

The first table is formed this way:

SELECT * INTO #TemporaryTable FROM [Users] WHERE EmailAddress = @emailAddress  AND 
                  PasswordHash = HASHBYTES('SHA2_256', @password +CAST(Salt AS NVARCHAR(36)))
ALTER TABLE #TemporaryTable DROP COLUMN Salt, PasswordHash

Permit me to first note that this not the way you do it. You list exactly the you want. You don't do SELECT * and then drop columns. Anyway, the Users table has nine columns, so now there are seven left.

The other table is formed this way:

SELECT * INTO #TemporaryCart FROM [UsersCart] WHERE UserID = @userId AND OrderID = @orderId

The table has five columns. In a UNION query, all components must return the same number of columns.

But apart from that, it seems funny trying put a user and a cart in the same result set. Not only, are you required to have the same number of columns in a UNION query - normally the columns should also have related data. This seems to be a mix of apples and oranges.

Maybe you should simply return two result sets?

Answer 4

Hi :-)

I updated my question as you asked.

Great :-)

Now we can base our answer better

The issue is as I said in my first respond. You are using UNION between two result SET which do not have the same structure (different number of columns)

and didn't understand how sql_variant can help me here, I read about it.

The reason I mentioned sql_variant is to clarify that when I said that you cannot have a table with column that includes two different data types I did not meant using data type sql_variant which can store different data types. It is not related to the solution of your question. just clarification of the previous sentence to make it accurate.

Why I get an error here, if the first DROP is inside an IF statement-

This "red line" has nothing to do with the server. It is only an "interpretation" of the SSMS. The SSMS does not parse the query in deep as the SQL Server does and it obviously does not execute it but only pass it to the server. Since you have "DROP TABLE #TemporaryTable" before you select from the table, then the SSMS mark an alert. SSMS does not "understand" that the DROP comes inside the IF and the SELECT comes in the ELSE.

Don't worry about this, it is not an error from the server but alert from the SSMS.

With that said, you DROP that TABLE #TemporaryTable in any case, so why do you DROP it inside the IF condition and the ELSE condition?

You can simply remove these two DROP and add it at the end of the SP. This will also remove the SSMS alert on the way

Answer 5

Now you have changed the code, so that it reads:

SELECT * FROM #TemporaryTable , #TemporaryCart

This is a cartesian join, which will give you the product of all rows. That is all rows in both tables combined with each other. Sometimes useful if you need to span a dimension, but it's nothing use very often.

And in this case, well I said that it is a product. And one table is empty. And what to you if you multiply with zero?