Obtaining rowcount from UDF 1

[Thargy]

I have a select statement which returns a variable number of rows. I have included it in a user-defined table function, and it works ok.

What I want to do is number each returned record consecutively, in a manner similar to Oracle's rownum function. From various searches I understand that there is no direct equivalent in sql server 2000.

Does anyone have a reference to a multi statement UDF that can do this, or better still, sample code?

Regards

T

 

[gmmastros]

This will probably work....

You said, "user-defined table function".

When you create the function, you probably have something like this...

[tt][blue]Create Function dbo.Whatever(@Param1 int)
Returns @MyReturnTable Table(Col1 Int, Col2 varchar(20))
As
....
[/blue][/tt]

Try changing it to...

Create Function dbo.Whatever(@Param1 int)
Returns @MyReturnTable Table([!]RowId Int Identity(1,1),[/!] Col1 Int, Col2 varchar(20))
As
....

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Thargy]

George,

I was hoping you'd post - thanks.

I've finished work now, so I won't be able to try this until monday, but that looks like just what I was after.

Just so you know why I've posted twice in a strange forum, it's because I've been lumbered with a bad sql server db, and been told to "fix it". In Oracle I'd be fine, but here I am a stranger in a strange land.

I am taking the trouble to google for things, and have searched this site too. Please bear with these obvious newbie posts whilst I scramble up that steep cliff called learning.

I also posted a bit of an overkill answer to a question by ermora. I would value your critique of my style of answer. Since it was demonstrated by code, I felt free to post, even though I'm an Oracle DBA. What do you think george?

Regards

T

 

[gmmastros]

Please bear with these obvious newbie posts

I wouldn't categorize this as a newbie post. Truth is, there are some ramifications of this. Since this is an identity column, you may need to change some of the code in the function to accommodate it. It's entirely possible that you won't need to change anything, but be prepared for it.

I've been lumbered with a bad sql server db

Most of us have been there at one time or another. As an Oracle DBA, you are no doubt biased towards that platform. However, please consider that the performance of a SQL Server database should be very comparable to that of Oracle. At times, Oracle may perform better than SQL Server and vice versa. My point is... as an experienced DBA, you should have a well developed 'gut instinct' for how long something should take. Trust your gut. If you have a poorly performing query in sql, and your gut tells you that it should be faster, then figure out why. Google it. Post it here. Whatever. Don't settle for poor performance just because it's SQL Server and not Oracle.

I also posted a bit of an overkill answer to a question by ermora.

I noticed that. I think the solution you posted was spot on. The only thing I would have done differently was to create the sample data in a table variable instead. You may have noticed, but I am a big fan of sample data in table variables. The way I see it, since it is a table variable, there's no clean up work necessary afterwards.

I particularly liked the way you used a sargable where clause. It would have been easy to write the query in a non-sargable way, which may perform slower (depending on index configuration).

Nice job!

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Thargy]

George,

I have used your suggestion and come up with the following:-

DROP FUNCTION STK_NEG_ANTIGENS_TEMP
GO
DROP FUNCTION STK_NEG_ANTIGENS
GO
CREATE FUNCTION STK_NEG_ANTIGENS_TEMP (@UNIT_ID INT) 
RETURNS @STK_NEG_ANTIGENS_TEMP TABLE  (ROW_NUM INT IDENTITY(0,1),
                             STK_NEGATIVE_ANTIGENS_ID VARCHAR(100),
                             NEGATIVE_ANTIGENS        VARCHAR(100),
                             STOCK_ID                 VARCHAR(100))
AS 
BEGIN 
             INSERT @STK_NEG_ANTIGENS_TEMP
             SELECT  NUMBER+ (CASE 
                  WHEN SUBNUMBER IS NULL THEN P.CODE+'|'
                  ELSE CHAR(SUBNUMBER)+P.CODE+'|'
               END)                              AS STK_NEGATIVE_ANTIGENS_ID,
       A.CODE                                    AS NEGATIVE_ANTIGENS,
       NUMBER+(CASE 
                  WHEN SUBNUMBER IS NULL THEN P.CODE
                  ELSE CHAR(SUBNUMBER)+P.CODE
               END)                             AS STOCK_ID
  FROM  ANTIGENS A,UNITS U
 INNER JOIN PRODUCTS P ON U.PRODUCTCODE = P.CODE
 WHERE CHARINDEX(','+A.CODE+',' COLLATE SQL_LATIN1_GENERAL_CP850_CS_AS ,NEGANTIGENS COLLATE SQL_LATIN1_GENERAL_CP850_CS_AS ) <> 0
   AND A.CODE = REPLACE(REPLACE(A.CODE,' ',','),'.',',')
   AND ID = @UNIT_ID
            RETURN

 
END 
GO

CREATE FUNCTION STK_NEG_ANTIGENS (@UNIT_ID INT) 
RETURNS @STK_NEG_ANTIGENS TABLE (STK_NEGATIVE_ANTIGENS_ID VARCHAR(100),
                                 NEGATIVE_ANTIGENS        VARCHAR(100),
                                 STOCK_ID                 VARCHAR(100)
                                )
AS
BEGIN 
             INSERT @STK_NEG_ANTIGENS
             SELECT STK_NEGATIVE_ANTIGENS_ID+CAST(ROW_NUM AS VARCHAR) AS STK_NEGATIVE_ANTIGENS_ID,
                    NEGATIVE_ANTIGENS,
                    STOCK_ID
               FROM STK_NEG_ANTIGENS_TEMP(@UNIT_ID)
            RETURN

 
END 
GO

You can see that I've gone via a temp UDF to populate the identity columns, using your suggestion (many thanks). However, since the identity can only be populated at runtime, I can't think of how to concatenate it with the STK_NEGATIVE_ANTIGENS_ID directly. I would like to improve the above, and avoid the need for a temp table, and just concatenate the id with the STK_NEGATIVE_ANTIGENS_ID field in one UDF.

I have googled and found the following from

http://www.extremeexperts.com/SQL/Articles/UDFFunctions.aspx

UDFs can also be used as Constraints or Default Values. This is an interesting use and we may not use them often. Recently I had someone ask me how can I populate the value of the identity column in another field without using triggers. Since we cannot create two identity columns we had to work around as below.

CREATE FUNCTION dbo.GetIdentity() RETURNS INT AS
BEGIN
RETURN (IDENT_CURRENT('vin_test'))
END
GO
CREATE TABLE vin_test
(
ID INT IDENTITY(1,1) NOT NULL,
ID2 INT DEFAULT dbo.GetIdentity(),
colA VARCHAR(10)
)
GO
INSERT INTO vin_test (colA) VALUES ('Test')
SELECT * FROM vin_test

which suggests that the generation of an id column and simultaneous concatenation with another field ought to be possible. Since my knowledge is nowhere near this sort of level, can you advise as to whether or not this is technically possible, and if it is, can you suggest mod's to my posted code?



Regards

T

 

[Thargy]

Errata,

I forgot to post typical output data, herewith:-

select * from stk_neg_antigens_temp(1707)

produces

ROW_NUM STK_NEG_ANTIGENS_ID NEG_ANTIGENS STOCK_ID
0	G0525062092178RC|   K	         G0525062092178RC
1	G0525062092178RC|   Cw	         G0525062092178RC
2	G0525062092178RC|   Jk(a)	 G0525062092178RC

And this data is used to produce the final desired output of
select * from stk_neg_antigens(1707)

STK_NEG_ANTIGENS_ID  NEG_ANTIGENS STOCK_ID
G0525062092178RC|0   K	          G0525062092178RC
G0525062092178RC|1   Cw	          G0525062092178RC
G0525062092178RC|2   Jk(a)	  G0525062092178RC

Regards

T

 

[gmmastros]

If this were my project, I would probably...

Create a table variable inside the UDF to store the NEGATIVE_ANTIGENS, STOCK_ID, and a ROW_NUM. Then, after populating this table, use the STOCK_ID and ROW_NUM to create the STK_NEG_ANTIGENS_ID. Like this...

DROP FUNCTION STK_NEG_ANTIGENS_TEMP
GO
DROP FUNCTION STK_NEG_ANTIGENS
GO
CREATE FUNCTION STK_NEG_ANTIGENS_TEMP (@UNIT_ID INT) 
RETURNS @STK_NEG_ANTIGENS_TEMP TABLE  (
                             STK_NEGATIVE_ANTIGENS_ID VARCHAR(100),
                             NEGATIVE_ANTIGENS        VARCHAR(100),
                             STOCK_ID                 VARCHAR(100))
AS 
BEGIN 

	Declare @Temp 
    Table   (ROW_NUM Int Identity(0,1),
             STK_NEGATIVE_ANTIGENS_ID VARCHAR(100),
             NEGATIVE_ANTIGENS        VARCHAR(100),
             STOCK_ID                 VARCHAR(100))

    Insert 
    Into    @Temp(NEGATIVE_ANTIGENS, STOCK_ID)
    SELECT  A.CODE AS NEGATIVE_ANTIGENS,
            NUMBER+(CASE 
                      WHEN SUBNUMBER IS NULL THEN P.CODE
                      ELSE CHAR(SUBNUMBER)+P.CODE
                      END) AS STOCK_ID
    FROM    ANTIGENS A,UNITS U
            INNER JOIN PRODUCTS P 
              ON U.PRODUCTCODE = P.CODE
    WHERE   CHARINDEX(','+A.CODE+',' COLLATE SQL_LATIN1_GENERAL_CP850_CS_AS ,NEGANTIGENS COLLATE SQL_LATIN1_GENERAL_CP850_CS_AS ) <> 0
            AND A.CODE = REPLACE(REPLACE(A.CODE,' ',','),'.',',')
            AND ID = @UNIT_ID
    
    Insert 
    Into   @STK_NEG_ANTIGENS_TEMP(
           STK_NEGATIVE_ANTIGENS_ID,
           NEGATIVE_ANTIGENS,
           STOCK_ID)
    Select STOCK_ID + '|' + Convert(VarChar(20), Stock_ID),
           NEGATIVE_ANTIGENS,
           STOCK_ID
    From   @Temp

    RETURN

 
END 
GO

Since the STK_NEGATIVE_ANTIGENS_ID is really the STOCK_ID with the ROW_NUMBER concatenated to the end, the internal table variable doesn't need to store STK_NEGATIVE_ANTIGENS_ID.

There is, of course, another way to use the identity value. With SQL Server, you can create computed columns in real tables, temp tables, and table variables. The only drawback here (for this query) is that there still needs to be the identity column. However, eventhough there is an identity column, that doesn't mean you need to use it. As such, I encourage you to try this....

DROP FUNCTION STK_NEG_ANTIGENS_TEMP
GO
DROP FUNCTION STK_NEG_ANTIGENS
GO
CREATE FUNCTION STK_NEG_ANTIGENS_TEMP (@UNIT_ID INT) 
RETURNS @STK_NEG_ANTIGENS_TEMP TABLE  (ROW_NUM Int Identity(0,1),
   [!]STK_NEGATIVE_ANTIGENS_ID As STOCK_ID + '|' + Convert(VarChar(20), ROW_NUM)[/!],
   NEGATIVE_ANTIGENS        VARCHAR(100),
   STOCK_ID                 VARCHAR(100))
AS 
BEGIN 

    Insert 
    Into    @STK_NEG_ANTIGENS_TEMP(NEGATIVE_ANTIGENS, STOCK_ID)
    SELECT  A.CODE AS NEGATIVE_ANTIGENS,
            NUMBER+(CASE 
                      WHEN SUBNUMBER IS NULL THEN P.CODE
                      ELSE CHAR(SUBNUMBER)+P.CODE
                      END) AS STOCK_ID
    FROM    ANTIGENS A,UNITS U
            INNER JOIN PRODUCTS P 
              ON U.PRODUCTCODE = P.CODE
    WHERE   CHARINDEX(','+A.CODE+',' COLLATE SQL_LATIN1_GENERAL_CP850_CS_AS ,NEGANTIGENS COLLATE SQL_LATIN1_GENERAL_CP850_CS_AS ) <> 0
            AND A.CODE = REPLACE(REPLACE(A.CODE,' ',','),'.',',')
            AND ID = @UNIT_ID

    RETURN
 
END 
GO

Notice that we are not inserting in to the computed column (you can't), but when you return the value from it, it will match whatever the computed column specs say. So....

[tt][blue]
select STK_NEGATIVE_ANTIGENS_ID,
NEGATIVE_ANTIGENS,
STOCK_ID
from stk_neg_antigens_temp(1707)
[/blue][/tt]

If you continue to use *, you will see the row_num column. If you specify the column names, then row_num is still returned, but you are not doing anything with it, so no harm done.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Thargy]

George,

thank you for the excellent response.

I haven't been able to use it up until now, as I've been put on to higher priority tasks which I've just completed.

I was unaware of computed columns in SQL Server (they're a recent addition to Oracle) and I like the elegant concatenation your code provides.

I am using your suggested code, and 'ere long it will be in production. I am using it as a model for several other similar tasks, so it will be used and re-used.

If I could award two stars I would. Please consider this task as fully and elegantly answered, and therefore closed.

Regards

T

 

[gmmastros]

I'm glad that I was able to help!

To be honest, I knew about computed columns for several years now, but this is the first time I thought to put one in a table variable. I was a little surprised that it worked.

There is one other thing I wanted to mention. In your code, there is a part like this...

[blue]CAST(ROW_NUM AS VARCHAR)[/blue]

I caution you against this (as a general programming principle). You see, when you indicate varchar, without specifying a size, you can sometimes get unpredictable results.

For example....

Declare @Test VarChar
Set @Test = 'Hello world'
Select @Test

Before you run it, what would you expect the output to be? Now run it. I think you will be unpleasantly surprised.

Now consider this...

Declare @TestInt Int
Set @TestInt = 123456
Select Cast(@TestInt as varchar)

Before you run this, what do you expect? If you run the first one, then your guess may be wrong here. But if you don't run the first one.... Anyway... the results are a bit unpredictable.

That's why I have a personal rule:

Never declare a varchar without specifying the size.

While it's possible to consistently get the correct results, why chance it. You know?

Instead, you should use something like...

[blue]CAST(ROW_NUM AS VARCHAR[!](20)[/!])[/blue]

This removes any ambiguity that there might be.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Thargy]

George,

I have run both your samples and the inconsistency between the two is enlightening. In all likelihood, I would only have found this after debugging apparently good code for far too many hours.

Fortunately, I copied your convert statement with the VARCHAR(20), so that's an explicit size, and I surmise will be fine. Thanks for the tip (tek or otherwise )

Regards

T