SQL 2000 Question:What can I do if I need to 10 column values and do logical processing on these columns to update a final column. 1-Since I cannot pass a Table to a UDF, I thought about using a UDF that is passed all these 10 values and return the processed value and that is a slow brutal way. UDF? 2 I am learning about CURSORS and the author says do not use unless I have to ? 3- can use UPDATE TABLE with a SET Values "section" that uses WHILE, IF statements without using a UDF ? Any help will be greatly appreciated , Thanks Excample below: PTYPE_STR below will be my column -result CREATE TABLE TEST_C ( S_TYPE varchar(2), FTYPE_VAL SMALLINT , PTYPE_C1 SMALLINT, PTYPEC0 SMALLINT, PTYPEC1 SMALLINT, PTYPEC2 SMALLINT, PTYPEC3 SMALLINT, PTYPEC4 SMALLINT, PTYPEC5 SMALLINT, PTYPEC6 SMALLINT, PTYPE_STR VARCHAR (100), ) INSERT INTO TEST_C VALUES ('F', 10, NULL, 0,1,2,3,4,5,6, NULL ) INSERT INTO TEST_C VALUES ('F', 33, NULL, 0,1,2,3,4,5,6, NULL ) INSERT INTO TEST_C VALUES ('P', NULL, 0, 0,1,2,3,4,5,6 , NULL ) INSERT INTO TEST_C VALUES ('P', NULL, 1, 0,1,2,3,4,5,6, NULL )
[quoted text, click to view] On Apr 27, 2:24 pm, placidi...@yahoo.com wrote:
> SQL 2000 Question:What can I do if I need to 10 column values and do
> logical processing on these columns to update a final column.
> 1-Since I cannot pass a Table to a UDF, I thought about using a UDF
> that is passed all these 10 values and return the processed value and
> that is a slow brutal way.
> UDF?
> 2 I am learning about CURSORS and the author says do not use unless I
> have to ?
>
> 3- can use UPDATE TABLE with a SET Values "section" that uses WHILE,
> IF statements without using a UDF ?
>
> Any help will be greatly appreciated , Thanks
>
> Excample below:
>
> PTYPE_STR below will be my column -result
>
> CREATE TABLE TEST_C (
> S_TYPE varchar(2),
> FTYPE_VAL SMALLINT ,
> PTYPE_C1 SMALLINT,
> PTYPEC0 SMALLINT,
> PTYPEC1 SMALLINT,
> PTYPEC2 SMALLINT,
> PTYPEC3 SMALLINT,
> PTYPEC4 SMALLINT,
> PTYPEC5 SMALLINT,
> PTYPEC6 SMALLINT,
> PTYPE_STR VARCHAR (100),
> )
>
> INSERT INTO TEST_C VALUES ('F', 10, NULL, 0,1,2,3,4,5,6, NULL )
> INSERT INTO TEST_C VALUES ('F', 33, NULL, 0,1,2,3,4,5,6, NULL )
> INSERT INTO TEST_C VALUES ('P', NULL, 0, 0,1,2,3,4,5,6 , NULL )
> INSERT INTO TEST_C VALUES ('P', NULL, 1, 0,1,2,3,4,5,6, NULL )
Not clear what you want. You stopped your example at Insert . What Next? You can use UPDATE with CASE
(placidite1@yahoo.com) writes: [quoted text, click to view] > SQL 2000 Question:What can I do if I need to 10 column values and do
> logical processing on these columns to update a final column.
> 1-Since I cannot pass a Table to a UDF, I thought about using a UDF
> that is passed all these 10 values and return the processed value and
> that is a slow brutal way.
> UDF?
It would help to know what the function will actually perform. One possibility is to pass only the key value to the UDF, and then have the UDF to read the rest of the columns. However, scalar UDFs with data access is usually a recpie for slowdowns, so avoid that. [quoted text, click to view] > 2 I am learning about CURSORS and the author says do not use unless I
> have to ?
Correct. But what has that to do with your question? :-) [quoted text, click to view] > 3- can use UPDATE TABLE with a SET Values "section" that uses WHILE,
> IF statements without using a UDF ?
No. [quoted text, click to view] > Excample below:
>
> PTYPE_STR below will be my column -result
>
> CREATE TABLE TEST_C (
> S_TYPE varchar(2),
> FTYPE_VAL SMALLINT ,
> PTYPE_C1 SMALLINT,
> PTYPEC0 SMALLINT,
> PTYPEC1 SMALLINT,
> PTYPEC2 SMALLINT,
> PTYPEC3 SMALLINT,
> PTYPEC4 SMALLINT,
> PTYPEC5 SMALLINT,
> PTYPEC6 SMALLINT,
> PTYPE_STR VARCHAR (100),
> )
You could make PTYPE_STR a computed column. Here is a simple example: PTYPE_STR AS ltrim(str(PTYPEC3)) For this to be possible, the forumla has to be a simple expression. You could use a UDF here, but since the value is computed on invocation, this may have an impact on performance. Another option is to have a trigger to set the value. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Hi!
If your datavolume is small and if you had 2005 I would say:
You can convert an resultset to an xml using "for xml" and then
you can pass an xml to an udf.
Alternative: use stored proc's with temporary tables, but the table
must be created before you call the proc (it's not beauty, but MS SQL
doesn't support collections like Oracle).
Greetings
Bjorn D. Jensen
Don't see what you're looking for? Try a search.
|
