SQL Server stored procedure for dynamic query

Question

I need a procedure for below dynamic query. In that procedure, f_id and value are input parameters. f_id and value are collections of values.

For example

f_id=1780
value=ABC
f_id=22483
value=`sasdfa`
f_id=3334
value=soap

So in the below query and exits part will be added dynamically depends on the count of f_id and value.

select distinct 
    v1.entity_id 
from 
    values v1 
inner join 
    listings l on v1.entity_id = l.entity_id   
where 
    l.c_id = 83  
    and exists (select 1 from values v2 
                where v1.entity_id = v2.entity_id 
                  and v2.f_id = 1780 and (value = ABC)) 
    and exists (select 1 from values v3 
                where v1.entity_id = v3.entity_id  
                  and v3.field_id = 22483 and (value = sasdfa)) 
    and exists (select 1 from values v4 
                where v1.entity_id = v4.entity_id   
                  and v4.field_id = 3334 and (value = soap)) 
    and exists (select 1 from values v5 
                where v1.entity_id = v5.entity_id  
                  and v5.field_id = 3433 and (value=paste)) 
order by 
    l.id desc

score 0 · Accepted Answer · edited May 23 '17 at 12:26

0

You need to create a stored procedure that has the SQL you have provided in your question as a SQL String with parameters and the use the EXEC statement to execute the SQL string.

Below is the example

CREATE PROCEDURE usp_dynamic_sql
(   @f_id AS INT,
    @value AS NVARCHAR(100)
)
AS
BEGIN
    DECLARE @SQL AS NVARCHAR(2000);

    SET @SQL
        =      ('
SELECT distinct 
    v1.entity_id 
from 
    values v1 
inner join 
    listings l on v1.entity_id = l.entity_id   
where 
    l.c_id = 83  
    and exists (select 1 from values v2 
                where v1.entity_id = v2.entity_id 
                  and v2.f_id = ''' + @f_id + ''' and (value = ''' + @value
                + ''')) 
    and exists (select 1 from values v3 
                where v1.entity_id = v3.entity_id  
                  and v3.field_id = 22483 and (value = '''+@value
                + ''')) 
    and exists (select 1 from values v4 
                where v1.entity_id = v4.entity_id   
                  and v4.field_id = 3334 and (value = '''+@value
                + ''')) 
    and exists (select 1 from values v5 
                where v1.entity_id = v5.entity_id  
                  and v5.field_id = 3433 and (value='''+@value+''')) 
order by 
    l.id desc'''
               );
    EXEC (@SQL);
END;

EDIT 2

Here is the link in stackoverflow community. Link 1 Link 2

If you are not able to make it, let me know. I will help you.

edited May 23 '17 at 12:26

Community

1
1

answered Apr 27 '17 at 06:35

Karthik Venkatraman

1,619
4
25
55

Input Parameters(f_id,value) accepts more then one values.(Like array) More then one input values will be passed to this parameters(f_id,value).Like array of values. Sample Inputs f_id=1780 value='abc' f_id=22483 value='xyz' f_id=3334 value='soap' f_id=3433 value='paste' etc..... In the above query and exists (select 1 from values v5 where v1.entity_id = v5.entity_id and v5.field_id=3433 and( value='paste' )) and exists part of sub query will not be fixed.it will be added depends on the count of input parameters. – Ram Apr 27 '17 at 13:58
From where you will be passing the values to the stored procedure? Is it from a web page or from another table? – Karthik Venkatraman Apr 27 '17 at 14:45
it is from web page.Asp.net is front end. – Ram Apr 27 '17 at 17:47
it is from web page.Asp.net is front end.This stored procedure is used for filter application. Filtering the records based on F_id and Value.F_id is the id of column name(like Name,Location....).All column name having id in the table.Value is value of column name (like Raja,USA,)(KUMAR,Mumbai).So more then one i/p parameters will come to procedure.Using this I/P parameters we have to select entity_id. – Ram Apr 27 '17 at 17:59
I have added links that can help you in my answer. – Karthik Venkatraman Apr 28 '17 at 00:40
Please provide link.Shall we use Table valued parameters for this case?.U have any idea or examples for TVP in this case? – Ram Apr 28 '17 at 02:39
I have added the link under edit 2. I am currently travelling. So will not be able to read your post immediately. – Karthik Venkatraman Apr 28 '17 at 09:05
Thank you.Now i am using table valued parameters for this case. – Ram Apr 29 '17 at 10:06
how to mark as correct.Link 1 and Link 2 is helpful. – Ram May 01 '17 at 09:06
You cannot mark as correct for those two links. The links are contained on my answer hence you can mark my answer as correct by clicking on the tick mark. Along with this, click on the link and up vote those answers. This will help them. – Karthik Venkatraman May 02 '17 at 04:37

SQL Server stored procedure for dynamic query

1 Answers1