Declare dynamic Variables from a table

Question

I want to DECLARE my dynamic SQL Variables form a table. Basically I have a "Assumptions" Table like that

City   BranchID Name
London 101      London Branch
Munich 102      Munich Branch

I've already tried this:

DECLARE @Cit varchar(75)
SET @Lob = 'London'

SET @ID= 'SELECT BranchID FROM dbo.assumptions WHERE city = @city'
SET @Name= 'SELECT Name FROM dbo.assumptions WHERE city= @city'

EXECUTE sp_executesql @ID, N'@city nvarchar(75)', @city= @city
EXECUTE sp_executesql @Name_Net, N'@city nvarchar(75)', @city= @city

This gives me the right result 101 and London Branch

the thing is I want to use those Variables later on eg

SELECT * From Sales where BranchID = @ID

I know how to do that. But right now this gives me

SELECT * From Sales where BranchID = SELECT BranchID FROM dbo.assumptions WHERE city = 'London'

This of course isn't working. I need @ID to result in '101'

Any ideas?

Answer 1

DECLARE @city VARCHAR(75),
        @string1 NVARCHAR(MAX),
        @string2 NVARCHAR(MAX),
        @ID INT,
        @Name VARCHAR(100)

SET @string1= 'SELECT @ID = BranchID FROM dbo.assumptions WHERE city = @city'
SET @string2= 'SELECT @Name = Name FROM dbo.assumptions WHERE city= @city'  

EXECUTE sp_executesql @string1, N'@ID int output, @city nvarchar(75)',@ID OUTPUT, @city= @city
EXECUTE sp_executesql @string2, N'@Name varchar(100) output, @city nvarchar(75)',@Name OUTPUT, @city= @city

SELECT @ID,@Name

P.S. There may be some compilation error as I don;t have this tables actually