Select statement not exception the variables value

Question

Select statement not exception the variables value

BuilderBits

SSCertifiable

Points: 6071
More actions
December 7, 2005 at 6:57 pm

#92423

Been working on this learning curve four about three days now and i think i have got a little better understanding of what is going on here...

this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on (((   @UsersMaxID ))) but for some reason it will not work with the next select statement...

can someone make the pain go away and help me here..??

erik..

SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s
                          INNER JOIN UserSpecialty us
                          ON s.SpecialtyCD = us.SpecialtyCD
                          WHERE us.UserID = 29

GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE AA
AS
DECLARE @GenericColumn Varchar (200)
DECLARE @GenericValue Varchar (200)
SET @GenericColumn = 'FirstName'
SET @GenericValue = 'Erik'
DECLARE @sql NVARCHAR(4000)
DECLARE @user-id INT
DECLARE @UsersMaxID INT
DECLARE @MaxID INT
declare @tempResult varchar (1000)
-------------------------------------------Define the #Temporary Table----------------------------------------------
CREATE TABLE #UsersTempTable
(
ID int IDENTITY PRIMARY KEY,
UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75),
City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200),
Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2),
CompanyPhoto [varchar](50), CompanyDescr [varchar](2000))

---------------------------------------Fill the temp table with the Customers data-----------------------------------
SET @sql = 'INSERT INTO #UsersTempTable
(UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax,
Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr)
Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2,
Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website,
Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr
FROM USERS
WHERE ' + @GenericColumn +' = ''' + @GenericValue + ''''
EXEC sp_executesql @sql
SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)
SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s
                          INNER JOIN UserSpecialty us
                          ON s.SpecialtyCD = us.SpecialtyCD
                          WHERE us.UserID = 29
SELECT * FROM #UsersTempTable
/*
EXEC sp_executesql @sql, N'@UsersMaxID INT OUT', @UsersMaxID OUT(@SQL)
SELECT * FROM #UsersTempTable */
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)
SELECT SpecialtyName FROM Specialty s
                          INNER JOIN UserSpecialty us
                          ON s.SpecialtyCD = us.SpecialtyCD
                          WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE

Dam again!

Viewing 3 posts - 1 through 3 (of 3 total)

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

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

I can't figure out what you're attempting to do in the earlier part of the proc (too late at night.... to delve into the whys and wherefores of dynamic sql) but it seems that you're inserting into your temp table rows "where firstname = 'Erik'" -

1) how many rows do you get when you do this ?!

2) what do you get when you "PRINT @UsersMaxID" when you're debugging ?!

3) when you say your select does not work, what do you mean ?! do you get an error msg ?!

4) can you post the results of "SELECT * FROM #UsersTempTable" ?!

**ASCII stupid question, get a stupid ANSI !!!**

BuilderBits SSCertifiable Points: 6071 More actions · Answer 2

I have been working with this all week... and just in the last 4 hours or so i things seem to be coming togeather...

this always happens to me for some reason when i hit a new area in sql... The procedure is right..... I FORGOT THAT I DID NOT HAVE DATA IN THE TABLE..

Well i was able to pick up on more stuff while looking for a problem that was not their....

thanks again..

erik

Dam again!