Tsql help on Passing values as parameters

Question

Post reply

Tsql help on Passing values as parameters

etirem

SSCommitted

Points: 1655
More actions
June 17, 2012 at 5:35 pm

#258182

Hi all,

Below is the data in the temp table and I have to pass 4 parameters and 4 values to get the results as:

The main criteria is Cat > 200, Horse < 40, Lion > 100 and Tiger > 150.

DECLARE @Result1 NVARCHAR(100),
@ResultValue1 NVARCHAR(100), @Result2 NVARCHAR(100), @ResultValue2 NVARCHAR(100), @Result3 NVARCHAR(100), @ResultValue3 NVARCHAR(100), @Result4 NVARCHAR(100), @ResultValue4 NVARCHAR(100) SET @Result1 = 'Cat' SET @ResultValue1 = 200--(Greater Than 200) SET @Result2 = 'Horse' SET @ResultValue2 = 40 --(Less Than 40) SET @Result3 = 'Lion' SET @ResultValue3 = 100 --(Greater Than 100) SET @Result4 = 'Tiger' SET @ResultValue4 = 150 --(Greater Than 150) IF object_id ('Tempdb..#tTable') IS NOT NULL DROP TABLE #tTable CREATE TABLE #tTable(ColName NVARCHAR(100), ColVal VARCHAR(100)) INSERT INTO #tTable(ColName, ColVal) VALUES ('a', '100') ,('aa', 'Nothing') ,('b', '120') ,('bb', 'NoResult') ,('Cat', '190') ,('Cat', '400') ,('Cat', '200') ,('Cat', '210') ,('d', 'Nothing') ,('e', '100') ,('i', '80') ,('f', 'Nothing') ,('Horse', '60') ,('Horse', '20') ,('Horse', '40') ,('Horse', '30') ,('Lion', '100') ,('Lion', '120') ,('s', 'Nothing') ,('Tiger', '140') ,('Tiger', '150') ,('Tiger', '160') ,('Tiger', '180') ,('y', 'NoResult') SELECT * FROM #tTable
DROP TABLE #tTable

Thanks all!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

etirem SSCommitted Points: 1655 More actions · Answer 1

etirem

SSCommitted

Points: 1655

June 17, 2012 at 7:19 pm

#1502059

The main criteria can be AND/OR i:e

Cat > 200 AND/OR

Horse < 40 AND/OR

Lion > 100 AND/OR

Tiger > 150

Dwain Camps SSC Guru Points: 86913 More actions · Answer 2

No need to pass 4 parameters. Below, I haven't created your SP but have given you everything you need to do so.



DECLARE @XML1 XML, @XML2 XML

IF object_id ('Tempdb..#pTable') IS NOT NULL

DROP TABLE 	#pTable	

CREATE TABLE #pTable(ColName NVARCHAR(100), ColVal VARCHAR(100))

INSERT INTO #pTable

VALUES ('Cat', 200), ('Horse', 40), ('Lion', 100), ('Tiger', 150)

IF object_id ('Tempdb..#tTable') IS NOT NULL

DROP TABLE 	#tTable	

CREATE TABLE #tTable(ColName NVARCHAR(100), ColVal VARCHAR(100))

INSERT INTO #tTable(ColName, ColVal)  VALUES ('a', '100')

,('aa', 'Nothing')

,('b', '120')

,('bb', 'NoResult')

,('Cat', '190')

,('Cat', '400')

,('Cat', '200')

,('Cat', '210')

,('d', 'Nothing')

,('e', '100')

,('i', '80')

,('f', 'Nothing')

,('Horse', '60')

,('Horse', '20')

,('Horse', '40')

,('Horse', '30')

,('Lion', '100')

,('Lion', '120')

,('s', 'Nothing')

,('Tiger', '140')

,('Tiger', '150')

,('Tiger', '160')

,('Tiger', '180')

,('y', 'NoResult')

SELECT * FROM #tTable

SET @XML1 = (

    SELECT ColName, ColVal

    FROM #tTable

    FOR XML PATH('R'), ROOT('X') )

SET @XML2 = (

    SELECT ColName, ColVal

    FROM #pTable

    FOR XML PATH('R'), ROOT('X') )

-- Display the two variables to pass into your SP

SELECT @XML1, @XML2

-- Now create an SP, pass in the two above values

-- and do something like this

SELECT z1.ColName, z1.ColVal

FROM (

    SELECT ColName=y.value('ColName[1]', 'NVARCHAR(100)')

        ,ColVal=y.value('ColVal[1]', 'VARCHAR(100)')

    FROM (SELECT @xml1 AS XML1) x CROSS APPLY XML1.nodes('X/R') t(y)) z1

INNER JOIN (

    SELECT ColName=y.value('ColName[1]', 'NVARCHAR(100)')

        ,ColVal=y.value('ColVal[1]', 'VARCHAR(100)')

    FROM (SELECT @xml2 AS XML2) x CROSS APPLY XML2.nodes('X/R') t(y)) z2

    ON z1.ColName = z2.ColName and z1.ColVal > z2.ColVal

DROP TABLE #tTable, #pTable

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

etirem SSCommitted Points: 1655 More actions · Answer 3

etirem

SSCommitted

Points: 1655

June 17, 2012 at 7:46 pm

#1502062

I need to pass the values in SSRS report for the parameters. And here you are hardcoding the required values into #pTable but need to pass them. Please help.

Dwain Camps SSC Guru Points: 86913 More actions · Answer 4

etirem (6/17/2012)

I need to pass the values in SSRS report for the parameters. And here you are hardcoding the required values into #pTable but need to pass them. Please help.

I don't see what I've done as hardcoding anything.

Pass in @XML2, which contains the parameters as you have (temporarily) stored them in #pTable.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

etirem SSCommitted Points: 1655 More actions · Answer 5

etirem

SSCommitted

Points: 1655

June 17, 2012 at 8:04 pm

#1502065

The below values need to be passed as parameters from SSRS report and these values may change everytime the report is ran. So they cannot be put into a #pTable.

('Cat', 200), ('Horse', 40), ('Lion', 100), ('Tiger', 150)

Dwain Camps SSC Guru Points: 86913 More actions · Answer 6

etirem (6/17/2012)

The below values need to be passed as parameters from SSRS report and these values may change everytime the report is ran. So they cannot be put into a #pTable.

('Cat', 200), ('Horse', 40), ('Lion', 100), ('Tiger', 150)

I happened to use a VALUES set to INSERT into #pTable with. But there's nothing saying you can't INSERT them into #pTable using whatever local variables you have them stored in using INSERT/SELECT/UNION ALL SELECT.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Dwain Camps SSC Guru Points: 86913 More actions · Answer 7

Like this:



DECLARE @Result1 NVARCHAR(100),

		 @ResultValue1 NVARCHAR(100),

		 @Result2 NVARCHAR(100),

		 @ResultValue2 NVARCHAR(100),

		 @Result3 NVARCHAR(100),

		 @ResultValue3 NVARCHAR(100),

		 @Result4 NVARCHAR(100),

		 @ResultValue4 NVARCHAR(100)

SET @Result1 = 'Cat'

SET @ResultValue1 = 200--(Greater Than 200)

SET @Result2 = 'Horse'

SET @ResultValue2 = 40 --(Less Than 40)

SET @Result3 = 'Lion'

SET @ResultValue3 = 100 --(Greater Than 100)

SET @Result4 = 'Tiger'

SET @ResultValue4 = 150 --(Greater Than 150)

IF object_id ('Tempdb..#pTable') IS NOT NULL

DROP TABLE 	#pTable	

CREATE TABLE #pTable(ColName NVARCHAR(100), ColVal VARCHAR(100))

--INSERT INTO #pTable

--VALUES ('Cat', 200), ('Horse', 40), ('Lion', 100), ('Tiger', 150)

INSERT INTO #pTable

SELECT @Result1, @ResultValue1

UNION ALL SELECT @Result2, @ResultValue2

UNION ALL SELECT @Result3, @ResultValue3

UNION ALL SELECT @Result4, @ResultValue4

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

etirem SSCommitted Points: 1655 More actions · Answer 8

But can I get the results without using XML and pass them as variables as below.



DECLARE @Result1 NVARCHAR(100),

		 @ResultValue1 NVARCHAR(100),

		 @Result2 NVARCHAR(100),

		 @ResultValue2 NVARCHAR(100),

		 @Result3 NVARCHAR(100),

		 @ResultValue3 NVARCHAR(100),

		 @Result4 NVARCHAR(100),

		 @ResultValue4 NVARCHAR(100)

SET @Result1 = 'Cat'

SET @ResultValue1 = 200--(Greater Than 200)

SET @Result2 = 'Horse'

SET @ResultValue2 = 40 --(Less Than 40)

SET @Result3 = 'Lion'

SET @ResultValue3 = 100 --(Greater Than 100)

SET @Result4 = 'Tiger'

SET @ResultValue4 = 150 --(Greater Than 150)

Dwain Camps SSC Guru Points: 86913 More actions · Answer 9

etirem (6/17/2012)

But can I get the results without using XML and pass them as variables as below.

Presumably so but IMHO it is messier.

Personally, I like to pass as few arguments as necessary to any SP.

Perhaps someone with more experience using SSRS can help you further.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

etirem SSCommitted Points: 1655 More actions · Answer 10

etirem

SSCommitted

Points: 1655

June 17, 2012 at 9:17 pm

#1502077

I tried your logic but the result should be

Your result is:

Dwain Camps SSC Guru Points: 86913 More actions · Answer 11

That's because I didn't notice that Horse is the exception where it should be < while the others are greater than.

You can change the logic on the INNER JOIN to use a series of ANDs and ORs to fix that issue. Something like this:



    ON z1.ColName = z2.ColName and

        ((z1.ColName <> 'Horse' AND z1.ColVal > z2.ColVal) OR

        (z1.ColName = 'Horse' AND z1.ColVal < z2.ColVal))

Suggest you play around a little to understand the SQL I've provided you.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St