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 stackoverflow.com/questions/2245691/… which may be the case, but I want to avoid creating an SP — Mead3000
– Mead3000, Commented Jan 12, 2013 at 11:50

Community · Accepted Answer · 2017-05-23 12:01:58Z

8

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.

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Jan 12, 2013 at 14:27

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

1 Comment

Mead3000 Over a year ago

Naming convention is down to my company. Thanks anyhow

Collectives™ on Stack Overflow

how to assign variable with exec string in sql?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related