Trying to run update with a variable that is a select statement

Question

This is in SQL server 2012. I'm on Sever Management studio. I have multiple servers that have multiple databases that I need to feed it a varying list of Database names to pull back a sum of a size field. At the moment @sql2 updates the BR_ProductionSize to the actual select statement instead of the answer to that statement.

DECLARE @TempCaseArtifactID NVARCHAR(60)
DECLARE @sql2 NVARCHAR(200)
DECLARE @answer NVARCHAR(200)
set @TempCaseArtifactID = (select '[EDDS'+BR_CaseArtifactid+'].[EDDSDBO].[File]' from #BRFindSizes where UpdateCompleted = '2') 
set @sql2 = '(select SUM(size) FROM '+@TempCaseArtifactID + ' where TYPE = 3 and InRepository = 1)'

IF (select BR_sqlServerID from #BRFindSizes where UpdateCompleted = '2') = 1015083
Begin
 update #BRFindSizes
set BR_ProductionSize = @sql2
where UpdateCompleted = '2'
end

user2989408 · Answer 1 · 2014-06-19T00:19:57.167

0

You have to Execute the dynamic statement that you have created, to use the value generated.

What you should do is

DECLARE @sql2 VARCHAR(50) = EXEC '(select CONVERT(VARCHAR, SUM(size)) FROM ' 
                                  + @TempCaseArtifactID 
                                  + ' where TYPE = 3 and InRepository = 1)'

And then

IF (select BR_sqlServerID from #BRFindSizes where UpdateCompleted = '2') = 1015083
BEGIN
    UPDATE #BRFindSizes
    SET BR_ProductionSize = @sql2
    WHERE UpdateCompleted = '2'
END

The same thing goes for @TempCaseArtifactID.

edited Jun 19 '14 at 00:19

answered Jun 19 '14 at 00:13

user2989408

3,127
1
17
15

Whenever I put = EXEC i get 'Incorrect syntax near keyword 'EXEC'. – user3754526 Jun 19 '14 at 00:29
@user3754526 Did you try `sp_executesql`, as suggested by Dan. – user2989408 Jun 19 '14 at 00:34

score 0 · Answer 2 · edited May 23 '17 at 11:49

0

This answer is untested and might have some syntax errors, but it represents the gist of what you want to do.

You have this (actually, I put in the SumOfSizes alias):

set @sql2 = '(select SUM(size) SumOfSizes
FROM '+@TempCaseArtifactID + ' 
where TYPE = 3 and InRepository = 1)'

Which is a string. To the the value, you have to execute that query and put the result into a variable. This is the general idea.

set @valueIWant = SumOfSizes from 
sp_executesql @SQLString @sql2

Then you use the @valueIWant variable in your update query.

For the correct syntax, read this.

edited May 23 '17 at 11:49

Community

1
1

answered Jun 19 '14 at 00:16

Dan Bracuk

20,699
4
26
43

It didnt like '= SumOfSizes'. I've also read your link and concluded that my brain is too frazzled to bring that all together at the moment. Thanks for the help. – user3754526 Jun 19 '14 at 00:38

Trying to run update with a variable that is a select statement

2 Answers2