April 27, 2017 at 12:30 am
I need procedure for below dynamic query.In that procedure f_id and value is input parameters.
f_id and value is collection of values.
f_id int
value nvarchar(max)
for example
f_id=1780
value='ABC'
f_id=22483
value='sasdfa'
f_id=3334
value='soap'
etc.....
So in the below query and exits part will be added dynamically depends on the count of f_id and value.
Query
-----
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
Pls help.
April 27, 2017 at 2:18 am
Dynamic SQl is not really the way to go with this, I would say. How much control do you have over the way the parameters are passed (I.e. in delimited strings, XML)? Are you able to ensure ordering if so?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 27, 2017 at 5:08 am
Need Procedure for executing dynamic query.
===== Input Parameters
f_id int
value nvarchar(max)
===== Input Parameters accepts more then one values.(Like array)
===== Sample Datas for Input Parameters.
f_id=1780
value='abc'
f_id=22483
value='xyz'
f_id=3334
value='soap'
f_id=3433
value='paste'
etc.....
Dyanmic Query
-----------------
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='xyz'))
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
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 be added depends on the count of input parameters.
April 27, 2017 at 6:00 am
jkramprakash - Thursday, April 27, 2017 5:08 AMNeed Procedure for executing dynamic query.
===== Input Parameters
f_id int
value nvarchar(max)
===== Input Parameters accepts more then one values.(Like array)
===== Sample Datas for Input Parameters.f_id=1780
value='abc'
f_id=22483
value='xyz'
f_id=3334
value='soap'
f_id=3433
value='paste'
etc.....Dyanmic Query
-----------------
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='xyz'))
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
In the above queryand 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 be added depends on the count of input parameters.
That doesn't answer my questions. You also seem to be expecting to pass two variable "store them in the ether", pass two variables, "store them in the ether", *rinse and repeat* run a statement for each set up variables. That isn't how SQL works. If a stored procedure is expecting two variables, then that's all you can pass. If you pass more it's not valid syntax, and you can't pass it 2 but tell it "wait, I'm going to pass you two more".
On thinking, we need more information about your application, or what is running the SQL. What are your full needs, what is this for? And, please answer the questions in my previous post.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 27, 2017 at 10:29 am
Take a look at Table Valued Parameters in Books Online maybe??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 27, 2017 at 12:19 pm
This stored procedure is used in filter application.Inputs are coming from web page.ASP.Net is front end. Filtering the records based on input parameters(F_id and Value)
F_id is the id(unique and int data type) of column name(like Name,Location....).Value is value of column name (like Raja,USA,)(KUMAR,Mumbai).So more then one f_id and value parameters will come to procedure.Using this I/P parameters we have to select the records from values table using above query.(dynamically changed depends on the i/p parameters count) How can i use Table valued parameters for this case?.
Example
F_id value
1001 (Name) Raja
kumar
Gopi
1002 (Location) Chennai
Mumbai
April 27, 2017 at 1:29 pm
Use one TVP per ID/Value sets. Then it is a join on both columns for each set. easy/peasy to do. Don't have time to think if this is the only/best way to do it though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 1, 2017 at 5:41 am
Now i created TVP and procedure also.While executing procedure Values are not passed to the query.Please find the procedure and Inputs.
CREATE TYPE TableVariable AS TABLE
(
id int identity(1,1),
field_ids INT,
value VARCHAR(MAX)
)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE Testing
(
@TableVar TableVariable READONLY,
@C_id INT
)
AS
Declare @maxPK INT;
Declare @pk INT;
Declare @fid INT;
Declare @val VARCHAR(max);
Declare @Where VARCHAR(max);
Declare @SQL VARCHAR(MAX);
Set @pk = 1
BEGIN
BEGIN TRY
SET NOCOUNT ON;
Select @maxPK = count(*) From @TableVar
SELECT @maxPK
SELECT @C_id
Set @SQL = 'SELECT DISTINCT v1.e_id from values v1 inner join listings l ON v1.e_id =
l.entity_id WHERE l.c_id='+ @C_id
SELECT @SQL
While @pk <= @maxPK
BEGIN
SELECT @fid= field_ids FROM @TableVar where id=@pk;
SELECT @val= value FROM @TableVar where id=@pk;
SELECT @fid
SELECT @val
SET @SQL += ' and exists (select 1 from values v@pk+1 where v1.e_id = v@pk+1.e_id and v@pk+1.field_id=@fid and(value=@val))'
Select @pk = @pk + 1
END
SELECT @SQL
EXECUTE SP_EXECUTESQL @SQL
END TRY
BEGIN CATCH
END CATCH
END
DECLARE @DepartmentTVP AS TableVariable;
insert into @DepartmentTVP values(14567,'first')
insert into @DepartmentTVP values(145678,'second')
SELECT * FROM @DepartmentTVP
EXEC Testing @DepartmentTVP,83
Table values
(a)listings
id e_id c_id
1 14567 83
2 145678 83
(b) values
id f_id Value e_id
1 1001 first 14567
2 1002 second 145678
May 1, 2017 at 6:02 am
I think you've missed the point of the TVF. You don't need any dynamic SQL if you are using it. Take this somewhat simple example:USE TestDB;
GO
--Create TVF
CREATE TYPE SampleTVF AS TABLE
(id int,
[value] varchar(10));
GO
--Create some a Sample Table and Data
CREATE TABLE SampleData
(f_id int identity(1,1),
[value] varchar(10),
[entity_id] int);
GO
INSERT INTO SampleData ([value], [entity_id])
VALUES
('ABC', 123),
('USDAGF',21621),
('SDFHDSI',3264612),
('HASSN',128),
('AGE',3277),
('ADSJ',6546);
GO
--Create a sample Proc
CREATE PROC SampleProc @TVF SampleTVF READONLY AS
SELECT DISTINCT SD.[Entity_id]
FROM SampleData SD
JOIN @TVF T ON SD.f_id = T.id AND SD.[value] = T.value
GO
--And now test the new TVF and Sample Proc
DECLARE @TVF AS SampleTVF;
INSERT INTO @TVF
VALUES
(1,'ABC'),
(5,'AGE');
EXEC SampleProc @TVF;
GO
--Cleanup
DROP PROC SampleProc;
DROP TYPE SampleTVF;
DROP TABLE SampleData;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 1, 2017 at 7:13 am
Going further on Thom's suggestion as his would assume a unique field_id. Here's one assuming the primary key is composite using columns entity_id, field_id, and value. If that's not a unique value in the table Values, there's an additional step to ensure the correct use of the parameters.
DECLARE @TVP TABLE( field_id int, value varchar(8000)); --This is your parameter, the solution is the rest.
DECLARE @ParameterCount int;
SELECT @ParameterCount = COUNT(*)
FROM @TVP;
SELECT *
FROM (
SELECT v.entity_id
FROM [values] v
JOIN @TVP p ON v.field_id = p.field_id AND v.value = p.value
GROUP BY v.entity_id
HAVING COUNT(*) = @ParameterCount) v
INNER JOIN listings l on v.entity_id = l.entity_id
WHERE l.c_id=83
ORDER BY l.c_id DESC;
No loops, no dynamic sql, no risk on SQL injection. But the main problem remains: this design needs to be changed before you start running into timeouts. Here's a horror story that you don't want to face: https://www.simple-talk.com/opinion/opinion-pieces/bad-carma/
May 3, 2017 at 2:41 am
Thom A - Monday, May 1, 2017 6:02 AMI think you've missed the point of the TVF. You don't need any dynamic SQL if you are using it. Take this somewhat simple example:USE TestDB;
GO
--Create TVF
CREATE TYPE SampleTVF AS TABLE
(id int,
[value] varchar(10));
GO
--Create some a Sample Table and Data
CREATE TABLE SampleData
(f_id int identity(1,1),
[value] varchar(10),
[entity_id] int);
GOINSERT INTO SampleData ([value], [entity_id])
VALUES
('ABC', 123),
('USDAGF',21621),
('SDFHDSI',3264612),
('HASSN',128),
('AGE',3277),
('ADSJ',6546);
GO
--Create a sample Proc
CREATE PROC SampleProc @TVF SampleTVF READONLY ASSELECT DISTINCT SD.[Entity_id]
FROM SampleData SD
JOIN @TVF T ON SD.f_id = T.id AND SD.[value] = T.valueGO
--And now test the new TVF and Sample Proc
DECLARE @TVF AS SampleTVF;INSERT INTO @TVF
VALUES
(1,'ABC'),
(5,'AGE');EXEC SampleProc @TVF;
GO
--Cleanup
DROP PROC SampleProc;
DROP TYPE SampleTVF;
DROP TABLE SampleData;
GO
I tested above logic using TVF.But Result returns from my procedure is different from my actual query result..
--Actual Query
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.field_id=1780
and( value='Smooth As Silk Deep Moisture Shampoo' or value='Smooth As Silk Deeper Moisture Conditioner'))
and exists (select 1 from values v3 where v1.entity_id = v3.entity_id
and v3.field_id=1782 and( value='037-05-1129' ))
--Output
entity_id
223875
--Create Values Table and Data
CREATE TABLE Values
(id int identity(1,1),
field_id int,
value varchar(max),
entity_id int)); insert into values values(1780,'Smooth As Silk Deep Moisture Shampoo',223875)
insert into values values(1780,'Smooth As Silk Deeper Moisture Conditioner',223876)
insert into values values(1782,'037-05-1129',223875)
--Create listings Table and Data
Create Table listings
(id int identity(1,1),
c_id int,
entity_id int))
insert into listings values(83,223875)
insert into listings values(83,223876)
--Create TVF
CREATE TYPE TableVariable AS TABLE
(
id int identity(1,1),
field_ids INT,
value VARCHAR(MAX)
)
--My procedure
ALTER PROCEDURE [dbo].[Testing]
(
@TableVar TableVariable READONLY,
@C_id INT
)
AS
Declare @maxPK INT;
Declare @pk INT;
Declare @fid INT
Declare @val VARCHAR(max);
Set @pk = 1
BEGIN
BEGIN TRY
SET NOCOUNT ON;
Select @maxPK = count(*) From @TableVar
BEGIN
SELECT @maxPk
SELECT distinct v.entity_id
FROM [values] v
INNER JOIN @TableVar t ON v.field_id = t.field_ids
AND v.value =t.value
INNER JOIN listings l on v.entity_id=l.entity_id
WHERE l.c_id=@C_id
END
END TRY
BEGIN CATCH
END CATCH
END
DECLARE @DepartmentTVP AS TableVariable;
insert into @DepartmentTVP values(1780,'Smooth As Silk Deep Moisture Shampoo')
insert into @DepartmentTVP values(1780,'Smooth As Silk Deeper Moisture Conditioner')
insert into @DepartmentTVP values(1782,'037-05-1129')
SELECT * FROM @DepartmentTVP
EXEC Testing @DepartmentTVP,83
output
entity_id
223875
223876
Above procedure returns both entity_ids.But my actual query returns only 223875 entity_id.
As per original query logic above procedure should return only 223875 entity_id
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply