How to use a stored procedure to return a table name

Question

I have the following stored procedure that returns a row on a table, which I then want to input into an SQL statement as a table name.

Stored Procedure:

Create Procedure TLookup as
select distinct [BillingReferenceData].[dbo].[Customers].[Tariff]
from [daisybilling].[dbo].[APRW14_FFA68878_Calls]
inner join [BillingReferenceData].[dbo].[Customers] on [DaisyBilling].[dbo].[APRW14_FFA68878_Calls].[Customer Lookup] = [BillingReferenceData].[dbo].[Customers].[Customer ID]
where [DaisyBilling].[dbo].[APRW14_FFA68878_Calls].[Customer Lookup] = [BillingReferenceData].[dbo].[Customers].[Customer ID]

Here is the remainder of my code, I have highlighted "INSERT TABLE NAME HERE" where I need to input the table name collected in the above procedure.

UPDATE [APRW14_FFA68878_Calls] 
SET    [APRW14_FFA68878_Calls].[Sell Price] = [INSERT TABLE NAME HERE].[Peak]/60*[APRW14_FFA68878_Calls].[Duration (secs)]
FROM   [Test].[dbo].[DaisyRates_May2014]
INNER JOIN [DaisyBilling].[dbo].[APRW14_FFA68878_Calls] on [DaisyBilling].[dbo].[APRW14_FFA68878_Calls].[ChargeCode] = [Test].[dbo].[INSERT TABLE NAME HERE].[Chargecode]

What is the correct syntax in order to achieve this?

Thanks

Unrelated but you should learn about aliases. They'd make your query much easier to read. e.g. instead of `SELECT db.dbo.table.column FROM db.dbo.table` use `SELECT t.column FROM db.dbo.table AS t` — OGHaza, May 27 '14 at 14:25

Dave.Gugg · Answer 1 · 2014-05-27T15:32:36.680

0

Once you have assigned the result of the stored procedure (the table name) to a variable, you'll have to use dynamic sql to insert it into your update statement, like this:

DECLARE @sql NVARCHAR(max)

SELECT @sql = 'UPDATE [APRW14_FFA68878_Calls] 
SET    [APRW14_FFA68878_Calls].[Sell Price] = ' + @tablename + '.[Peak]/60*[APRW14_FFA68878_Calls].[Duration (secs)]
FROM   [Test].[dbo].[DaisyRates_May2014]
INNER JOIN [DaisyBilling].[dbo].[APRW14_FFA68878_Calls] on [DaisyBilling].[dbo].[APRW14_FFA68878_Calls].[ChargeCode] = [Test].[dbo].[INSERT TABLE NAME HERE].[Chargecode]'

EXEC sp_executesql @sql

edited May 27 '14 at 15:32

answered May 27 '14 at 14:57

Dave.Gugg

6,561
3
24
43

Thanks, but when using this method I get "Cannot call methods on varchar" for the @tablename parameter – user3580480 May 27 '14 at 15:22
That's right, it needs to be nvarchar. I have corrected the script. – Dave.Gugg May 27 '14 at 15:32
Thanks, that's working now - How do I store the result of the stored procedure (the table name) to a variable? – user3580480 May 27 '14 at 16:22
See http://stackoverflow.com/questions/2245691/how-to-assign-an-exec-result-to-a-sql-variable – Dave.Gugg May 27 '14 at 16:26

How to use a stored procedure to return a table name

1 Answers1