How can i assign this result to a string 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)

How can i assign this result to a string variable

Author

Topic

sunilmahanti
Starting Member

4 Posts

Posted - 2011-02-17 : 05:53:02

Hi,

Dis is sunil & nu b to .net here i create a stored procedure but i can'nt the result to a string variable. plz can any body help me.

thanks

Sunil Kumar.

The Stored Procedure is like this:

alter procedure [dbo].[tst2](@num int)
as
begin
declare @n int
declare @res varchar(50)
select @n = @num;
SELECT CASE (@n / 100000) % 14
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
WHEN 4 THEN 'four'
WHEN 5 THEN 'five'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
when 10 then 'Ten'
when 11 then 'Eleven'
when 12 then 'Twelve'
when 13 then 'Thirtenn'
ELSE ''
END
+CASE WHEN (@n / 100000) % 10 > 0 THEN ' Lakh ' ELSE '' END
+CASE WHEN (@n / 1000) % 100 > 0 AND (@n / 1000) > 100 THEN ' and ' ELSE '' END
+CASE (@n / 1000) % 100
WHEN 10 THEN 'ten'
WHEN 11 THEN 'eleven'
WHEN 12 THEN 'twelve'
WHEN 13 THEN 'thirteen'
WHEN 14 THEN 'fourteen'
WHEN 15 THEN 'fifteen'
WHEN 16 THEN 'sixteen'
WHEN 17 THEN 'seventeen'
WHEN 18 THEN 'eighteen'
WHEN 19 THEN 'nineteen'
ELSE
CASE (@n / 10000) % 10
WHEN 2 THEN 'twen'
WHEN 3 THEN 'thir'
WHEN 4 THEN 'for'
WHEN 5 THEN 'fif'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
ELSE ''
END
+CASE WHEN (@n / 10000) % 10 > 0 THEN 'ty' ELSE '' END
+CASE WHEN (@n / 1000) % 10 > 0 AND (@n / 10000) % 10 > 0 THEN ' ' ELSE '' END
+CASE (@n / 1000) % 10
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
WHEN 4 THEN 'four'
WHEN 5 THEN 'five'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
ELSE ''
END
END
+CASE WHEN (@n / 1000) % 1000 > 0 THEN ' Thousand' ELSE '' END
+CASE WHEN (@n / 100) % 10 > 0 THEN ' ' ELSE '' END
+CASE (@n / 100) % 10
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
WHEN 4 THEN 'four'
WHEN 5 THEN 'five'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
ELSE ''
END
+CASE WHEN (@n / 100) % 10 > 0 THEN ' Hundred' ELSE '' END
+CASE WHEN @n % 100 > 0 AND @n > 100 THEN ' and ' ELSE '' END
+CASE @n % 100
WHEN 10 THEN 'ten'
WHEN 11 THEN 'eleven'
WHEN 12 THEN 'twelve'
WHEN 13 THEN 'thirteen'
WHEN 14 THEN 'fourteen'
WHEN 15 THEN 'fifteen'
WHEN 16 THEN 'sixteen'
WHEN 17 THEN 'seventeen'
WHEN 18 THEN 'eighteen'
WHEN 19 THEN 'nineteen'
ELSE
CASE (@n / 10) % 10
WHEN 2 THEN 'twen'
WHEN 3 THEN 'thir'
WHEN 4 THEN 'for'
WHEN 5 THEN 'fif'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
END
+CASE WHEN (@n / 10) % 10 > 0 THEN 'ty' ELSE '' END
+CASE WHEN @n % 10 > 0 AND (@n / 10) % 10 > 0 THEN ' ' ELSE '' END
+CASE @n % 10

WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
WHEN 4 THEN 'four'
WHEN 5 THEN 'five'
WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
ELSE ''
END
end Rupees
--print @num

end

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-17 : 06:46:52

try this.... Using output parameters

create procedure [dbo].[tst2](@num int,@string varchar(max) output)
as
begin
declare @n int
declare @res varchar(50)
select @n = @num;
SELECT @string=CASE (@n / 100000) % 14
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
.........
........
..........
.......

WHEN 6 THEN 'six'
WHEN 7 THEN 'seven'
WHEN 8 THEN 'eight'
WHEN 9 THEN 'nine'
ELSE ''
END
END
END

go
--- excuting sp
DECLARE @result varchar(max)
EXEC [dbo].[tst2] 12345, @string=@result OUTPUT
SELECT @result

--Ranjit

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-17 : 07:18:34

are you looking for something like this ???

Alter procedure [dbo].[tst2](@num int)
as
begin
declare @n int
declare @res varchar(50)
select @n = @num;

DECLARE @string varchar(8000)
set @string=<The select statement>

End

Make sure that your select statement returns only One Value. Otherwise you will get an error

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources