Update a #temptable IF @Table_Variable <>

Question

Post reply

Update a #temptable IF @Table_Variable <>

cor_dog2

Mr or Mrs. 500

Points: 539
More actions
October 4, 2017 at 1:18 pm

#328946

Hello, I'm trying to update a #TempTable IF @Table_Variable <> to a couple values, but I can't seem to get the syntax right.
I think I'm close, but either getting the error "Must declare the scalar variable" or just not working correctly.
This is what I have so far:
/*Get Entry Point Name Values*/
DECLARE @EntryPoint TABLE
(
Name varchar(50)
)
INSERT @EntryPoint
EXEC spNameHere 'SEntryPoint'
--Create TABLE #AllCases --INSERT INTO #AllCases --SELECT --FROM, etc... /* --Everything is working up to this point
--Now I want to delete rows from the #AllCases TempTable from above depending on what @EntryPoint.Name values it has. --My current code looks like this below, but I know it's incorrect as I'm getting the "Must declare the scalar variable @EntryPoint". --If anyone can point me in the correct direction, that would be greatly appreciated. Thanks! */
IF @EntryPoint <> '<ALL>' AND @EntryPoint <> '<NONE>'
DELETE FROM #AllCases
WHERE ISNULL(ColumnEntryPointName, '') <> @EntryPoint

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

Try this

IF EXISTS (SELECT 1 from @EntryPoint where Name NOT IN ('<ALL>', '<NONE>') BEGIN --do stuff END

You may need to refine the Boolean logic to fully match your needs.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Maybe something like this:
IF NOT EXISTS( SELECT * FROM @EntryPoint WHERE name IN( '<ALL>' , '<NONE>')) DELETE FROM #AllCases WHERE ISNULL(ColumnEntryPointName, '') NOT IN ( SELECT name FROM @EntryPoint)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

cor_dog2 Mr or Mrs. 500 Points: 539 More actions · Answer 3

Thank you both!
Luis, your code was spot on!! Huge thanks! Very much appreciated.

ScottPletcher SSC Guru Points: 101103 More actions · Answer 4

Never use ISNULL in a WHERE clause: not only is it unsargable [not relevant in this particular case], but it can also make the code harder to understand. The code below should do the same thing unless you could provide a blank name in @EntryPoint, in which case additional changes are needed, but I can't imagine that would ever be the case.

WHERE (ColumnEntryPointName IS NULL OR ColumnEntryPointName NOT IN ( SELECT name FROM @EntryPoint WHERE name IS NOT NULL))

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".