469,927 Members | 1,867 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Hello everyone, I am new to SQL server

I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:

CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Thanks in advance,
Syed
Jul 24 '07 #1
4 1099
ck9663
2,878 Expert 2GB
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:

CREATE OR REPLACE FUNCTION fn_comma_env(int4)
RETURNS text AS
$BODY$
DECLARE
rec record;
str text;
comstr text;
BEGIN
str := '';
comstr := '';
FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP
str := str || comstr || rec.classname;
comstr := ',';
END LOOP;
RETURN str;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Thanks in advance,
Syed
could you just say what the function receives, do and returns?
Jul 25 '07 #2
Hello ck9663,

This function recieves an int value e.g 1020 and then gets the varchar value from a table based on 1020. Now tricky thing is it could be more than one record.
If it is one record then function returns a varchar e.g. "TGTI - Environment" or if more than one then varchar e.g. "TGTI - Environment, TWI".
So there has to be a comma in between for more than two values, if not then no comma.

For one record function works as:
Takes int: e.g. 1020
Works: getting a value from table e.g. "TGTI - Environment"
Returns varchar: TGTI - Environment

For more than one record function works as:
Takes: int e.g. 1020
Works: getting multiple values from table e.g. "TGTI - Environment" & "TWI"
Returns: TGTI - Environment, TWI

I hope I have made it clear. Appreciate your help.

Syed
Jul 25 '07 #3
ck9663
2,878 Expert 2GB
Hello ck9663,

This function recieves an int value e.g 1020 and then gets the varchar value from a table based on 1020. Now tricky thing is it could be more than one record.
If it is one record then function returns a varchar e.g. "TGTI - Environment" or if more than one then varchar e.g. "TGTI - Environment, TWI".
So there has to be a comma in between for more than two values, if not then no comma.

For one record function works as:
Takes int: e.g. 1020
Works: getting a value from table e.g. "TGTI - Environment"
Returns varchar: TGTI - Environment

For more than one record function works as:
Takes: int e.g. 1020
Works: getting multiple values from table e.g. "TGTI - Environment" & "TWI"
Returns: TGTI - Environment, TWI

I hope I have made it clear. Appreciate your help.

Syed

you have to run a query first to retrieve all records based on that ID. however, i have yet to see s function that will pivot a table... are you just expecting two records and turn the result into a single character field with comma as separator? or this is a 1-to-N ?
Jul 25 '07 #4
you have to run a query first to retrieve all records based on that ID. however, i have yet to see s function that will pivot a table... are you just expecting two records and turn the result into a single character field with comma as separator? or this is a 1-to-N ?

Yes! you are right. I am expecting two records and turn the result into a single character field with comma as separator.
Aug 1 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Nugs | last post: by
1 post views Thread by flyreally | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.