October 24, 2006 at 11:02 am
I have a pretty complex stored procedure that we are developing for some reporting we need to do and am running into an issue in one part of it. I've taken just that part out and documented it here to simplify things. For my question I think I've included what is needed to explain what I'm trying to accomplish so here goes.
DECLARE @login varchar(50)
DECLARE @MyCounter INT
DECLARE @MaxCount INT
DECLARE @Level INT
DECLARE @StrucCount varchar(100)
DECLARE @StrucCount1 INT
--===== Presets
SET @StrucCount = '##structure_' + @login
SET @StrucCount1 = (SELECT COUNT(*) FROM @StrucCount)
SET @MyCounter = 2
SET @MaxCount = 2 * @StrucCount1
SET @Level = 1
Basically, what I am trying to do is get a count from a table created earlier in the procedure that is made up of ##structure_ and the users login. So, if the users login was Ted the name of the temporary table created earlier would be ##structure_Ted. We need to do this because the application this will be called from has limited security and logs everyone in under the same login.
From there I am trying to take that count multiplied by 2 so I can use the @MaxCount later in the procedure.
When I try and create the procedure I get an error indicating I must declare the variable @StrucCount.
If anyone can offer input indicating if I'm missing something simple here or need to approach it differently that would be great.
October 24, 2006 at 11:34 am
You cannot reference a table name stored within a variable without using Dynamic SQL. From the error that you've posted, it appears that you are using dynamic SQL and you may have omitted that from your example. Is this correct?
October 24, 2006 at 11:41 am
Yes, I am. Earlier in the procedure I use this to populate the table.
SET @SQL = 'CREATE TABLE ##structure _' + @login +
' (enid INT NOT NULL PRIMARY KEY,
headid INT,
orgid INT,
entity_name VARCHAR(100))
INSERT INTO ##structure_' + @login +
' (headid,orgid,enid,entity_name)
SELECT headid, orgid,enid,entity_name
FROM en_entity
WHERE orgid = ' + @orgid
EXEC(@SQL)
October 24, 2006 at 11:46 am
You cannot use a variable in the FROM clause in a DML statement. You will need to write the SELECT COUNT(*) FROM @StrucCount as Dynamic SQL also.
October 24, 2006 at 12:17 pm
declare @count int,
@sql nvarchar(1000),
@tablename varchar(100)
set @count = 0
set @tablename = 'person.contact'
set @sql = 'select @Count = count(*) from ' + @tableName
exec sp_executesql @sql,N'@count int OUTPUT',@count = @count OUTPUT
select @count
October 24, 2006 at 5:26 pm
Thanks John. That got me over that hurdle. Unfortunately some of the other errors I was getting are not proving to be as easy to resolve as I thought.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply