Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using multiple variables in a Query / Stored Procedure Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 10:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, Visits: 48
Hello all,

I've looked online and have found numerous articles pertaining to my issue here, I'm not exactly sure that it correlates perfectly, however.

The below query is functional and works properly, however I want to be able to enter 'BUY,SELL' for @TC and have it output the data for both BUY and SELL with regards to column 'TransCode'

when @TC = BUY it will spit out the proper data when TransCode = Buy

when @TC = SELL it will also spit out the proper data when TransCode = SELL

However, I'm looking for the ability to combine both 'BUY,SELL' for @TC and have it return both BUY and SELL.

Essentially I'm looking for the ability to eventually have my WHERE clause read:

WHERE @TC in ('BUY','SELL','CONTRIBUTION') and have it return data when multiples are selected

There are many other variables in the TransCode field other than BUY and SELL (HOLD, CONTRIBUTION, ON ACCOUNT etc..) so bringing back the entire column is not an option... any ideas?



declare @idnum varchar
select @idnum = 1
declare @TC varchar(50)
select @TC = 'BUY'


IF @TC = 'ALL'
BEGIN
SELECT @TC as TC, @idnum as IDNum, B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = 1
ORDER BY SecID
END

ELSE

SELECT @TC as TC, @idnum as IDNum, B.GroupDetail, C.SecID, C.TransCode, C.Quantity, B.GroupId, C.PortfolioID
FROM ERTutTransactions C
INNER JOIN FinalGroupDetail B
ON C.PortfolioID = B.PortfolioID
AND B.GroupId = 1
WHERE TransCode = @TC
ORDER BY SecID
END
Post #1428686
Posted Friday, March 8, 2013 10:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 4:54 AM
Points: 2,080, Visits: 1,691
Can you just look for TransCode being a substring of @TC?
Post #1428692
Posted Friday, March 8, 2013 10:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, Visits: 48
I'm not sure -- just started using SQL about a week ago. I will research and see if that will work.
Post #1428696
Posted Friday, March 8, 2013 11:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 8:06 AM
Points: 21, Visits: 48
So I'm able to create three separate query's to pull BUY SELL and CONTRIBUTION from the string, but now that I've written these queries, how is it that they prove beneficial?
Post #1428721
Posted Friday, March 8, 2013 1:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 13,058, Visits: 11,884
Multiple values in a single parameter don't quite work like that. It is kind of a pain to deal with. However, there is really slick way to parse the value of your parameter into a table. In fact, it is quite useful anytime you need to parse a delimited string.

Since you didn't post any ddl or sample data I created some based on some other data that I happened to have. This example uses name and State but the concept is much the same. This should do a decent job of demonstrating how this can work.

if object_id('tempdb..#Names') is not null
drop table #Names

create table #Names
(
FName varchar(25),
ST char(2)
)

insert #Names
select 'Francisco', 'PA' union all
select 'Samuel', 'PA' union all
select 'Georgia', 'WA' union all
select 'James', 'MO' union all
select 'Martha', 'MO' union all
select 'Connie', 'PA' union all
select 'Carol', 'PA' union all
select 'Billy', 'WA' union all
select 'Ruby', 'WA' union all
select 'Steve', 'LA'

--Now we have our example data so we can begin looking the actual problem

declare @TC varchar(100) = 'PA,MO'

select *
from #Names n
inner join dbo.DelimitedSplit8K(@TC, ',') s on n.ST = s.Item

--now we only sent in a single value
set @TC = 'PA'

select *
from #Names n
join dbo.DelimitedSplit8K(@TC, ',') s on n.ST = s.Item

Now this is NOT going to work on your system without a little effort on your part first. You can find the code for the DelimitedSplit8K function by following the link in my signature about splitting strings.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1428745
Posted Friday, March 8, 2013 1:17 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 645, Visits: 3,773
There are two approaches to this problem that I am aware of.

You procedure is passed one string parameter like 'BUY','SELL','CONTRIBUTION' and then the string is split within the procedure into its individual components.

You use a table valued parameter(TVP). Think of this as passing one parameter to the procedure but that parameter is a table containing multiple records. You take this TVP and insert into a temp table. Then you join your results to the temp table.

This thread has some sample code I wrote that you may be able to adapt.

http://www.sqlservercentral.com/Forums/Topic1382201-392-1.aspx#bm1382230

Or search other articles on TVPs.
Post #1428749
Posted Friday, March 8, 2013 1:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 13,058, Visits: 11,884
Chrissy321 (3/8/2013)
There are two approaches to this problem that I am aware of.

You procedure is passed one string parameter like 'BUY','SELL','CONTRIBUTION' and then the string is split within the procedure into its individual components.

You use a table valued parameter(TVP). Think of this as passing one parameter to the procedure but that parameter is a table containing multiple records. You take this TVP and insert into a temp table. Then you join your results to the temp table.

This thread has some sample code I wrote that you may be able to adapt.

http://www.sqlservercentral.com/Forums/Topic1382201-392-1.aspx#bm1382230

Or search other articles on TVPs.


The TVP approach would work to. However you do not need to insert into a temp table. If you pass a TVP you can just join directly to it like you would any other table.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1428754
Posted Friday, March 8, 2013 2:11 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 645, Visits: 3,773
Sean is correct....

--Create your type
CREATE TYPE TCType AS TABLE
(
TranCode varchar(20)
)

--Create some sample data
CREATE TABLE Test (TranCode varchar(20))
INSERT INTO Test (TranCode) VALUES ('BUY')
INSERT INTO Test (TranCode) VALUES ('SELL')
INSERT INTO Test (TranCode) VALUES ('CONSTRAIN')
INSERT INTO Test (TranCode) VALUES ('HOLD')
INSERT INTO Test (TranCode) VALUES ('ON ACCOUNT')

--Create your procedure

CREATE PROCEDURE TestProc
@TCType TCType READONLY
AS

SELECT
*
FROM Test T
INNER JOIN @TCType TC
ON T.TranCode = TC.TranCode

GO
--Decalre your variable populate it and pass it to the procedure
DECLARE @TranCodes AS TCType
INSERT INTO @TranCodes SELECT 'BUY' UNION SELECT 'HOLD'

EXECUTE TestProc @TranCodes




Post #1428769
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse