Using procA to test procB which has output

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 2000 Forums

Transact-SQL (2000)

Using procA to test procB which has output

Author

Topic

bbondi
Starting Member

20 Posts

Posted - 2005-04-28 : 16:50:47

Hello,
ut_sp_Capitalize_It makes the first char of a string uppercase and returns it.

ut_testCapitalize_It exex's the other and verifies the output, if it is not uppercase or null the test fails.

exec ut_testCapitalize_It 'bingo!'

When I use this command: exec ut_testCapitalize_It then the code calls the Capitalize_It proc with 'bingo!' as the param.
=======================================================
ALTER proc ut_sp_Capitalize_It
@strIn VARCHAR(255), --Input Param
@strOut CHAR(255) OUTPUT --Output Param
as
IF @strIn IS NULL
BEGIN
RETURN NULL
END
DECLARE
@strlen VARCHAR(255),
@c VARCHAR(2)
SELECT @strlen = LEN(@strIn)
SELECT @strOut = ''
SET @c = SUBSTRING(@strIn,1,1)
SET @c = UPPER(@c)
SET @strOut = @strOut + @c + SUBSTRING(@strIn,2,@strlen - 1)
RETURN @strOut
GO
=============================================================
--Verify returned value is uppercased Bingo!
ALTER PROCEDURE ut_testCapitalize_It AS
BEGIN
DECLARE @outStr CHAR(500)
EXEC ut_sp_Capitalize_It 'bingo!', @outStr OUT
IF (ASCII(LEFT(@outStr,1)) <> ASCII('B')) --OR @outStr IS NULL
BEGIN
print 'is not uppercased'
EXEC tsu_failure 'Capitalize should make the first character uppercase'
print 'Failed'
print 'Returned value is: ' + cast(@outStr as char)
IF @outStr is null BEGIN print 'IS NULL' END
ELSE BEGIN print 'NOT NULL' END
RETURN -1
END
ELSE
BEGIN
print 'PASS'
END
RETURN 1
END
=============================================================
output is:
Failed
Returned value is:
NOT NULL

Obviously there is something I have missed. I am asking for clues, tidbits, and comments.

Thanks,
Bob

Do the Social Engineering teams need QA? Naw, let 'em have fun!

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-28 : 17:19:35

ALTER proc ut_sp_Capitalize_It
@strIn VARCHAR(255), --Input Param
@strOut CHAR(255) OUTPUT --Output Param
as
IF @strIn IS NULL
BEGIN
RETURN NULL
END
DECLARE
@strlen VARCHAR(255),
@c VARCHAR(2)
SELECT @strlen = LEN(@strIn)
SELECT @strOut = ''
SET @c = SUBSTRING(@strIn,1,1)
SET @c = UPPER(@c)
SET @strOut = @strOut + @c + SUBSTRING(@strIn,2,@strlen - 1)
RETURN @strOut
GO

should be:

SET @strOut = @c + SUBSTRING(@strIn,2,@strlen - 1)

and no need to RETURN the value, use the OUTPUT param.

Do you want this in a UDF? Hees an example, this will work for multiple words separated by spaces, ex. nathan skerl -> Nathan Skerl:

CREATE FUNCTION dbo.fn_format_case (@text_in VARCHAR(200))
RETURNS VARCHAR(200)
AS
BEGIN
	DECLARE @char VARCHAR(1),
		@text_out VARCHAR(200), 
		@pos INT
	
	If @text_in IS NOT NULL
	BEGIN
		SELECT 	@char = '', 
			@text_out = '', 
			@pos = 1,
			@text_in = UPPER(@text_in)
		WHILE @pos <= DATALENGTH(@text_in) 
		BEGIN
			-- set @char to first character
			SET @char = SUBSTRING(@text_in, @pos, 1)

			-- if previous letter is already UPPER then lowercase current pos
			IF ASCII(SUBSTRING(@text_in, @Pos - 1, 1)) Between 65 And 90  
			BEGIN
				SET @char = LOWER(@char)
			END

			-- build output string, increment position
			SET @text_out = (@text_out + @char)
			SET @pos = (@pos + 1)
		END
END
RETURN(@text_out)
END



-- usage: 
select dbo.fn_format_case ('nathan skerl')

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-04-28 : 17:28:08

Your way, you dont need to RETURN the value as you already declared it as OUTPUT parameter, just select the output... see below:

alter proc ut_sp_Capitalize_It 
	@strIn varchar(255), 
	@strOut varchar(255) output
as

declare @strlen VARCHAR(255), 
	@c VARCHAR(2) 

if @strIn is NULL
begin
	return
end

select	@strlen = LEN(@strIn),
	@strOut = '' 

select 	@strOut = UPPER(SUBSTRING(@strIn,1,1)) + SUBSTRING(@strIn,2,@strlen - 1) 
go



-- usage:
declare @out varchar(255)
exec ut_sp_capitalize_it 'nathan', @out output
select @out

bbondi
Starting Member

20 Posts

Posted - 2005-04-28 : 18:37:35

Great, got it no RETURN @Value when it is declared as an OUTPUT in the param section. DOH!

I can't wait until I can add an answer to the forum, until then...Many thanks.

P.S. I'm trying to use the TsqlUnit package, this is the only reason I'm using procs, cuz the cookbook does. But, yes the functions were easier to write an they did work. I'm struggling through the TsqlUnit concept, but it is coming together slowly. My SQL skills beyond basic queries is just 30 days old now, creeping on my knees, hehe.

Do the Social Engineering teams need QA? Naw, let 'em have fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources