A String as variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A String as variable

Author

Topic

OWSQL
Starting Member

27 Posts

Posted - 2011-09-02 : 08:42:03

Hi
I would like to define a string as a variable.
The text string includes 4 values that should be a condition in the where clause.
I have defined a variable TB. How can I make the syntax so the below condition is fulfilled?
Thanks

Declare @TB text;
set @TB = ('IRM Fuel Prop 1','IRM Fuel Prop 2','IRM Hedging','IRM Proprietary');

select statement....
From...
where...
AND valuationdetail.tradebook IN (@TB)

Devart
Posting Yak Master

102 Posts

Posted - 2011-09-02 : 08:59:55

Hello,

For example:

Declare @TB varchar(8000);
set @TB = 'IRM Fuel Prop 1,IRM Fuel Prop 2,IRM Hedging,IRM Proprietary,';

WITH valuationdetail AS
(
SELECT 'IRM Fuel Prop 1' as tradebook
UNION ALL
SELECT 'IRM Fuel Prop 2'
UNION ALL
SELECT 'IRM Hedging'
UNION ALL
SELECT 'IRM Proprietary'
UNION ALL
SELECT 'IRM Proprietary kjslgf'
)
select *
From valuationdetail
where @TB<>replace(@TB,valuationdetail.tradebook,'')

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 09:32:36

1) Dynamic SQL - generally a bad idea
2) Using a SPLIT function - which would give you syntax something like:


select statement....
From...
    JOIN dbo.MySplitFunction(@TB)
        ON SplitValue = valuationdetail.tradebook
where...

3) Put the values into a temporary table and JOIN that table.

if @TB is a "list" passed to an Sproc then the Split Function is probably the easiest (you CAN create a temporary table dirct from the Client, but I think its more aggro than it is worth).

If this all happens within am Sproc then make a temporary table if you need the data multiple times within the SProc, or for convenience, or make the data available using some sort of pseudo list (like Devart has done - I would use CROSS APPLY with a VALUES list)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-05 : 05:38:55

What is the SQL Server version you are using? In version 2008, you can use Table-Valued Parameter(TVP)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources