[oohrah]

Hello everyone. I am new to dealing with SQL totally and have searched and searched but I am humbly here for assistance. I need to write a function for SQL 2005 where I pass 2 string variables to it it then needs to join them, do a trim and and if a certian character or charters are the only remaing it needs to return a null string. Something like this if written in vb. Any help is apriciated

Function chkVal(byval var1, byval var2) as string
dim var3 as string

  var3 = var1 & "/" & var2

  if trim(var3)="/" then
	var3 = ""
  end if

  chkVal = var3
end function

I would prefer a tutorial as nothing is learned if I don't figure it out myself. Definately need guidance though

[Martyr2]

Well you could create a stored procedure like this...

CREATE PROCEDURE ConcatDemo (
	@string1 varchar(10),
	@string2 varchar(10),
	@result varchar(20) OUTPUT)
AS

DECLARE @concat varchar(20); 

SET @concat = @string1 + @string2

IF RTRIM(LTRIM(@concat)) = '/'
	SET @result = NULL
ELSE
	SET @result = @concat

Which would create a procedure which would take two parameters (string1 and string2), concatenate them into @concat and then after a left and right trim, check if it is equal to '/'. If it is, return NULL. If not, it would return the concatenated values together.

Where you can find information on this is on the net for any stored procedure tutorial. The important parts is "how to create a stored procedure", "passing in parameters and OUTPUT parameters", and the functions RTRIM and LTRIM. The rest should be pretty self explanatory.

You could also achieve a similar effect with a user function like...

CREATE FUNCTION ConcatDemoFunc(@string1 varchar(10), @string2 varchar(10)) 

RETURNS varchar(20)
AS
BEGIN
	DECLARE @result varchar(20);
	DECLARE @concat varchar(20); 

	SET @concat = @string1 + @string2

	IF RTRIM(LTRIM(@concat)) = '/'
		SET @result = NULL
	ELSE
		SET @result = @concat

	RETURN @result

END

Hope it helps!

"At DIC we be stored procedure loving code ninjas... our procedures are never stored, they are active and kicking coders butts!"

This post has been edited by Martyr2: 22 August 2009 - 07:41 PM

[oohrah]

Thanks for your reply. It worked like a pro. I atleast learned you had to include the dbo too anyway got another question for ya as you seem like a very intelligent person on this stuff. I have an already complicated query going with numerous joins and to try and clear some cluter I was wondering if there is a way to, in the query through another function or procedure, I could pull the full name of say a person from another table based on their intials stored in the table I am querying. If I need to start another post I will but if you could guide me I would be much appriciative. I would even offer to name my first born after you but seeing that I am already 5 in on that matter and too old to deal with that again I can offer you a big thanks

[Martyr2]

I am still a bit hazy on what you are asking but if I understand you correctly you could certainly create another function/procedure which will pull a person's name based on their initials into your procedure and continue working on it from there. Your query most likely would contain something with the "like" operator for wild card matching. For instance, to pull all names that match initials "S K" you could do select names from your_table where names like 'S% K%' which would pull names like "Sam Kerry" and "Samantha Kensington".

Put this query into its own function and then you can execute it from your other procedure, bring in the results, and work on them further there.

Hopefully that is what you were asking.

[oohrah]

Sounds on track. Here is what i have so far. I am getting an error though : Incorrect syntax near '@result'.

CREATE FUNCTION [dbo].[DoPullName](@Nint varchar(10)) 

RETURNS varchar(20)
AS
BEGIN
	DECLARE @result varchar(200);

	@result = SELECT FIRSTNAME + ' ' + LASTNAME AS UNAME FROM tblEmployees WHERE (INITIALS = @Nint)

	RETURN @result

END

[Martyr2]

Just need to change up how you set your @result

CREATE FUNCTION [dbo].[DoPullName](@Nint varchar(10))

RETURNS varchar(200)
AS
BEGIN
	DECLARE @result varchar(200);

	SELECT @result = FIRSTNAME + ' ' + LASTNAME FROM tblEmployees WHERE (INITIALS = @Nint)

	RETURN @result

END

Notice how we select into the variable @result here. You should also make sure that you are returning a value the same size as what you declared @result. You have a varchar 200 but say it returns a varchar 20. These should match. Give this a whirl and see if it works.

[ForcedSterilizationsForAll]

You'll have issues with that if there is more than one person with the same initials.

[oohrah]

ForcedSterilizationsForAll, on 25 Aug, 2009 - 10:48 AM, said:

You'll have issues with that if there is more than one person with the same initials.

Yea but that won't happen. Hasn't happened in 10 years and they must be unique

[ForcedSterilizationsForAll]

So do you just not hire people that have the same initials? Is it like "sorry, we already have someone with the initials SD," or do you add numbers?

[rgfirefly24]

Also remember when working with Variables that you need to DECLARE then SET:


I.E:

DECLARE @VariableName varchar(10)
 
SET @VariableName = A Value

[oohrah]

FSFA, there are only 5 people and there will never be an instance that this will happen and if it does it will be dealt with at that time