SQL Server Performance Forum – Threads Archive

oracle sp to sqlserver sp

hi,
i have one oracle sp, i need to convert into sqlserver sp. any bdy help..thanks advance the SP look like this.
CREATE PROCEDURE Concat_text
(
@text_agn numeric(13),
@lang_cd code = null
)
as
SET NOCOUNT ON BEGIN
declare @concat_txt_id varchar(50)
cursor cnt_id is select text as
text_cd from GNRL_TXT where [email protected]_agn and [email protected]_cd order by seq_nbr;
cnt_id1 cnt_id%rowtype; Begin open cnt_id;
Loop
Fetch cnt_id into cnt_id1;
exit when cnt_id1%notfound;
concat_txt_id:=concat_txt_id||to_char(cnt_id1.text)
end loop;
insert into temp values(@text_agn,@lang_cd,concat_txt_id);
close cnt_id;
End;
END Concat_text_cd;
SURYA

---

If you’re concatenating a string from the same column from multiple rows, here’s the basic syntax in SQL 2000: DECLARE @String VARCHAR(8000) SET @String = ” SELECT @String = @String + CASE WHEN LEN(@String) = 0 THEN ” ELSE ‘, ‘ END + T.Column
FROM Table T
WHERE …………………………. The usual way to implement this is to create a UDF, which you can call from any query, using a number of parameters to filter the rows from which you need to concatenate. In SQL 7.0, you’ll have to use the same cursor-based approach as in Oracle. Not sure what the additional stuff in the Oracle script does, but this is the basic stuff to get you started.

---

Also see if this helps
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true Madhivanan Failing to plan is Planning to fail

---

Hi ya, one construct that the proc is using is the %rowtype which is essentially an array type, and is not something that SQL Server has. In SQL Server you’d need to declare each column name and concat them together or within the sql select concat each column into a single variable (accepting that it may overflow the 8000 byte limit of a varchar Cheers
Twan

---

thank you all, i wrote something like this. CREATE PROCEDURE Concat_text
(
@text_agn numeric(13) ,
@lang_cd numeric(10)
)
AS BEGIN
SET NOCOUNT ON declare @concat_txt_id varchar(50)
declare @cnt_id1 varchar(10) create table #temp(text_agn numeric(13),lang_cd numeric(10),concat_txt_id varchar(50)) declare cnt_id CURSOR FOR
select text_cd from GNRL_TXT
where text_agn = @text_agn
and lang_cd = @lang_cd
order by seq_nbr open cnt_id fetch next from cnt_id into @cnt_id1 while @@FETCH_STATUS = 0
begin
select @concat_txt_id = @concat_txt_id + ltrim(rtrim(@cnt_id1)) fetch next from cnt_id into @cnt_id1 end insert into #temp values(@text_agn,@lang_cd,@concat_txt_id) CLOSE cnt_id
DEALLOCATE cnt_id END
SURYA

---

Looks like you’ll always be inserting NULL, as you’re not initializing the @concat_txt_id variable before the loop – SET @concat_txt_id = ” Anyway, you really can do this in a single SELECT statement – just use the statement that you’re using for the cursor. Or even rewrite this as a UDF, with the same parameters as your SP. Finally, your SP doesn’t seem to return any data to the outside world – the temp table is local to the SP, and you don’t have an OUTPUT parameter.

---

Otherwise post some sample data and the result you want Madhivanan Failing to plan is Planning to fail

---

Probably these will help you, too:
http://www.microsoft.com/downloads/…88-C919-463F-B020-81468CD231DA&displaylang=en
http://www.microsoft.com/downloads/…5E-2CFC-49AE-A184-5705ACF8591F&displaylang=en
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part2/c0761.mspx —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

thank you all once again…
SURYA

---

]]>