how to assign variable with exec string in sql?

Question

I would like to assign a variable with what is returned from and "exec (string)" but am struggling with syntax. Below is some working code...

declare @iGeographyLevel int = 2
declare @iGeographyLevelID int = 64
declare @sGeographyName varchar(30)

declare @sSQL nvarchar(max)

set @sSQL = '
select Name
    from GeographyLevel'+ cast(@iGeographyLevel as varchar(5))+'
    where GeographyLevel'+ cast(@iGeographyLevel as varchar(5)) + 'ID = '+ cast(@iGeographyLevelID as varchar(5))

exec (@sSQL)

I would like to do something like...

set @sGeographyName  = exec (@sSQL)

Some might say it is a duplicate of http://stackoverflow.com/questions/2245691/how-to-assign-an-exec-result-to-a-sql-variable which may be the case, but I want to avoid creating an SP — Mead3000, Jan 12 '13 at 11:50

score 8 · Accepted Answer · edited May 23 '17 at 12:01

sp_executesql is the best way of doing this.

DECLARE @iGeographyLevel INT = 2
DECLARE @iGeographyLevelID INT = 64
DECLARE @sGeographyName VARCHAR(30)
DECLARE @sSQL NVARCHAR(max)

SET @sSQL = '
SELECT @sGeographyName = Name
FROM   GeographyLevel' + cast(@iGeographyLevel AS VARCHAR(5)) + '
WHERE  GeographyLevel' + cast(@iGeographyLevel AS VARCHAR(5)) + 'ID = @iGeographyLevelID'

EXEC sp_executesql
  @sSQL,
  N'@iGeographyLevelID INT, @sGeographyName VARCHAR(30) OUTPUT',
  @iGeographyLevelID,
  @sGeographyName OUTPUT

SELECT @sGeographyName

The requirement for dynamic SQL here is a bit smelly though. Not sure why you don't just have one Geography table with a Level column (or possibly a partitioned view if the tables must be separate) Also Hungarian notation for variable names is widely discouraged these days.

Naming convention is down to my company. Thanks anyhow – Mead3000 Jan 14 '13 at 09:26 — Mead3000, Jan 14 '13 at 09:26

how to assign variable with exec string in sql?

1 Answers1