N.G. N.G. - 6 months ago 7
SQL Question

SQL Attempting to use variables to provide a log

So I am using MS SQL and I am attempting to setup my statements such that I can easily have a log printed as the code is executed. For some reason my variables appear to be out of scope and I can't determine why.

Error received:

Must declare the scalar variable "@Ideas"


Snippet of what works (aka without attempting to print out):

DECLARE @Ideas TABLE(IdeaID int)
INSERT INTO @Ideas (IdeaID)
SELECT IdeaID FROM dbo.Ideas
WHERE IdeaID IN (5,6,7)
DELETE FROM dbo.TableA WHERE IdeaID IN (SELECT IdeaID FROM @Ideas)
DELETE FROM dbo.TableB WHERE IdeaID IN (SELECT IdeaID FROM @Ideas)
DELETE FROM dbo.TableC WHERE IdeaID IN (SELECT IdeaID FROM @Ideas)


And the deletes continue after that from various tables but always the same array of IDs... my attempt to provide a way of printing the transactions:

DECLARE @PopulateIdeas varchar(500)
DECLARE @Ideas TABLE(IdeaID int)
SET @PopulateIdeas =
'
INSERT INTO' + @Ideas + '(IdeaID)
SELECT [IdeaID]
FROM [dbo].[Ideas]
WHERE [IdeaID] IN (5,6,7,8)
'
PRINT @PopulateIdeas
EXECUTE (@PopulateIdeas)


At this point I haven't even attempted to setup the deletes because I am stuck at populating my temp table and having it print... All help/suggestions appreciated.

Answer

As everyone else has said, this is a scoping issue. One work-around is to use "#temp" tables, as follows. It is not an ideal solution, particularly if there is a lot of data involved, but for small sets it should be fine.

DECLARE @PopulateIdeas varchar(500)

CREATE TABLE #Ideas (IdeaID int)

SET @PopulateIdeas = 
    '
    INSERT INTO #Ideas (IdeaID) 
    SELECT IdeaID
    FROM dbo.Ideas
    WHERE IdeaID IN (5,6,7,8) 
    '

PRINT @PopulateIdeas
EXECUTE (@PopulateIdeas)