output to be redirected to a variable

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

SQL Server 2005 Forums

Transact-SQL (2005)

output to be redirected to a variable

Author

Topic

lazydev
Starting Member

16 Posts

Posted - 2008-03-26 : 15:03:37

here's my code i want my output to be stored in a variable or out statement .Any suggestions on the below procedure

alter proc r (@id INT)
as
BEGIN

DECLARE @input VARCHAR(800)
DECLARE @c_input INT
DECLARE @i_Input INT
DECLARE @input_left VARCHAR(800)
DECLARE @delimiter CHAR(1)
select @delimiter = ','
DECLARE @in VARCHAR(800)
DECLARE @list VARCHAR(800)
declare @list2 VARCHAR(800)

SET @input = 'AWCA,GCS,IHP,Aetna'
select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))
set @c_input = @c_input + 1
while @c_input > 0
BEGIN
select @i_input = charindex(@Delimiter,@input)
if @i_input != 0
BEGIN
select @input_left = left(@input, @i_input - 1)
END
else
select @input_left = @input

select @in = '''' + @input_left + ''''
select @list = ISNULL(@list + ',', '') + @in

select @input = right(@input ,(len(@input) - @i_input))
SET @c_input = @c_input -1
if @c_input = 0 or @input = @input_left
break
end

PRINT @list

END

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 15:18:56

alter proc r (@id INT, @list VARCHAR(800) out)
and remove the declaration of @list

then call via
declare @list VARCHAR(800)
exec r @id = 1, @list = @list out

What are you trying to do - I have a suspicion this could be a single statement.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lazydev
Starting Member

16 Posts

Posted - 2008-03-26 : 15:29:10

My input is a list of values seperated by commas
now my output should be list of values not in the table joined by comma

eg : if my table consists of list of all databases like
db2
oracle,
sybase,
mssql,
mysql,

myinput would be like this db2,sybase,oracle
my output should be mssql,mysql

how to get that

Any easier methods

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 16:04:44

see http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

with csvtbl(i,j)
as
(
select i=1, j=charindex(',',@list+',')
union all
select i=j+1, j=charindex(',',@list+',',j+1) from csvtbl where charindex(',',@list+',',j+1) <> 0
)
select substring(@list,i,j-i)
from csvtbl
where substring(@s,i,j-i) not in (select db from tbl)

That'll give a resultset - if you want another csv list then chage the select to
select @r = coalesce(@r + ',','') + substring(@list,i,j-i)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-26 : 16:06:03

You could also use this function to return the resultset to compare
http://www.nigelrivett.net/SQLTsql/ParseCSVString.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lazydev
Starting Member

16 Posts

Posted - 2008-03-27 : 04:36:53

As per your code it gives me invalid column name i,j

alter proc r (@id INT)
as
BEGIN

DECLARE @input VARCHAR(800)
DECLARE @c_input INT
DECLARE @i_Input INT
DECLARE @input_left VARCHAR(800)
DECLARE @delimiter CHAR(1)
select @delimiter = ','
DECLARE @in VARCHAR(800)
DECLARE @list VARCHAR(800)
declare @list2 VARCHAR(800)
declare @s VARCHAR(300)
declare @r VARCHAR(400)

SET @input = 'AWCA,GCS,IHP,Aetna'
select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))
set @c_input = @c_input + 1
while @c_input > 0
BEGIN
select @i_input = charindex(@Delimiter,@input)
if @i_input != 0
BEGIN
select @input_left = left(@input, @i_input - 1)
END
else
select @input_left = @input

select @in = '''' + @input_left + ''''
select @list = ISNULL(@list + ',', '') + @in

select @input = right(@input ,(len(@input) - @i_input))
SET @c_input = @c_input -1
if @c_input = 0 or @input = @input_left
break
end

PRINT @list;
----------This is your code ---------------------
with csvtbl(i,j)
as
(
select i=1, j=charindex(',',@list+',')
union all
select i=j+1, j=charindex(',',@list+',',j+1) from csvtbl where charindex(',',@list+',',j+1) <> 0
)
select substring(@list,i,j-i)
from csvtbl
where substring(@s,i,j-i) not in (select Label from RepricingSystemType)
select @r = coalesce(@r + ',','') + substring(@list,i,j-i)
PRINT @r
END

nr
SQLTeam MVY

12543 Posts

Posted - 2008-03-27 : 05:51:25

the population of @r is part of the statement not a new one.
Try
with csvtbl(i,j)
as
(
select i=1, j=charindex(',',@list+',')
union all
select i=j+1, j=charindex(',',@list+',',j+1) from csvtbl where charindex(',',@list+',',j+1) <> 0
)
select @r = coalesce(@r + ',','') + substring(@list,i,j-i)
from csvtbl
where substring(@s,i,j-i) not in (select Label from RepricingSystemType)
PRINT @r

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 15:22:38

May be this

SELECT LEFT(dl.dblist,LEN(dl.dblist)-1)
FROM
(SELECT dbname + ',' as [text()]
FROM YourTable
WHERE @List NOT LIKE '%' + dbname + '%'
FOR XML PATH(''))dl(dblist)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources