February 4, 2009 at 9:39 am
How come when I do something like the following:
SELECT
@Var1 = 5
, @Var2 = @Var2 + 1
, @Var3 = ( SELECT MyValue FROM MyTable WHERE MyID = @MyID )
@Var3 is always NULL. But when I break it out so that @Var3 is assigned separately like this:
SELECT
@Var1 = 5
, @Var2 = @Var2 + 1
SELECT
@Var3 = ( SELECT MyValue FROM MyTable WHERE MyID = @MyID )
@Var3 gets the value as expected.
Can someone please explain what's going on?
Thanks!
John
February 4, 2009 at 10:24 am
You are doing right.
You are just missing a step.. "That is if you are wanting to view the new values in the query window in management studio"
-------------------------------------------------------------
DECLARE
@Var1 INT
, @Var2 INT
, @Var3 BIT;
SET @Var2=2;
SELECT
@Var1 = 5
, @Var2 = @Var2 + 1
, @Var3 = ( SELECT IsApproved FROM dbo.aspnet_Membership WHERE UserId = 'd2a15d29-f87f-4fcd-8b9e-6c49a9eaa57d' )
SELECT
@Var1
, @Var2
, @Var3;
Dam again!
February 4, 2009 at 10:29 am
Oh, I just didn't include the declarations and assignments above in the code snippet. I have a whole proc that I was modifying and as part of the it I needed to set up a loop where one value in the row points to the next row ID so I was going to grab the next row ID and assign it along with incrementing a counter and some other things, but I found that when I performed the variable assignment in the same SELECT as the other variable assignments, the subquery assignment is returning NULL. When I break it out into its own SELECT, it returns the value properly. I'm just a little baffled by this.
February 4, 2009 at 10:34 am
you will have to post your code so someone can look at what you are describing.
Dam again!
February 4, 2009 at 10:40 am
Please post your code. Based on your last post indicating you are trying to use a loop, there is most likely a better way to accomplish your task without a loop.
February 4, 2009 at 10:56 am
I had to rename some things because this is code I'm working on for a client but here it is:
Working Code:
ALTER FUNCTION [dbo].[MyFunc] (
@MyId INT
)
RETURNS @t TABLE (
MyIDINT
,MySubIDINT
,MyNameVARCHAR(50)
,MyLevelINT
)
AS
BEGIN
DECLARE
@MySubIDINT
,@MyLevelINT
-- This is where I had to break it out into two separate selects
SELECT @MyLevel = 0
SELECT @MySubID = ( SELECT MySubID FROM MyTable WHERE MyID = @MyID )
WHILE ( @MyID <> 0 )
BEGIN
INSERT INTO @t (
MyID
,MySubID
,MyName
,MyLevel
)
SELECT
a.MyID
,a.MySubID
,a.MyName
,@Level
FROM
MyTable a
WHERE
a.MyID = @MyID
SELECT @MyLevel = @MyLevel + 1
SELECT @MyID = @MySubID
SELECT @MySubID = ( SELECT MySubID FROM MyTable WHERE MyID = @MyID )
END
RETURN
END
GO
The original code was using a CTE but I found that the recursion was linear and I couldn't get it work without using a loop. It seems like the CTE recursion would only work if it was a tree like structure such as an org chart. The CTE code is below (note: the terminating condition is when MyID = 0):
ALTER FUNCTION [dbo].[MyFunc] (
@MyID INT
)
RETURNS @t TABLE (
MyID INT
,MySubID INT
,MyName VARCHAR(50)
,MyLevel INT
)
AS
BEGIN
WITH MyCTETable (
MyID
,MySubID
,MyName
,MyLevel
) AS (
-- Old code has the starting point as the anchor query, not the ending point. If I change this it will not give the right results either
SELECT
a.MyID
,a.MySubID
,a.MyName
,0 AS MyLevel
FROM
MyTable a
WHERE
a.MyID = @MyID
UNION ALL
-- Create the recursive query.
SELECT
a.MyID
,a.MySubID
,a.MyName
,b.MyLevel + 1
FROM
dbo.MyTable a
INNER JOIN MyCTETable b
ON a.MySubID = b.MyID
)
INSERT INTO @t
SELECT * FROM MyCTETable ORDER BY MyID
RETURN
END
GO
Thanks for your help!
John
February 4, 2009 at 11:07 am
John (2/4/2009)
I had to rename some things because this is code I'm working on for a client but here it is:Working Code:
ALTER FUNCTION [dbo].[MyFunc] (
@MyId INT
)
RETURNS @t TABLE (
MyIDINT
,MySubIDINT
,MyNameVARCHAR(50)
,MyLevelINT
)
AS
BEGIN
DECLARE
@MySubIDINT
,@MyLevelINT
-- This is where I had to break it out into two separate selects
SELECT @MyLevel = 0
SELECT @MySubID = ( SELECT MySubID FROM MyTable WHERE MyID = @MyID )
WHILE ( @MyID <> 0 )
BEGIN
INSERT INTO @t (
MyID
,MySubID
,MyName
,MyLevel
)
SELECT
a.MyID
,a.MySubID
,a.MyName
,@Level
FROM
MyTable a
WHERE
a.MyID = @MyID
SELECT @MyLevel = @MyLevel + 1
SELECT @MyID = @MySubID
SELECT @MySubID = ( SELECT MySubID FROM MyTable WHERE MyID = @MyID )
END
RETURN
END
GO
The original code was using a CTE but I found that the recursion was linear and I couldn't get it work without using a loop. It seems like the CTE recursion would only work if it was a tree like structure such as an org chart. The CTE code is below (note: the terminating condition is when MyID = 0):
ALTER FUNCTION [dbo].[MyFunc] (
@MyID INT
)
RETURNS @t TABLE (
MyID INT
,MySubID INT
,MyName VARCHAR(50)
,MyLevel INT
)
AS
BEGIN
WITH MyCTETable (
MyID
,MySubID
,MyName
,MyLevel
) AS (
-- Old code has the starting point as the anchor query, not the ending point. If I change this it will not give the right results either
SELECT
a.MyID
,a.MySubID
,a.MyName
,0 AS MyLevel
FROM
MyTable a
WHERE
a.MyID = @MyID
UNION ALL
-- Create the recursive query.
SELECT
a.MyID
,a.MySubID
,a.MyName
,b.MyLevel + 1
FROM
dbo.MyTable a
INNER JOIN MyCTETable b
ON a.MySubID = b.MyID
)
INSERT INTO @t
SELECT * FROM MyCTETable ORDER BY MyID
RETURN
END
GO
Thanks for your help!
John
This is something that I noticed and is not your fix... I am just prettying it up a little.
-- This is where I had to break it out into two separate selects
SELECT @MyLevel = 0,
@MySubID = MySubID FROM MyTable WHERE MyID = @MyID;
SELECT @MyLevel = @MyLevel + 1,
@MyID = @MySubID,
@MySubID = MySubID FROM MyTable WHERE MyID = @MyID;
Dam again!
February 4, 2009 at 11:11 am
Really the way you have is should return just fine.
-- This is where I had to break it out into two separate selects
SELECT @MyLevel = 0
SELECT @MySubID = ( SELECT MySubID FROM MyTable WHERE MyID = @MyID )
WHILE ( @MyID <> 0 )
-------------------------------------
This should work as well
-- This is where I had to break it out into two separate selects
SELECT @MyLevel = 0,
@MySubID = MySubID FROM MyTable WHERE MyID = @MyID;
WHILE ( @MyID <> 0 )
Dam again!
February 4, 2009 at 11:12 am
Need a little more from you. The DDL for the table in the function, sample data for the table (as INSERT sttements that can be cut, paste, and executed to load the table), and expected results based on the sample data.
For more assistance in this, please read the first article linked below in my signature block regarding asking for assistance.
February 4, 2009 at 11:27 am
Okay, something's weird because I created a test case
create database test
go
USE test
go
CREATE TABLE MyTable (
MyID INT
,MySubID INT
,MyName VARCHAR(50)
)
INSERT INTO MyTable (MyID, MySubID, MyName) VALUES (10, 5, 'Name1')
INSERT INTO MyTable (MyID, MySubID, MyName) VALUES (5, 2, 'Name1')
INSERT INTO MyTable (MyID, MySubID, MyName) VALUES (2, 0, 'Name1')
And I ran the function on it in my test database using
SELECT @MyLevel = 0
SELECT @MySubID = ( SELECT MySubID FROM MyTable WHERE MyID = @MyID )
and
SELECT
@MyLevel = 0
, @MySubID = ( SELECT MySubID FROM MyTable WHERE MyID = @MyID )
And they both worked. Then I went back into the client's test database, changed the code again, and it's working. Is there some kind of SET option that could have possibly changed the assignment behavior?
If not, I'm attributing it to user error (myself) and closing this thread... I knocked my head around for over an hour on this and now when I finally ask for help it starts working :p typical
Thanks everyone, sorry about that.
February 4, 2009 at 11:39 am
Nothing to apologize for, but please, still give us the info I requested. If you are using a loop, it won't scale well. There is most likely a set-based approach to your problem that will scale better.
February 4, 2009 at 11:53 am
I don't know what else you want..? I gave the old / new function code, the table creation code for a test case, and some insert statements to populate the test case.
Basically the premise here is that we have a starting point (one uniquely identified record in the table) and there's a link in that table to another record. When the linking value reaches 0, we stop.
In typical recursion, I would have started with checking the terminating case of value = 0 then returning out of the recursion but the problem with using this in the CTE is that I have not figured out a way to specify the starting point. If I just use a JOIN in the recursion query on the chaining fields it'll pull way more data than I want.
Do you know how to construct a CTE or other set based solution to do this in a general case without looping? (such as, given employee ID 5, return the list of employee / manager / director / VP / and finally CEO ... as a table)
February 4, 2009 at 11:57 am
I read your comments about things starting to work and thought you hadn't finished getting everything, sorry.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply