1

I have a function dbo.getMyTableBasedOnId() that returns a different cache table names and I want to assign it to a variable to use in a SELECT statement with multiple JOINs. I'm not sure how to set the result of this function to a variable and then use WITH and MERGE INTO (if that is the correct path) instead of JOINs. My attempt below did not work.

DECLARE @MyTableName varchar(50)
SET @MyTableName = (SELECT dbo.getMyTableBasedOnId(123))
SELECT @MyTableName

SELECT i.personName, a.weight
FROM peopleTable1 i
JOIN (select [id], [weight] from @MyTableName) as a
ON a.personName = i.personName
WHERE i.City IN ('New York', 'Atlanta')

Thanks in advance for any help.

june
  • 11
  • 1
  • 1
    Possible duplicate of [Table name as variable](http://stackoverflow.com/questions/2838490/table-name-as-variable) – Ash Jun 08 '16 at 06:50
  • 1
    Is it sample/actual query? because `a` having the `id, weight` are the columns, but you are using `a.personName, a.weight` also – Arulkumar Jun 08 '16 at 06:54
  • Why can't you write such a function that will return you your weight and id? – Ivan Starostin Jun 08 '16 at 06:56

1 Answers1

0

Try This

DECLARE @MyTableName varchar(50)
DECLARE @QUERY NVARCHAR(4000)
SET @MyTableName = (SELECT dbo.getMyTableBasedOnId(123))
SELECT @MyTableName

SET @QUERY='SELECT i.personName, a.weight
FROM peopleTable1 i
JOIN (select [id], [weight] from '+@MyTableName+') as a
ON a.personName = i.personName
WHERE i.City IN (''New York'', ''Atlanta'')'

EXEC SP_EXECUTESQL @QUERY

Thanks

Rajesh Ranjan
  • 537
  • 2
  • 12
  • Thanks so much. How would I use SELECT statement in place of the parameter for the given function (SELECT dbo.getMyTableBasedOnId())? I tried to declare and set the SELECT statement to a variable SET (at)parameter = (SELECT id FROM femaleTable WHERE AGE = 30) and execute that first before (at)query but it did not work. – june Jun 10 '16 at 07:06