Am I an idiot? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Am I an idiot?

Author

Topic

jaford
Starting Member

14 Posts

Posted - 2003-11-26 : 17:47:36

I've worked with this all afternoon.....to no avail.

Anyway, I need to set a variable within my stored proc by executing a select on a different db. i cannot hard code the db name because it could be one of several. so here's what i'm trying to do.

Declare @convRate integer
, @CtransSQL varchar(500)
, @db varchar(20)
, @custid varchar (50)
, @tsql varchar(20)

Select @db = 'dbase-snp'
Select @custid = '1671'

SELECT @CTransSQL = 'use approducts; select [' + @db + ']..tblPconv.conv into #ConversionTABLE FROM [' + @db + ']..tblPconv LEFT OUTER JOIN [' + @db + ']..tblSite ON [' + @db + ']..tblPconv.tblpconvID = [' + @db + ']..tblSite.pconvID RIGHT OUTER JOIN [' + @db + ']..tblUsers ON [' + @db + ']..tblSite.site_id = [' + @db + ']..tblUsers.site WHERE ([' + @db + ']..tblUsers.id = ' + @custid +')'

select @convrate = exec(@CtransSQL)

Obviously, the syntax is incorrect for the final Select but I have no idea what will work....I've even tried inserting the value into a temp db....yet when i "select * from #tempdb" i get an error that it's an invalid object.

can somebody point me in the right direction? PLEASE!!!! :)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-26 : 17:52:38

You cannot access a local temp table (#ConversionTABLE) this way. It is not in scope.
You can make it a ##ConversionTABLE, which is global in scope:


SELECT @CTransSQL = 'use approducts; select [' + @db + ']..tblPconv.conv into ##ConversionTABLE FROM [' + @db + ']..tblPconv LEFT OUTER JOIN [' + @db + ']..tblSite ON [' + @db + ']..tblPconv.tblpconvID = [' + @db + ']..tblSite.pconvID RIGHT OUTER JOIN [' + @db + ']..tblUsers ON [' + @db + ']..tblSite.site_id = [' + @db + ']..tblUsers.site WHERE ([' + @db + ']..tblUsers.id = ' + @custid +')'
EXEC(@CTransSQL)
SELECT @convRate = conv FROM ##ConversionTable
DROP TABLE ##ConversionTable

but be aware of multi user issues

jaford
Starting Member

14 Posts

Posted - 2003-11-26 : 18:11:38

ah ha....thanks.

by multi user issues to you mean more than one user accessing the stored proc at the same time or more than one user using the same temp table name?

Thanks again!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-11-26 : 18:26:07

You only have one field in the temp table so create the temp table then do an insert.
If it's more complicated then if all databases have the same structure just hard code one

select ... into #tbl from .... where 1=0
then code the insert in dynamic sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources