T-SQL help with parameters
-
I have 4 temp tables and the final result I need to retrieve is ONLY ID column results as 1 and 6.
As 1 and 6 ID`s EXISTS in 3 temp tables when I pass the values based on the below criteria.
DECLARE @param1 VARCHAR(25) = NULL
SET @param1 = 'Banana'
DECLARE @param2 VARCHAR(25) = NULL
SET @param2 = 'Parrot'
DECLARE @param3 VARCHAR(25) = NULL
SET @param3 = NULL
DECLARE @param4 VARCHAR(25) = NULL
SET @param4 = 'Fruit'
IF OBJECT_ID('TempDb..#Temp1') IS NOT NULLDROP TABLE #Temp1
CREATE TABLE #Temp1(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp1
VALUES
(1, 'A', 'Apple')
,(2, 'A', NULL)
,(3, 'A', 'Apricot')
,(4, 'A', 'Apple')
,(5, 'A', 'Mango')
,(6, 'A', 'Banana')
,(7, 'A', 'Apple')
,(8, 'A', NULL)
,(9, 'A', 'Apricot')
,(10, 'A', 'Apple')
IF OBJECT_ID('TempDb..#Temp2') IS NOT NULL
DROP TABLE #Temp2
CREATE TABLE #Temp2(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp2
VALUES
(1, 'B', 'Parrot')
,(2, 'B', NULL)
,(3, 'B', 'Cat')
,(4, 'B', 'Cat')
,(5, 'B', 'Cat')
,(6, 'B', 'Parrot')
,(7, 'B', 'Cat')
,(8, 'B', NULL)
,(9, 'B', 'Cat')
,(10, 'B', 'Cat')
IF OBJECT_ID('TempDb..#Temp3') IS NOT NULL
DROP TABLE #Temp3
CREATE TABLE #Temp3(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp3
VALUES
(1, 'C', 'Fly')
,(2, 'C', NULL)
,(3, 'C', 'Walk')
,(4, 'C', 'Run')
,(5, 'C', 'Walk')
,(6, 'C', 'Fly')
,(7, 'C', 'Apple')
,(8, 'C', NULL)
,(9, 'C', 'Run')
,(10, 'C', 'Walk')
IF OBJECT_ID('TempDb..#Temp4') IS NOT NULL
DROP TABLE #Temp4
CREATE TABLE #Temp4(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp4
VALUES
(1, 'D', 'Fruit')
,(2, 'D', NULL)
,(3, 'D', 'Vegetable')
,(4, 'D', 'Fruit')
,(5, 'D', 'Vegetable')
,(6, 'D', 'Fruit')
,(7, 'D', 'Vegetable')
,(8, 'D', NULL)
,(9, 'D', 'Vegetable')
,(10, 'D', 'Vegetable')
SELECT * FROM #Temp1 WHERE GDesc='Banana'
SELECT * FROM #Temp2 WHERE GDesc='Parrot'
SELECT * FROM #Temp3 WHERE GDesc IS NULL
SELECT * FROM #Temp4 WHERE GDesc='Fruit'
DROP TABLE #Temp1
DROP TABLE #Temp2
DROP TABLE #Temp3
DROP TABLE #Temp4
Please let me know if this is not clear and thanks in advance.
-
Excellent job posting ddl and sample data. It is totally unclear what you want as output. If you can explain, this is probably pretty simple.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I need to pass parameter values as per below declare statements.
For @param1 is for #temp1, @param2 is for #temp2, @param3 is for #temp3 and @param4 is for #temp4.
DECLARE @param1 VARCHAR(25) = NULLSET @param1 = 'Banana'
DECLARE @param2 VARCHAR(25) = NULL
SET @param2 = 'Parrot'
DECLARE @param3 VARCHAR(25) = NULL
SET @param3 = NULL
DECLARE @param4 VARCHAR(25) = NULL
SET @param4 = 'Fruit'
SELECT * FROM #Temp1 WHERE GDesc='Banana'SELECT * FROM #Temp2 WHERE GDesc='Parrot'
SELECT * FROM #Temp3 WHERE GDesc IS NULL
SELECT * FROM #Temp4 WHERE GDesc='Fruit'
I need only ID column as output and the result should be as 1 and 6. The ID`s 1 and 6 exists in the 3 temp tables based on the above sql queries.
-
:ermm: from what a read as i understand you then you only need to replace * with ID sorry if im misunderstanding you but looking at your tables am not sure why you would only want ID as if you look at ID 6 in your examples the Gdesc is different
SELECT ID FROM #Temp1 WHERE GDesc='Banana'
SELECT ID FROM #Temp2 WHERE GDesc='Parrot'
SELECT ID FROM #Temp3 WHERE GDesc IS NULL
SELECT ID FROM #Temp4 WHERE GDesc='Fruit'
***The first step is always the hardest *******
-
Yeah that didn't really help explain what you want but maybe something like this?
select t1.ID from #Temp1 t1
join #Temp2 t2 on t2.ID = t1.ID and t2.GDesc = @param2
join #Temp4 t4 on t4.ID = t1.ID and t4.GDesc = @param4
where t1.GDesc = @param1
group by t1.ID
Your sample data and desired output don't match. I don't understand what table 3 has to do with this at all. I am willing and able to help if you can make it clear what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
So you only want the ids that are in common between the result sets? Since #3 didn't return any results, that one is not counted?
I'm not sure the results match the test data for the first 'select' on #temp1.
And then again, I might be wrong ...
David Webb -
Yes..
I only want the ids that are in common between the result sets.
Since #3 didn't return any results, that one is not counted?
Also, If the logic is changed to have no results from #1 then it should not be counted and give the matching ID`s from the rest of the tables.
Sorry if the sample data in the #temp tables is not accurate.
-
Your requirements are incredibly unclear. Nobody can understand what you are trying to do here. Let's take your ddl and sample data and try again.
DECLARE @param1 VARCHAR(25) = 'Banana',@param2 VARCHAR(25) = 'Parrot',
@param3 VARCHAR(25) = NULL,
@param4 VARCHAR(25) = 'Fruit'
IF OBJECT_ID('TempDb..#Temp1') IS NOT NULL
DROP TABLE #Temp1
CREATE TABLE #Temp1(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp1
VALUES
(1, 'A', 'Apple')
,(2, 'A', NULL)
,(3, 'A', 'Apricot')
,(4, 'A', 'Apple')
,(5, 'A', 'Mango')
,(6, 'A', 'Banana')
,(7, 'A', 'Apple')
,(8, 'A', NULL)
,(9, 'A', 'Apricot')
,(10, 'A', 'Apple')
IF OBJECT_ID('TempDb..#Temp2') IS NOT NULL
DROP TABLE #Temp2
CREATE TABLE #Temp2(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp2
VALUES
(1, 'B', 'Parrot')
,(2, 'B', NULL)
,(3, 'B', 'Cat')
,(4, 'B', 'Cat')
,(5, 'B', 'Cat')
,(6, 'B', 'Parrot')
,(7, 'B', 'Cat')
,(8, 'B', NULL)
,(9, 'B', 'Cat')
,(10, 'B', 'Cat')
IF OBJECT_ID('TempDb..#Temp3') IS NOT NULL
DROP TABLE #Temp3
CREATE TABLE #Temp3(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp3
VALUES
(1, 'C', 'Fly')
,(2, 'C', NULL)
,(3, 'C', 'Walk')
,(4, 'C', 'Run')
,(5, 'C', 'Walk')
,(6, 'C', 'Fly')
,(7, 'C', 'Apple')
,(8, 'C', NULL)
,(9, 'C', 'Run')
,(10, 'C', 'Walk')
IF OBJECT_ID('TempDb..#Temp4') IS NOT NULL
DROP TABLE #Temp4
CREATE TABLE #Temp4(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp4
VALUES
(1, 'D', 'Fruit')
,(2, 'D', NULL)
,(3, 'D', 'Vegetable')
,(4, 'D', 'Fruit')
,(5, 'D', 'Vegetable')
,(6, 'D', 'Fruit')
,(7, 'D', 'Vegetable')
,(8, 'D', NULL)
,(9, 'D', 'Vegetable')
,(10, 'D', 'Vegetable')
OK now we have your tables and parameters. What should the output look like and what are the business rules you are trying to follow?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Just a guess. This will give you the common ids and a count of how many tables they were found in.
IF OBJECT_ID('TempDb..#Temp1') IS NOT NULLDROP TABLE #Temp1
CREATE TABLE #Temp1(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp1
VALUES
(1, 'A', 'Banana')
,(2, 'A', NULL)
,(3, 'A', 'Apricot')
,(4, 'A', 'Apple')
,(5, 'A', 'Mango')
,(6, 'A', 'Banana')
,(7, 'A', 'Apple')
,(8, 'A', NULL)
,(9, 'A', 'Apricot')
,(10, 'A', 'Apple')
IF OBJECT_ID('TempDb..#Temp2') IS NOT NULL
DROP TABLE #Temp2
CREATE TABLE #Temp2(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp2
VALUES
(1, 'B', 'Parrot')
,(2, 'B', NULL)
,(3, 'B', 'Cat')
,(4, 'B', 'Cat')
,(5, 'B', 'Cat')
,(6, 'B', 'Parrot')
,(7, 'B', 'Cat')
,(8, 'B', NULL)
,(9, 'B', 'Cat')
,(10, 'B', 'Cat')
IF OBJECT_ID('TempDb..#Temp3') IS NOT NULL
DROP TABLE #Temp3
CREATE TABLE #Temp3(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp3
VALUES
(1, 'C', 'Fly')
,(2, 'C', NULL)
,(3, 'C', 'Walk')
,(4, 'C', 'Run')
,(5, 'C', 'Walk')
,(6, 'C', 'Fly')
,(7, 'C', 'Apple')
,(8, 'C', NULL)
,(9, 'C', 'Run')
,(10, 'C', 'Walk')
IF OBJECT_ID('TempDb..#Temp4') IS NOT NULL
DROP TABLE #Temp4
CREATE TABLE #Temp4(ID INT , GName varchar(5), GDesc varchar(20))
INSERT INTO #Temp4
VALUES
(1, 'D', 'Fruit')
,(2, 'D', NULL)
,(3, 'D', 'Vegetable')
,(4, 'D', 'Fruit')
,(5, 'D', 'Vegetable')
,(6, 'D', 'Fruit')
,(7, 'D', 'Vegetable')
,(8, 'D', NULL)
,(9, 'D', 'Vegetable')
,(10, 'D', 'Vegetable')
GO
create proc parameter_test @p1 varchar(25), @p2 varchar(25), @p3 varchar(25), @p4 varchar(25)
as
declare @numberofresults int
create table #tempid (ID INT , GName varchar(5), GDesc varchar(20), ttable varchar(20))
if @p1 IS NOT NULL
insert #tempid (id,gname,gdesc, ttable)
SELECT id, GName,GDesc, 'temp1' FROM #Temp1 WHERE GDesc=@p1
if @p2 IS NOT NULL
insert #tempid (id,gname,gdesc, ttable)
SELECT id, GName,GDesc, 'temp2' FROM #Temp2 WHERE GDesc=@p2
if @p3 IS NOT NULL
insert #tempid (id,gname,gdesc, ttable)
SELECT id, GName,GDesc, 'temp3' FROM #Temp3 WHERE GDesc=@p3
if @p4 IS NOT NULL
insert #tempid (id,gname,gdesc, ttable)
SELECT id, GName,GDesc, 'temp4' FROM #Temp4 WHERE GDesc=@p4
select @numberofresults = COUNT(distinct(id)) from #tempid
select ID, COUNT(*) from #tempid
group by ID
having COUNT(*) = @numberofresults
RETURN
GO
exec parameter_test 'banana','parrot',NULL,'fruit'
DROP TABLE #Temp1
DROP TABLE #Temp2
DROP TABLE #Temp3
DROP TABLE #Temp4
And then again, I might be wrong ...
David Webb
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply