SELECT Into a Stored procedure

  • 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

  • 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'


    Brian Knight

  • yes, it'good but how can I read the value of variable @punteggio?

    SET @VAREXEC = 'SELECT @punteggio = punti from '+ @TblProfessione + 'WHERE ID = 1'


    @somma = @punteggio + ...

  • 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().


  • I have solved my problem with sp_executesql.

    Thank you for yuor help

  • Taki, would you mind posting your final solution? Future readers will appreciate it!


  • my solution is:

    DECLARE @stringa nvarchar(4000) @parametri nvarchar(100)

    DECLARE @punteggioSel smallint, @punteggioTot


    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