how to execute dynamic query based on the value of dynamic query result

Question

I have below SP and want to excute query 3 when the value of query 2 = 0 or null and want to return values of query 1 and query 2/query 3

When I execute the SP I have a value in @val1 = 50 but still it is going inside the if loop and i want to return only @val, @val1 but i am getting 5 values

exec usp_GetDefaultValue 'HQP','home','ConDefault','12345'

Results:

HQP: 0 // query 1 HQP: 50 //query 2 @val: 0 @val1: 0 can anybody help me in this

ALTER PROCEDURE [dbo].[usp_GetDefaultValue]

    @Service varchar(20), @LOB varchar(10), @Scenario varchar(20), @Num varchar(10)

AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @val as int
    DECLARE @val1 as int
    DECLARE    @Value  AS NVARCHAR(MAX)
    DECLARE    @DefValue  AS NVARCHAR(MAX)

    set @Value =  'SELECT '  + @Service + ' from PDetail where LOB = ''' + @LOB  + ''' and Scenario =  ''' + @Scenario + '''  and Number = ''' + @Num + ''' '  -- query 1
    set @DefValue =  'SELECT '  + @Service + ' from PDetail where LOB = ''' + @LOB  + ''' and Scenario =  ''none''  and Number =''none''  and pid = 1'         -- query 2

    exec @val = sp_executesql @Value
    exec @val1 = sp_executesql @DefValue

    if (@val1 = 0 or @val1 = null)  
        begin
            set @DefValue = null
            set @DefValue =  'SELECT '  + @Service + ' from PDetail where LOB = ''' + @LOB  + ''' and Scenario =  ''none''  and pid = 2'               -- query 3
            exec @val1 = sp_executesql @DefValue
        end

    select @val,@val1  -- want to return only two values

END

Whenever I see questions like this I always think the main problem is a poor table design/structure. — Sherman
– Sherman, Commented Jul 6, 2015 at 20:33
Please read Erland Sommarskog's The Curse and Blessings of Dynamic SQL. Your code is at risk for SQL injection. — Brent D
– Brent D, Commented Jul 7, 2015 at 0:00

Jeffrey Wieder · Accepted Answer · 2015-07-06 20:25:43Z

2

I suggest that you change

  if (@val1 = 0 or @val1 = null)

to

  if (@val1 IS NULL or @val1 = 0)

Remember that this is SQL, the proper way to check for null is with IS NULL

answered Jul 6, 2015 at 20:25

Jeffrey Wieder

2,3841 gold badge16 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

how to execute dynamic query based on the value of dynamic query result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related