Variables

  • DECLARE @Var1INTEGER  = 12
    DECLARE @Var2SYSNAME = '@Var1'

    Is there any way to get 12 from @Var2?

    Thanks!

    P

     

  • No.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Thanks for the succinct reply 🙂

    For that matter, is there a way to even list the variables in the current scope?

     

  • No. This is T-SQL. It's not Perl or Python.

    But tell us what you really want to achieve, that is, what drove you to ask these question in the first place? Maybe then we can help you with your real problem.

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • So I'm running a bunch of reports through a cursor. Different DBs, different subsets of the same list of reports.

    A couple of the reports have additional parameters. I was hoping to use sp_executeSQL to call the reports, dynamically extending the [@params =]  and [ @param2 = ] , er..., parameters.

    The goal is to avoid hard-coding the exceptions, but at this point, I really don't see any way around it.

     

  • It's hard to comment without seeing the details. But it may be better to do something like this in a client-side language. Python, Powershell,Perl or C# are all more powerful here that T-SQL is.

    Then again, there is nothing that says that just because you have a parameter @param76 in your parameter list that it must actually appear in your SQL text.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Using sp_executeSql - you can pass all parameters to the dynamic code and only use the parameters needed.  Instead of worrying about 'exceptions' - just pass them all.  The dynamic code that is built will just ignore any parameters not used.

    Declare @sqlCommand nvarchar(max) = 'Select @parm1, @parm2';

    Declare @parm1 int = 1
    , @parm2 int = 2
    , @parm3 int = 3
    , @parm4 int = 4;

    Execute sp_executeSql
    @stmt = @sqlCommand
    , @params = N'@parm1 int, @parm2 int, @parm3 int, @parm4 int'
    , @parm1 = @parm1, @parm2 = @parm2, @parm3 = @parm3, @parm4 = @parm4;

    Set @sqlCommand = 'Select @parm3, @parm4;'

    Execute sp_executeSql
    @stmt = @sqlCommand
    , @params = N'@parm1 int, @parm2 int, @parm3 int, @parm4 int'
    , @parm1 = @parm1, @parm2 = @parm2, @parm3 = @parm3, @parm4 = @parm4;

    If you don't want to do that - then your only other option is to use a conditional.  That can be data-driven based on whatever table/source you are using to drive this procedure, but essentially you would have several different calls to sp_executeSql based on each condition.

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I need to call the sprocs with named parameters, so I ended up just building a command in the cursor declaration.

    I think there was always going to be some hard-code, since I need to know which variable to substitute.

    SELECT' EXECUTE dbo.' + RTRIM(LTRIM(procedure_name)) 
    + ' @cDisplayLang = ''' + @cDisplayLang + ''''
    + ISNULL(', ' + cParmList + ' = '
    + CASEWHEN cParmList = '@nReport_IdArg'
    THEN CAST(@nReport_Id AS VARCHAR(8))
    END, ''),
    cReportColumns
    FROM dbo.reports

    Then I just pass that to EXECUTE()

    Thanks for taking the time.

     

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 10 posts - 1 through 10 (of 10 total)

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