August 28, 2001 at 9:52 am
Into a store procedure I want write a SELECT changing dynamically the name of table to select. I have used:
SELECT @punteggio = @punteggio + Punti from @TblProfessione WHERE ID = @professione
where @TblProfessione is the variable with the name of table to select but It's don't work
August 28, 2001 at 9:58 am
You'll have to build the string dynamically and then execute it as one variable.
Here's a simplified version:
SET @VAREXEC = 'SELECT blah, blah, blah from '+ @TblProfessione + 'WHERE ID = 1'
EXEC (@VAREXEC)
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
August 28, 2001 at 10:07 am
yes, it'good but how can I read the value of variable @punteggio?
SET @VAREXEC = 'SELECT @punteggio = punti from '+ @TblProfessione + 'WHERE ID = 1'
EXEC (@VAREXEC)
@somma = @punteggio + ...
August 28, 2001 at 6:13 pm
Only way to get that value is to write it out to a table, either a permanent one or a global temp (use the ## prefix). One of the many reasons I prefer NOT to use Exec().
Andy
August 29, 2001 at 8:35 am
I have solved my problem with sp_executesql.
Thank you for yuor help
August 29, 2001 at 5:56 pm
Taki, would you mind posting your final solution? Future readers will appreciate it!
Andy
August 30, 2001 at 2:46 am
my solution is:
DECLARE @stringa nvarchar(4000) @parametri nvarchar(100)
DECLARE @punteggioSel smallint, @punteggioTot
SET CONCAT_NULL_YIELDS_NULL OFF
SET @stringa = @stringa + ' SELECT @punteggioInt = @punteggioInt + PuntiMaschio from ' + @TblEta + ' WHERE ID = ' + CAST (@eta AS VARCHAR(5))
SET @stringa = @stringa + ' SELECT @punteggioInt = @punteggioInt + Punti from ' + @TblProfessione + ' WHERE ID = '+ CAST (@professione AS VARCHAR(5))
SET @parametri = '@punteggioInt smallint output'
EXEC sp_executesql @stringa, @parametri, @punteggioSel output
SET @punteggioTot = @punteggioSel + ...
I had not posted it first because it's similar of the solution of the topic "Ident_current and Dynamic SQL" in this forum
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply