Must Declare Scalar Variable @Var1

1
votes

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*

2
sql-server-2008variablesstored-procedures
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
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
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
If any rows match an existing row, those rows are ignored. All other rows are inserted. – Ccorock

2 Answers

3
votes

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.

2
votes

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.