CODEWITHSUNDEEP
t-sqlsp-executesql
MarJer
MarJer

Reputation: 107

How can I concatenate stringvalues in a parameter definition for a sp_executesql statement?

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)?

Upvotes: 0

Views: 586

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56735

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

Upvotes: 1

Related Questions