Stored Procedure Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Stored Procedure Help

Author

Topic

crazyco
Starting Member

30 Posts

Posted - 2012-01-11 : 06:18:09

I'm needing help with a stored procedure where I insert data based on a select statement and the number returned in that select statement. Two codes and the number will be joined together and inserted along with the other data. I've done most of it, just unsure of the next stage, i.e. if statement?

ALTER PROCEDURE [dbo].[sp_code]

(@hb_code varchar(16), @dhsw_code varchar(16))

AS
BEGIN

set @hb_code = 'TT1'
set @dhsw_code = 'HSW'
set @firstname = 'John'
set @surname = 'Smith'
set @address1 = '123 South Street'
set @telephone1 = '012345'
--set @dhsw_code --will consist of @hb_code+@dhsw_code+number
set @dt_created = GETDATE()

DECLARE @code VARCHAR(9)

SET NOCOUNT ON;

SELECT @code = MAX(right(dhsw_code, 3))+1 FROM dental_worker WHERE left(hb_id, 3) IN (@hb_code)
AND dhsw_code LIKE '%'+@dhsw_code+'%'

SELECT @code, LEN(@code)
--look at len, must be length of 3 so if its 6, should be 006, or 36 should be 036
--add leading zeros where required

--do I do an if statement here?
--add in the @hb_code and @dhsw_code
SET @code = @hhb_code+@dhsw_code+@code

INSERT INTO dental_worker VALUES (@firstname, @surname, @address1, @telephone1, @code, @dt_created, NULL,NULL, NULL, NULL, NULL)

END

Kristen
Test

22859 Posts

Posted - 2012-01-11 : 06:37:22

"IN (@hb_code)"

That won't work the way you are (probably) expecting.

"left(hb_id, 3)"

that type of thing, in a WHERE clause, tends to be slow (just so you are aware!) If you have a Column containing "composite information" it would be better to split it, and store the individual bits in separate fields, so you can query them, and then combine them back together again for output (you can have a VIEW or COMPUTED COLUMN that does that, so you don't need to code it every time you want to use it.

"AND dhsw_code LIKE '%'+@dhsw_code+'%'"

That's also going to be slow, for the same reasons. The concept of "Contains" means that SQL has to scan through all the records, and will not (normally) use indexes etc. to help speed up the query

I haven't looked closely at your requirement (sorry, bit pressed for time) but could you do something like:


INSERT INTO dental_worker
SELECT @firstname, @surname, @address1, @telephone1, @hhb_code+@dhsw_code+ MAX(right(dhsw_code, 3))+1 , @dt_created, NULL,NULL, NULL, NULL, NULL
FROM dental_worker WHERE left(hb_id, 3) IN (@hb_code) -- You'll still have to sort the "IN" out properly
AND dhsw_code LIKE '%'+@dhsw_code+'%'

Note that this will give you a syntax error because

MAX(right(dhsw_code, 3))

is (presumably) a Char String, but

MAX(right(dhsw_code, 3)) + 1

will convert "MAX(right(dhsw_code, 3))" to a number, and then add one, and

@hhb_code+@dhsw_code+ MAX(right(dhsw_code, 3))+1

is a String Concatenation with a Numeric Element, for which there is no implicit conversion. At the least you will need:


@hhb_code+@dhsw_code+ (CONVERT(varchar(20), MAX(right(dhsw_code, 3))+1)

but its all very messy combining Char Strings and Numerics in this way. Much better to have separate columns, each of the appropriate data type.

Arumugam
Starting Member

11 Posts

Posted - 2012-01-11 : 06:48:02

Hi,

I couldn't understand your requirement completely. If you want to add defined no. of leading zeros with a value, you can use the code given below.

DECLARE @Code Int
DECLARE @Length Int
Set @Code = 2

--No.of leading Zeros you want
Set @Length = 3

--No. of zeros should be changed based on your requirement
Select Substring('000',1,@Length - LEN(@Code)) + Cast(@Code AS Varchar(Max))

crazyco
Starting Member

30 Posts

Posted - 2012-01-11 : 10:22:37

Not to worry, it was something pretty simple I had to add, just:

IF @code IS NULL
BEGIN
SET @code = 1
END

SELECT @code = CASE WHEN LEN(@code) = 1 THEN '00' +@code WHEN LEN(@code) = 2 THEN '0'+@code END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:25:54

quote:
Originally posted by crazyco

Not to worry, it was something pretty simple I had to add, just:

IF @code IS NULL
BEGIN
SET @code = 1
END

SELECT @code = CASE WHEN LEN(@code) = 1 THEN '00' +@code WHEN LEN(@code) = 2 THEN '0'+@code END

all of this can be simplied as

SELECT @code = RIGHT('000' + COALESCE(@code,'1') ,3)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources