Using multiple variables in a Query / Stored Procedure

Question

Post reply

Using multiple variables in a Query / Stored Procedure

meadow0

Old Hand

Points: 389
More actions
March 8, 2013 at 10:15 am

#290604

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

Viewing 8 posts - 1 through 7 (of 7 total)

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

Richard Warr SSCertifiable Points: 6963 More actions · Answer 1

Can you just look for TransCode being a substring of @TC?

meadow0 Old Hand Points: 389 More actions · Answer 2

I'm not sure -- just started using SQL about a week ago. I will research and see if that will work.

meadow0 Old Hand Points: 389 More actions · Answer 3

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?

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

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 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/

Chrissy321 SSCoach Points: 15750 More actions · Answer 5

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.

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

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 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/

Chrissy321 SSCoach Points: 15750 More actions · Answer 7

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