Setting values in stored procedure

Question

Setting values in stored procedure

ehlinger

Hall of Fame

Points: 3585
More actions
October 24, 2006 at 11:02 am

#121011

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.

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ehlinger Hall of Fame Points: 3585 More actions · Answer 2

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)

John Rowan SSC Guru Points: 56440 More actions · Answer 3

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.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ehlinger Hall of Fame Points: 3585 More actions · Answer 4

That's the road I went down initially and I kept getting an error when running the stored procedure telling me that there was an error converting that string ('SELECT COUNT(*) FROM ##structure_' + @login) to data type INT.

John Rowan SSC Guru Points: 56440 More actions · Answer 5

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

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ehlinger Hall of Fame Points: 3585 More actions · Answer 6

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.