Variables

Question

Post reply

Variables

schleep

SSChampion

Points: 12582
More actions
April 29, 2021 at 5:20 pm
Go to Answer
#3877489
```
DECLARE @Var1INTEGER  = 12
DECLARE @Var2SYSNAME = '@Var1'
```
Is there any way to get 12 from @Var2?
Thanks!
P
Erland Sommarskog

SSC-Insane

Points: 24038
More actions
April 29, 2021 at 6:10 pm
Answer
#3877513

No.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
May 18, 2021 at 6:36 am

This was removed by the editor as SPAM
May 28, 2021 at 11:54 am

This was removed by the editor as SPAM

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

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

schleep SSChampion Points: 12582 More actions · Answer 1

Thanks for the succinct reply 🙂

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

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]

schleep SSChampion Points: 12582 More actions · Answer 3

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.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 4

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]

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

schleep SSChampion Points: 12582 More actions · Answer 6

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.