SQL Server Stored Procedure to generate different types of reference number

At work, there is a website that we need to generate several types of unique reference numbers. Numbers such as A000013579 for answer id and Q000024680 for question id. To generate numbers like that, we created a stored procedure. I can see that we can use this method in other websites as well.

First, we created a table to save the last used number. Let’s call it [SequenceTable]. In this table, we need 4 columns: [Type],[Prefix],[LastNumber] and [Length].
values for [Type] column could be: “Answer ID”
values for [Prefix] column could be: “A”
values for [LastNumber] column could be: 13579
values for [Length] column could be: 10

Then, we created a Stored Procedure that take value for [Type]. Look for the value of [LastNumber] where [Type] is the input. Fill 0’s using the REPLICATE function. Increase the value of [LastNumber] by 1 and update it in the table. Append prefix and return it as an output. The stored procedure is defined as follow:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_GetNextReferenceID]
  @Type as varchar(25),
  @ID varchar(20) OUTPUT
AS
DECLARE
  @Prefix VARCHAR(10),
  @LastNumber int,
  @Length smallint,
  @charLastNumber as varchar(15)
BEGIN
  if exists (Select * From SEQUENCETABLE Where Type = @Type)
    begin
      Select @prefix = Prefix,
          @LastNumber = LastNumber,
          @Length = Length
      From SEQUENCETABLE Where Type = @Type

      Select @LastNumber = @LastNumber + 1
      Update SEQUENCETABLE Set LastNumber = @LastNumber Where Type = @Type

      -- To Fill 0 in between with the REPLICATE function
      Select @charLastNumber = REPLICATE('0',@length - len(@LastNumber) - len(@prefix))
+ cast(@LastNumber as varchar(10))
      Select @ID = @prefix + @charLastNumber
    End
  Else
    Begin
	SELECT @ID = 'INVALID TYPE'
	RAISERROR('Invalid Type',16,1)
    End
END

use coalesce function to concat field values

Problem: I got 3 tables: [Posts],[Tags] and [PostsTags]
When the tables join, I got multiple records for each post with distinct tags.
I want a table that contains single record for each post with tags concatenated.

Solution:

1. In SQL Server 2005, I created a Scalar-valued Function that takes one parameter (the post id). This function returns concatenated values of the tag name given the post id.
2. Then I called this function in the Views and Stored Procedure.
3. The Scalar-valued Function is defined as below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[SVF_TagNameByPostID] (@post_id int)
RETURNS nvarchar(1000)
AS
BEGIN
	DECLARE @Result nvarchar(1000)

	SELECT @Result = COALESCE(@Result + '|', '') +
	CAST(Table_Tags.tag_name AS nvarchar(50))
	FROM Table_Tags INNER JOIN
	Table_PostsTags ON Table_Tags.tag_id = Table_PostsTags.tag_id INNER JOIN
	View_Posts ON Table_PostsTags.post_id = View_Posts.post_id
	WHERE View_Posts.post_id = @post_id

	RETURN @Result
END