Must Declare Scalar Variable @Var1

Question

Error:

I get an error on SELECT * FROM @SportsTable reporting I must declare the scalar variable. I don't understand, if it is declared as a paramter to the stored procedure should I not be able to use it? If I remove the If NOT EXISTS line the procedure compiles correctly.

 USE [DB]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[sp_Sports_Insert]
    (
            @SportsTable dbo.TVP_SportsTable READONLY
        )      
    AS

    if NOT EXISTS (SELECT SportsTable.SportGUID FROM SportsTable WHERE SportsGUID = @SportsTable.SportsGUID)
    BEGIN
       INSERT INTO [dbo].[SportsTable]
       SELECT * FROM @SportsTable
    END

Objective:

To pass a table from Visual Studio 2008 to the parameter TVP_SportsTable. Then to insert any rows that do not already match an existing row in the [dbo].SportsTable. If an incoming row in the TVP_SportsTable matches an existing row in the [dbo].SportsTable, the row should be ignored, the remaining should be inserted.

*NOTE: TVP_SportsTable references a User defined type, it is defined on the server with the same structure as the dbo.TVP_SportsTable*

It is a table but you are using it as though it was a scalar variable. It isn't magically in scope for your `SELECT` statement without appearing in the `FROM` or as a `JOIN`-ed table and it could have more than one row anyway. Some of which do exist and others which don't. — Martin Smith, Feb 26 '14 at 16:33
Thanks Martin, I'm rather new at stored procedures would you mind explaining how I could implement what I'm attempting? Do I need to DECLARE the variable again. Like I explained above, when I remove the `IF` line. I am able to compile the stored procedure. So I don't see how the `IF` takes the variable out of scope. — Ccorock, Feb 26 '14 at 16:37
If `@SportsTable` has three rows. Two of them match an existing row in `[dbo].[SportsTable]` and one doesn't then what is the desired logic? Insert the one row and ignore the other two? Insert the one row and update the other two? Skip inserting anything at all? Something else? — Martin Smith, Feb 26 '14 at 16:38
If any rows match an existing row, those rows are ignored. All other rows are inserted. — Ccorock, Feb 26 '14 at 16:40

score 3 · Accepted Answer · edited May 23 '17 at 12:03

To meet your clarified objective you can use

INSERT INTO [dbo].[SportsTable]
SELECT *
FROM   @SportsTable vST
WHERE  NOT EXISTS(SELECT *
                  FROM   [dbo].[SportsTable] ST WITH(UPDLOCK, ROWLOCK, HOLDLOCK)
                  WHERE  ST.SportsGUID = vST.SportsGUID)

The rules for table variables and TVPs aren't any different than for normal tables. It is not valid to use them in a SELECT query without introducing them in the FROM clause or with a JOIN first.

The various locking hints are there to avoid race conditions from concurrent executions.

Shantanu Gupta · Answer 2 · 2014-02-27T08:31:01.373

2

You can simply use

INSERT INTO [dbo].[SportsTable]
   SELECT * FROM @SportsTable s LEFT JOIN SportsTable st       
              ON s.SportGUID = st.SportGUID
   WHERE st.SportGuid IS NULL

instead of

if NOT EXISTS (SELECT SportsTable.SportGUID FROM SportsTable 
                WHERE SportsGUID = @SportsTable.SportsGUID)
BEGIN
   INSERT INTO [dbo].[SportsTable]
   SELECT * FROM @SportsTable
END

You can also look into MERGE for achieving more functionalities like INSERT, DELETE and UPDATE using single query.

edited Feb 27 '14 at 08:31

answered Feb 26 '14 at 16:47

Shantanu Gupta

20,688
54
182
286

Giving it to Martin for an answer I'm more comfortable implementing but I will use your as a learning tool to further my knowledge, thank you. +1 – Ccorock Feb 26 '14 at 16:51

Must Declare Scalar Variable @Var1

2 Answers2