Cannot get value from SELECT statement

Question

Cannot get value from SELECT statement

Johann Montfort

Hall of Fame

Points: 3123
More actions
November 28, 2008 at 3:56 am

#154963

Hi All,
I am trying to do a Select from a table, which has active rows and if it returns any results, I want to get the ID returned (will always return 1 row) and then Delete that row in the table. I also want to keep this ID to do an insert into another table which has deleted accounts with this same ID.
I am doing the following
SELECT BuildID AS [@BuildID]
FROM Builds_LIVE
WHERE BranchID = @BranchID
AND ProductID = @ProductID
--if found
IF @@ROWCOUNT > 0
BEGIN
DELETE FROM Builds_LIVE
WHERE BuildID = @BuildID
AND BranchID = @BranchID
AND ProductID = @ProductID
END
This is throwing an error already because the @BuildID is blank
Then I want to keep the same @BuildID to do the following
SELECT BuildID AS [@BuildDeadID]
FROM Builds_DEAD
WHERE BranchID = @BranchID
AND ProductID = @ProductID
AND BuildID = @BuildID
--if not found, insert
IF @@ROWCOUNT <= 0
BEGIN
INSERT INTO Builds_DEAD
(BuildID, BranchID, ProductID, BuildStatusID)
VALUES
(@BuildID, @BranchID, @ProductID, @DeletedStatus)
END
Can anyone help me out and point me to where the error is please
Thanks a lot
Regards

Viewing 15 posts - 1 through 14 (of 14 total)

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

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 1

Johann Montfort (11/28/2008)
Hi All,
I am trying to do a Select from a table, which has active rows and if it returns any results, I want to get the ID returned (will always return 1 row) and then Delete that row in the table. I also want to keep this ID to do an insert into another table which has deleted accounts with this same ID.
I am doing the following
SELECT BuildID AS [@BuildID]
FROM Builds_LIVE
WHERE BranchID = @BranchID
AND ProductID = @ProductID
--if found
IF @@ROWCOUNT > 0
BEGIN
DELETE FROM Builds_LIVE
WHERE BuildID = @BuildID
AND BranchID = @BranchID
AND ProductID = @ProductID
END
This is throwing an error already because the @BuildID is blank
Then I want to keep the same @BuildID to do the following
SELECT BuildID AS [@BuildDeadID]
FROM Builds_DEAD
WHERE BranchID = @BranchID
AND ProductID = @ProductID
AND BuildID = @BuildID
--if not found, insert
IF @@ROWCOUNT <= 0
BEGIN
INSERT INTO Builds_DEAD
(BuildID, BranchID, ProductID, BuildStatusID)
VALUES
(@BuildID, @BranchID, @ProductID, @DeletedStatus)
END
Can anyone help me out and point me to where the error is please
Thanks a lot
Regards

use "isnull" in your query, for example:

select isnull (BuildID, 0) as [@BuildID] ....

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 2

the thing is that @BuildID is returning a value, since there is a row at the moment, for example 30023. However I am loosing that value then

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The line SELECT BuildID AS [@BuildID] is actually naming the return column @BuildID, not assigning the value to the variable. To do the assignment, you need to do it like this:

SELECT @BuildID = BuildID

FROM Builds_LIVE

WHERE BranchID = @BranchID

AND ProductID = @ProductID

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ken McKelvey SSCoach Points: 18843 More actions · Answer 4

The syntax is wrong. Try:

SELECT @BuildID = BuildID ...

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 5

Johann Montfort (11/28/2008)
the thing is that @BuildID is returning a value, since there is a row at the moment, for example 30023. However I am loosing that value then

@BuildID is a return value that's why I'm using "IsNull" function, also check other variables in where caluse also (@BranchID, @ProductID).

use like:

select @BuildID = isnull (BuildID, 0) ...

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 6

Johann Montfort (11/28/2008)
Hi All,
I am doing the following
SELECT BuildID AS [@BuildID]
FROM Builds_LIVE
WHERE BranchID = @BranchID
AND ProductID = @ProductID
--if found
IF @@ROWCOUNT > 0
BEGIN
DELETE FROM Builds_LIVE
WHERE BuildID = @BuildID
AND BranchID = @BranchID
AND ProductID = @ProductID
END
This is throwing an error already because the @BuildID is blank

When you write that @BuildID is blank do you mean that it is null? Did you try to assign it a value using the select statement that you wrote? If the answer to both questions is yes, you first need to modify the select statement to look this way:

SELECT @BuildID = BuildID

FROM FROM Builds_LIVE

WHERE BranchID = @BranchID

AND ProductID = @ProductID

By the way I think that you can do the same thing that you are doing with your queries with one delete query that also uses output clause.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

euhm...

I would miss a declare statement !

Declare @BuildID integer

SELECT @BuildID = select top 1 BuildID

FROM Builds_LIVE

WHERE BranchID = @BranchID

AND ProductID = @ProductID

--if found

IF @@ROWCOUNT > 0

BEGIN

DELETE FROM Builds_LIVE

WHERE BuildID = @BuildID

AND BranchID = @BranchID

AND ProductID = @ProductID

END

once again, I made the mistake of opening several forumns at a time and not refreshing the page at the time I got to actually read it :blush:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 8

Hi All

this worked

SELECT @BuildID = BuildID

Adi can you tell me how I can do it with one delete and get a return value?

Thanks all!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Hi Johann

Looking at this from a slightly different angle...which variables do you have populated before you run this bit of code (nicked from Gail's post, thanks Gail)...

SELECT @BuildID = BuildID

FROM Builds_LIVE

WHERE BranchID = @BranchID

AND ProductID = @ProductID

Is it just @BranchID and @ProductID?

Next question: when you run this code...

SELECT COUNT(*)

FROM Builds_LIVE

WHERE BranchID = @BranchID

AND ProductID = @ProductID

how many rows can you get? Is it only ever one row or can it be more?

The reason I'm asking is because there's potentially a much simpler way of doing this.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 10

Chris Morris (11/28/2008)
Hi Johann
Looking at this from a slightly different angle...which variables do you have populated before you run this bit of code (nicked from Gail's post, thanks Gail)...
SELECT @BuildID = BuildID
FROM Builds_LIVE
WHERE BranchID = @BranchID
AND ProductID = @ProductID
Is it just @BranchID and @ProductID?
Next question: when you run this code...
SELECT COUNT(*)
FROM Builds_LIVE
WHERE BranchID = @BranchID
AND ProductID = @ProductID
how many rows can you get? Is it only ever one row or can it be more?
The reason I'm asking is because there's potentially a much simpler way of doing this.
Cheers
ChrisM

Hi Chris

Replying to your questions

Is it just @BranchID and @ProductID? Yes only BranchID and ProductID

Is it only ever one row or can it be more? It will always be 1 row, since I have a Composite Primary Key comprising of the BuildID, BranchID and ProductID, so there can only be 1 value for the BranchID/ProductID combination

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Thanks Johann - more than one row returned would have indicated a slightly different table-based solution. Anyway, reckon this should do the trick:

[font="Courier New"]SELECT @BuildID = BuildID

FROM Builds_LIVE

WHERE BranchID = @BranchID

AND ProductID = @ProductID

IF @BuildID IS NOT NULL

BEGIN

DELETE FROM Builds_LIVE

WHERE BuildID = @BuildID

AND BranchID = @BranchID

AND ProductID = @ProductID

END

IF NOT EXISTS (SELECT 1

FROM Builds_DEAD

WHERE BranchID = @BranchID

AND ProductID = @ProductID

AND BuildID = @BuildID)

BEGIN

INSERT INTO Builds_DEAD

(BuildID, BranchID, ProductID, BuildStatusID)

VALUES

(@BuildID, @BranchID, @ProductID, @DeletedStatus)

END

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 12

Johann Montfort (11/28/2008)
Hi All
this worked
SELECT @BuildID = BuildID
Adi can you tell me how I can do it with one delete and get a return value?
Thanks all!

You can use the output clause to insert the deleted data into another table. Here is an example:

use tempdb

--Creating the first table that stores data and inserting 2 records

create table BuyingList (ID int not null identity(1,1) primary key,

Product varchar(30),

Quantity int,

status tinyint default (0))

go

insert into BuyingList (Product, Quantity) values ('Milk',2)

insert into BuyingList (Product, Quantity, status) values ('Bread', 1, 1)

go

create table PurchaseLog (Product varchar(30), Quantity tinyint)

go

--using the output clause to send what ever I deleted

--into table PurchaseLog. After this statement completes

--the columns Product and Quantity from records that were

--deleted, will be inserted into PurchaseLog table

--by the output clause that I used in the query

delete BuyingList

output deleted.Product, deleted.Quantity

into PurchaseLog

where status = 1

go

--See the data in both tables

select * from BuyingList

select * from PurchaseLog

go

--Clean up the tables that were created for the demo

drop table BuyingList

drop table PurchaseLog

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 13

Chris Morris (11/28/2008)
Thanks Johann - more than one row returned would have indicated a slightly different table-based solution. Anyway, reckon this should do the trick:
[font="Courier New"]SELECT @BuildID = BuildID
FROM Builds_LIVE
WHERE BranchID = @BranchID
AND ProductID = @ProductID
IF @BuildID IS NOT NULL
BEGIN
   DELETE FROM Builds_LIVE
   WHERE BuildID = @BuildID
   AND BranchID = @BranchID
   AND ProductID = @ProductID
END
IF NOT EXISTS (SELECT 1
   FROM Builds_DEAD
   WHERE BranchID = @BranchID
   AND ProductID = @ProductID
   AND BuildID = @BuildID)
BEGIN
   INSERT INTO Builds_DEAD
                (BuildID, BranchID, ProductID, BuildStatusID)
   VALUES
                (@BuildID, @BranchID, @ProductID, @DeletedStatus)
END
[/font]

Cheers
ChrisM

Thanks Chris

Excellent advice as usual 🙂

Johann Montfort Hall of Fame Points: 3123 More actions · Answer 14

Adi Cohn (11/28/2008)
Johann Montfort (11/28/2008)
Hi All
this worked
SELECT @BuildID = BuildID
Adi can you tell me how I can do it with one delete and get a return value?
Thanks all!
You can use the output clause to insert the deleted data into another table. Here is an example:
use tempdb
--Creating the first table that stores data and inserting 2 records
create table BuyingList (ID int not null identity(1,1) primary key,
Product varchar(30),
Quantity int,
status tinyint default (0))
go
insert into BuyingList (Product, Quantity) values ('Milk',2)
insert into BuyingList (Product, Quantity, status) values ('Bread', 1, 1)
go
create table PurchaseLog (Product varchar(30), Quantity tinyint)
go
--using the output clause to send what ever I deleted
--into table PurchaseLog. After this statement completes
--the columns Product and Quantity from records that were
--deleted, will be inserted into PurchaseLog table
--by the output clause that I used in the query
delete BuyingList
output deleted.Product, deleted.Quantity
into PurchaseLog
where status = 1
go
--See the data in both tables
select * from BuyingList
select * from PurchaseLog
go
--Clean up the tables that were created for the demo
drop table BuyingList
drop table PurchaseLog
Adi

Excellent Stuff Adi

Always something new to learn!

Thanks again