0

I'm trying to concatenate string parameters to a sp_executesql statement but I can't find a way to do it. It's quite possible I'm not searched the net worth the right words. What I'm trying to to can be exemplified with the following code, where I want the sp_executesql statement to give the same result as my first print statement (print(@t1 + @t2):

declare @r nvarchar(max), @t1 nvarchar(2), @t2 nvarchar(2)
set @t1 = 'AA'
set @t2 = 'BB'
print(@1 + @t2)
set @r = 'print(@t)'
exec sp_executesql @r, N'@t nvarchar(100)', @t = ?

I've tried:

@t = @t1 + @t2

and

@t = concat(@t1, @t2)

My first question: Is this even possible (I guess it is)? In real this is about making adjustments and som calculation on multiple tables. The table names is built up in three parts; prefix, name and suffix. The databases involved and part of the table names change in different ways for every iteration. In this particular case the first iteration is with one database and one table. The second iteration the database is changed and so the table name. The third iteration is with the same database as the second iteration, the same prefix and name, but with a different suffix and so on. This is solved with variables for the different parts of the table names which is concatenated to a "database.schema.table name" for every iteration.

My second question: How should the code be written for this to work (if it's possible)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MarJer
  • 87
  • 2
  • 9
  • Why are you using `sys.sp_executesql` here in the first place? What are you *really* trying to achieve here? `sys.sp_executesql` is normally used when you want to execute dynamic SQL; you have no dynamic SQL here. – Thom A Oct 11 '21 at 14:58
  • I need to loop over several tables and change 5 variables for every loop, among 2 is dependent on the value from 1 of the others and another 4 "global" variables that are static in this part of the code. Today we are solving this with a lot of code which is a nightmare to maintain and I thougt that maybe sp_executesql could be the solution. Otherwise I will solve it with a series of if statements... (or Python if I was allowed... which I'm not). :) – MarJer Oct 11 '21 at 15:06
  • Show us the *real* problem you are trying to solve here, not problem Y. See [XY Problem](http://xyproblem.info) – Thom A Oct 11 '21 at 15:08
  • Did some extra explanation to my first question above. The code is locked in a secure environment and I cant export it from there. – MarJer Oct 11 '21 at 15:32
  • That doesn't explain what you're really trying to achieve, @MarJer . Where is the loop you are talking about and the "global variables" (do you mean like `@@VERSION`?). – Thom A Oct 11 '21 at 15:43
  • The code principle is, in some sence, familiar with heritage between "objects". Some time ago, someone tried to manage this with a lot of variables that build up other variables and then copy this code over and over again. I wantet to change this with a loop. My main problem is that I can't find a way to get variables to "survive" over begin-end or go statements. Solution to that = many many if-statements where I define one variable at a time. With sp_executesql I saw a potential solution to get away with only one if-statement per table since I there can define several variables within one if. – MarJer Oct 12 '21 at 07:44

1 Answers1

1

Like others, I am still not sure what you are trying to do here, but at a guess, I'll suggest trying it like this:

declare @r nvarchar(max), @t1 nvarchar(2), @t2 nvarchar(2)
set @t1 = 'AA'
set @t2 = 'BB'
declare @t nvarchar(max)
set @t = @t1 + @t2        -- <-- added this
print(@t1 + @t2)
set @r = 'print(@t)'
exec sp_executesql @r, N'@t nvarchar(100)', @t = @t  -- <-- and this
RBarryYoung
  • 55,398
  • 14
  • 96
  • 137