Set 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

Set variable

Author

Topic

OWSQL
Starting Member

27 Posts

Posted - 2011-11-22 : 05:29:48

Hi
I am trying to create a variable that needs to contain a string such as below with the variable @tbook. @tbook can be different tradebooks. the below example works with 1 name (IRM Conference) but when I add that extra one (IR Hedging) I get an incorrect syntax error.
Any ideas on how to do this? (This variable will be appear many times in the final query just FYI)

Declare @tbook nvarchar(60);
Set @tbook = ('IRM Conference','IRM Hedging') ;
Select *
From ******
Where *****
and valuationdetail.tradebook IN (@tbook)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-22 : 05:55:02

You need a split function, search for that in the forums and you will find what you need.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-22 : 06:00:18

Declare @tbook table (tradebook nvarchar(60))
insert @tbook
select 'IRM Conference' union all
select 'IRM Hedging'

Select *
From ******
Where *****
and valuationdetail.tradebook IN (select tradebook from @tbook)

No, you're never too old to Yak'n'Roll if you're too young to die.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-22 : 06:15:21

Please use split function

SELECT * FROM dbo.Split('jasssi,singh,new,coder',',')

ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

OWSQL
Starting Member

27 Posts

Posted - 2011-11-22 : 08:22:08

Thanks, webfred!
That was the most suitable solution for me.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-22 : 08:51:07

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources