Update a table, get the value updated, insert the value in another table and display the value

[infinitizon]

Hi all,

I was working with an interesting subject that took me two days to complete. However, I'm not certain of the cost of the query so I thought to post it as a question. Here it is.

First, I have a function that gets the alphabeths in a string

CREATE FUNCTION [dbo].[GetAlphabeths](@DATA VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN   
    RETURN Left(
	SubString(@DATA, PatIndex('%[a-zA-Z><!=]%', @DATA), 8000),PatIndex('%[^a-zA-Z><!=]%', SubString(@DATA, PatIndex('%[a-zA-Z><!=]%', @DATA), 8000) + '1')-1)
END

Then another one that gets the numbers in the same string

ALTER FUNCTION [dbo].[GetNumbers](@DATA VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN   
    RETURN LEFT(
       SUBSTRING(@DATA, PATINDEX('%[0-9.-]%', @DATA), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(@DATA, PATINDEX('%[0-9.-]%', @DATA), 8000) + 'X')-1)
END

I used the two functions in another function that gets a string from a table, splits it, increments the number part (by a default incrementer specified), and returns the string (now incremented)

ALTER FUNCTION [dbo].[getLstOvtUsed]()
RETURNS VARCHAR(10)
AS
BEGIN
	DECLARE @lastNoUsed int, @prepender varchar(3), @incNo int, @returnVal varchar(10)
	SELECT @lastNoUsed = dbo.GetNumbers([Last No. Used]), @prepender=dbo.GetAlphabeths([Last No. Used])
			, @incNo = [Increment-by No]
		FROM [No_ Series Line]
	SET @returnVal = @lastNoUsed+@incNo
	RETURN @prepender + RIGHT('0000000' + @returnVal, 7)
END

Now, I create a procedure thus

CREATE PROCEDURE sp_insertOvtAppl
	@staff_id VARCHAR(10), @dept_id VARCHAR(10), @designation VARCHAR(10)
	, @1st_sup VARCHAR(50), @2nd_sup VARCHAR(50)
	, @apply_date DATETIME, @start_time DATETIME, @end_time DATETIME
AS
BEGIN
	DECLARE @over_id VARCHAR(10)
	UPDATE [No_ Series Line] SET [Last No. Used]=dbo.getLstOvtUsed()
	SELECT @over_id = [Last No. Used] FROM [No_ Series Line]
	INSERT INTO OvertimeApplication
			([over_id], [staff_id],[dept_id],[designation],[1st_sup],[2nd_sup],[apply_date],[start_time],[end_time])
		VALUES
			(@over_id, @staff_id, @dept_id, @designation, @1st_sup, @2nd_sup, @apply_date, @start_time, @end_time)
	SELECT [Last No. Used] FROM [No_ Series Line]
END

Everything works fine. However, When I look at the procedure and the number of side-effecting queries in it, I begin to wonder if this is the most efficient way to go about this?

Any Ideas please?

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[markros]

One unrelated comment - don't name your SP with sp_...
sp_ is used by MS system stored procedures, so you need to use something different for a prefix.

PluralSight Learning Library

 

[infinitizon]

@markros: Thanks for the notice. However, you did not answer the question.

 

[markros]

Try making both of your functions to be inline table-valued functions - they perform better.

PluralSight Learning Library

 

[gmmastros]

If I understand correctly, you want to get a unique value that can be used elsewhere. Your values are a mixture of letters and numbers, and you want to increment the numbers each time you insert a row.

Ex:

[tt]TableX - ColId
ABC000123
ABC000124
ABC000125
ABC000126[/tt]

With the sample above, you would want to return ABC000127. Is this correct?

If this is the case, I have to ask... what is the significance of the letters before the numbers? Will the letters ever change? What would cause them to change?

I often times see situations similar to what I have described. In these situations, I usually recommend that you have an identity column (integer) in the table. Then use a computed column to generate the "DisplayId". Like this...

First create a table to test this with.

Create Table SampleTest(Id Int Identity(1,1), Name VarChar(20))

Now add a computed column for the DisplayId.

Alter Table SampleTest Add DisplayID As 'ABC' + Right('000000' + Convert(VarChar(10), id), 6)

Now insert some sample data.

Insert Into SampleTest(Name) Values('Alex')
Insert Into SampleTest(Name) Values('Brad')
Insert Into SampleTest(Name) Values('Charles')
Insert Into SampleTest(Name) Values('Daniel')

Now look at the data in the table.

Select * From SampleTest

As you can see, there is an integer column that increments by 1, but there is also a "DisplayId" column that is formatted with a couple letters, several 0's and the id number. This is probably similar to the id number you are trying to manufacture with several functions and lots of code.

To clean up after ourselves....

Drop Table SampleTest

Personally, I prefer to use integers for my unique identifiers. If humans are more comfortable looking at a "code" that includes some sort of alpha identifier, then go ahead and create it. However, I still prefer to use the actual integer id behind the scenes and foreign key values in other tables. Using Integers for your id's will also perform better.

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[infinitizon]

@markros: Thanks again, I'd look into those. However I'd like to know about the side-effecting queries in the procedure. Suppose there is a concurrent connection to the database (2 or three people executing the procedure at about the same time.) The procedure needs to first update the number series table, select the updated value, insert the updated value into another table then, display the value to the user.
This is some task and I don't if there won't be performance hitches.

 

[markros]

In SQL Server 2012 the best solution for your problem will be using Sequence tables. Prior to that I can refer you to a very interesting discussion that shows how to implement this in a good way:

http://forum.lessthandot.com/viewtopic.php?f=17&t=6518

PluralSight Learning Library

 

[RTag]

In SQLServer 2005 and above you can use OUTPUT clause which will provide you access to the updated/deleted/inserted rows

http://msdn.microsoft.com/en-US/library/ms177564(v=SQL.90).aspx

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[infinitizon]

@RTag: Well your output idea seems good. However, can output display anything apart from integers? All examples I have seen so far outputs integers and when I tried in my example, it did thesame (throwing an error when I put a string for output).
@markros: I dont seem to be making a headway in the link you provided. The information doesn't seem to flow in my direction...Thanks all thesame.
@gmmastros: Well, thanks. However, that was not exactly what I was asking. I know how to do that and if you follow my post carefully, you'd see that I used that in the function dbo.getLstOvtUsed().

What I need to know is if my procedure (sp_insertOvtAppl) is not going to be a mistake in the future because of the number of queries involved. UPDATE a table, SELECT from that table, INSERT into another table, then SELECT again all in one procedure (Even though I've tweaked the second table to 'SELECT @over_id')

 

[markros]

What is your SQL Server version? In SQL Server 2008 and up you can do all of what you're doing using just one command, in SQL 2005 you need to use OUTPUT clause of UPDATE command (I assume your table has one row only) to return the values after update.

So, for SQL 2008 check this blog post and look into composable DML (that's the term to search for).
[URL unfurl="true"]http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/using-t-sql-output-and-merge[/url]

Some simple explanations for what OUTPUT clause is about you can find also in this blog post
OUTPUT clause for DML operations

PluralSight Learning Library