Pass a table name to stored procedure as a parameter

Question

This is my stored procedure, this is working:

/* 
    @CRM_Ref - This is the only required input port for this SP
    @North_Ref - Output port, returns the North_Ref for a valid CRM_Ref input
    @Output_Message - Output port, Returns the output of the SP, either 'Success' or 'North_Ref not found'
*/

ALTER PROCEDURE [dbo].sp_Get_North_Reference 
    @CRM_Ref NVARCHAR(255), 
    @North_Ref VARCHAR(255) OUTPUT, 
    @Output_Message VARCHAR(255) OUTPUT
AS
    DECLARE @var_North_Ref VARCHAR(255); -- Variable used to store the North_Ref
    DECLARE @var_Output_Message VARCHAR(255); -- Variable to carry the Output_Message
    DECLARE @COUNTER INT; -- Counter for the amount of times the while loop should run

    SET @COUNTER = 100;

    -- Loop will run 10 times with a 10 second delay between each loop
    WHILE @COUNTER >= 1
    BEGIN
        SET @var_North_Ref = (SELECT TOP 1 North_Ref FROM DEV.dbo.Address__ADDRESS WHERE CRM_Ref =  @CRM_Ref ORDER BY PUBLICATION_INSTANCE_DATE DESC)
        IF @var_North_Ref IS NULL
        BEGIN
            SET @COUNTER = @COUNTER - 1; -- Counter is decremented by 1
            SET @var_Output_Message = 'North_Ref not found';
            WAITFOR DELAY '00:00:10'; -- Wait is triggered if no North_Ref is found
        END
        ELSE
        BEGIN
            SET @COUNTER = 0; -- Counter is set to 0 to end the while loop
            SET @var_Output_Message = 'Success';
        END
    END

SET @Output_Message =  @var_Output_Message; -- Format Output_Message
SET @North_Ref = @var_North_Ref; -- Format North_Ref

;

GO

I would like to add another parameter into this stored procedure (@TableName VARCHAR(255)) which I want to pass to the SELECT statement.

So I would like something like:

SELECT TOP 1 North_Ref 
FROM @Table_Name 
WHERE CRM_Ref =  @CRM_Ref 
ORDER BY PUBLICATION_INSTANCE_DATE DESC

I have tried doing this as it is above but I am getting errors as I don't think you can use parameters as a table name in stored procedures

You will need [dynamic SQL](https://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement) for that. — Rigerta, Nov 27 '17 at 17:41
You would have to use dynamic sql if you pass in the table name. I always get concerned when I see this that using a column in the table would be better than different tables all holding the same data. And that while loop waiting for 10 seconds is huge red flag to me. Last but not least you should read this. http://sqlperformance.com/2012/10/t-sql-queries/sp_prefix — Sean Lange, Nov 27 '17 at 17:42
@RigertaDemiri are you able to provide an example? thank you — jeffry, Nov 27 '17 at 17:42
The thread linked on my comment is one simple example. Good luck! — Rigerta, Nov 27 '17 at 17:44
The issue is that data access needs to be fast. The way you have written this is that it will delay execution for 10 seconds. Why???? Are you trying to slow down the system for no apparent reason? Why not change up the logic so you don't need to loop through your table? Just select the row you want the first time and be done with it. If there is no row found then there is no row found. Delaying for 10 seconds and trying again is a bit strange. — Sean Lange, Nov 27 '17 at 17:47
Possible duplicate of [How to take table name as an input parameter to the stored procedure?](https://stackoverflow.com/questions/22105121/how-to-take-table-name-as-an-input-parameter-to-the-stored-procedure) — Tab Alleman, Nov 27 '17 at 19:08
@SeanLange I have 2 processes running at the same time, I need to wait for process 1 to complete before I can run process 2. Process 1 will update the table I am querying, once updated I can run process 2. My thinking was that I would wait 10 seconds instead of running the loop constantly without any time delay as it would reduce the amount of reads. Is there a better approach to this? — jeffry, Nov 28 '17 at 08:40

Ardi Ardianto · Accepted Answer · 2017-11-28T05:38:34.780

1

how about try this concept :

CREATE TABLE #tempTable(abc int);
declare @strSQL nvarchar(255)
SET @strSQL = 'insert into #tempTable select 123'
EXEC sp_executesql @strSQL
declare @abc int
select top 1 @abc = abc from #tempTable
drop table #tempTable
select @abc

edited Nov 28 '17 at 05:38

answered Nov 28 '17 at 05:04

Ardi Ardianto

99
6

Pass a table name to stored procedure as a parameter

1 Answers1