akhrot akhrot - 3 months ago 11
SQL Question

Dynamic sql inside stored Procedure-sql server 2008

The below when i tried to execute throw an error


Msg 208, Level 16, State 0, Procedure CallView, Line 11
Invalid object name '#table'.


CREATE PROCEDURE [dbo].[CallView]
AS
SET NOCOUNT ON;
declare @name as varchar (max)

select @name = VIEW_NAME from ZBT_MAPPING_TABLE MT
INNER JOIN OUTPUT_DL DL ON MT.COMPID = DL.COMPID


-- insert to temp table
INSERT INTO #table
(
customerID,
PRODUCT,
PRODUCT_PRICE
)
EXEC (N'select * from ' + @name)


when i execute it

exec [dbo].[CallView]


it throwing error as


Invalid object name '#table'.**


may someone please suggest what is going wrong here and how can we rectify it.
Thanks

Answer

Try like this,

CREATE PROCEDURE [dbo].[CallView]
AS
SET NOCOUNT ON;

DECLARE @name AS VARCHAR(max)

CREATE TABLE #table (
    customerID INT Identity(1, 1)
    ,PRODUCT VARCHAR(100)
    ,PRODUCT_PRICE DECIMAL(18, 2)
    )

SELECT @name = VIEW_NAME
FROM ZBT_MAPPING_TABLE MT
INNER JOIN OUTPUT_DL DL ON MT.COMPID = DL.COMPID

-- insert to temp table
INSERT INTO #table (
    customerID
    ,PRODUCT
    ,PRODUCT_PRICE
    )
EXEC (N'select * from ' + @name)

select *from  #table

drop table  #table