Store values in variable

Question

Post reply

Store values in variable

mark_close11

SSC Rookie

Points: 42
More actions
November 9, 2010 at 7:30 pm

#241069

I have a list of values that I want to store in a variable in SQL server 2005.
DECLARE @State varchar(30)
SET @State = 'VIC', 'QLD'
I get an error.
Incorrect syntax near ','.
Then i want to run a select query
SELECT * FROM address WHERE state IN @State
I do not want to hard code the values as they are used multiple times in my stored proc.
How can I do this?
Thanks
Mark

Viewing 5 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 739010 More actions · Answer 1

The only way that you can store multiple values in a variable like this is to store them in a string

DECLARE @State varchar(30)

SET @State = '''VIC''', '''QLD'''

Where do the values come from? You can also potentially store these in a table variable or temp table and do a join with your main table.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Use a temp table and store your variables in rows and join against it instead of trying to use the IN statement that way.

IE:

DECLARE @filter TABLE (FilterVal VARCHAR(100))

INSERT INTO @filter VALUES ('AZ')

INSERT INTO @filter VALUES ('ND')

SELECT

*

FROM

StateTable AS st

JOIN @filter AS f

ON st.state = f.filter

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

mark_close11 SSC Rookie Points: 42 More actions · Answer 3

This data is only for testing.

The @State parameter will be passed into the stored proc from Reporting Services.

WayneS SSC Guru Points: 95461 More actions · Answer 4

In that case, you will need to use a function that splits the delimited string into the individual items, and returns them as a table.

Here is the latest version of the Delimited Split Function

You would use it like:

SELECT *

FROM address a

JOIN dbo.DelimitedSplit8K(@State, ',') ds

ON ds.Item = a.State;

You mention that they are used multiple times. You might want to do this instead:

SELECT [State] = Item

INTO #States

FROM dbo.DelimitedSplit8K(@State, ',');

And then join to this temporary table everywhere

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2