Dynamically creating variables in SQL Server

Question

Good Afternoon All,

Can anyone advise if I can dynamically declare and assign values to variables in the scenario described below?

I've written a stored procedure (sproc) that calculates the % of members in subgroups of an organization.

I know there are 7 subgroups. I store the results of the percentage calculation in 7 variables which I use later in the sproc. Each variable is named according to the subgroup name.

Naturally this means if there are changes in the name or number of subgroups, I have to rewrite parts of the sproc.

I believe dynamic SQL could be used to allow the sproc to adjust to changes in the subgroups, but I'm not sure how to set up dynamic SQL for this scenario. Can anyone offer an example or guidance?

Dynamic SQL is usually not the solution to sql problems. Like cursors, it has its place, but its easy to screw up, difficult to maintain, and vulnerable to injection attacks. Consider using a temp table (or table variable) that has a row for each "variable" instead of using standard sql variables. — StingyJack
– StingyJack, Commented Apr 22, 2016 at 16:20

Clay · Accepted Answer · 2016-04-22 16:33:17Z

1

What you're proposing goes against the grain in Sql Server. Your concern about having to rewrite later kinda speaks to this...so you're on the right track to be concerned.

Generally, you'd want to make your results into some kind of set-oriented thing...table-like...where one column has the name of the subgroup and the other column has the calculated value.

You might find table-valued functions more appropriate for your problem...but it's hard to say...we're not deep on specifics in this question.

Dynamic SQL is almost always the last resort. It seems fun, but has all sorts of issues...not the least of which is addressing the results in a programmatically safe and consistent way.

answered Apr 22, 2016 at 16:33

Clay

5,1141 gold badge30 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FLICKER · Accepted Answer · 2016-04-22 16:30:57Z

0

You can follow this simple query to see how you can do that

declare @sql nvarchar(max) = ''
declare @outerX int = 0 -- this is your variable you want to set it from dynamic SQL 
declare @i int = 0 -- for loop

while @i <= 6 
begin
  set @sql = 'select @x = ' + CAST(@i as varchar)
  exec sp_executesql @sql, N'@x int OUTPUT', @x = @outerX output
  set @i = @i + 1
  print @outerX
end

Output will be

More Detail Here

answered Apr 22, 2016 at 16:30

FLICKER

6,7434 gold badges52 silver badges83 bronze badges

Collectives™ on Stack Overflow

Dynamically creating variables in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related