SQL check if the table is exists with dynamic query then return output value

Question

Anyone know why my query not work well

ALTER PROCEDURE [dbo].[SPMonStoVal]
    @StoDate varchar(25),
    @StockStatus varchar(2) OUT
AS
    DECLARE 
        @TbName varchar(25)

    SET @TbName = 'zSTO:'+@StoDate

    EXEC('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@TbName+']'') AND type in (N''U''))
       SET @StockStatus = ''1''
    ELSE
       SET @StockStatus = ''0'' ')

Anyone know why my query not work well?

Chanom First · Accepted Answer · 2016-08-23 05:25:57Z

1

Why use Execute text just use simple statement

ALTER PROCEDURE [dbo].[SPMonStoVal]
    @StoDate varchar(25),
    @StockStatus varchar(2) OUT
AS
BEGIN
    DECLARE 
        @TbName varchar(25)

    SET @TbName = 'zSTO:'+@StoDate

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(CAST(@TbName AS NVARCHAR(100))) AND type in (N'U'))
       SET @StockStatus = '1'
    ELSE
       SET @StockStatus = '0'
END

answered Aug 23, 2016 at 5:25

Chanom First

1,1561 gold badge12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bagong Over a year ago

i thought if the table was dynamic so must use execute text.

Collectives™ on Stack Overflow

SQL check if the table is exists with dynamic query then return output value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related