newbie 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 2005 Forums

Transact-SQL (2005)

newbie help

Author

Topic

Pr0FiT
Starting Member

7 Posts

Posted - 2008-11-24 : 17:26:01

Hey guys. I think this is probably a simple thing but I'm not all that great with sql

I have a stored procedure (which I got from technet) which converts a 16-bit binary value to varchar(225). The SP returns everything great, in fact everything works great so far. What I'm struggling with is how to assign the return value to a variable so i can use it again in the loop I've got going. I hope thats clear. Here is the SP and the query I have so far:

SP


	@binvalue varbinary(255) AS
		DECLARE @charvalue varchar(255)
		DECLARE @i int
		DECLARE @length int
		DECLARE @hexstring char(16)
		SELECT @charvalue = '0x'
		SELECT @i = 1
		SELECT @length = datalength(@binvalue)
		SELECT @hexstring = '0123456789abcdef'
		WHILE (@i <= @length)
		BEGIN
			DECLARE @tempint int
			DECLARE @firstint int
			DECLARE @secondint int
			SELECT @tempint = convert(int, substring(@binvalue,@i,1))
			SELECT @firstint = floor(@tempint/16)
			SELECT @secondint = @tempint - (@firstint*16)
			SELECT @charvalue = @charvalue +
				substring(@hexstring, @firstint+1, 1) +
				substring(@hexstring, @secondint+1, 1)
			SELECT @i = @i + 1
		END
	SELECT ('nr:' + upper(substring(@charvalue,3,32)))

Query


USE mdb
DECLARE	@persid varchar(255)
DECLARE	@uuid binary(16)
DECLARE	nr_cursor CURSOR
FOR
SELECT	dbo.usp_owned_resource.owned_resource_uuid
FROM	dbo.usp_owned_resource INNER JOIN
		dbo.ca_owned_resource ON dbo.usp_owned_resource.owned_resource_uuid = dbo.ca_owned_resource.own_resource_uuid
WHERE	(dbo.usp_owned_resource.zlast_inv_date < (DATEDIFF(ss,'19700101',GETDATE()) - 7171200)) AND
		(dbo.ca_owned_resource.resource_class=400200) AND
		(dbo.ca_owned_resource.inactive = 0)
	OPEN nr_cursor
	FETCH NEXT FROM nr_cursor INTO @uuid
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		IF (@@FETCH_STATUS <> -2)
		BEGIN
		EXECUTE sp_hexadecimal @uuid
		END
		FETCH NEXT FROM nr_cursor INTO @uuid
	END
CLOSE nr_cursor
DEALLOCATE nr_cursor

So to reitterate: after the EXECUTE I would like to take the return value and use that in another SELECT statement. In fact I don't even need it returned to the query window as it is now, straight to a var is fine by me. Thanks in advance for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 23:29:17

either add an OUTPUT aparmeter to sp and return value through it or rewrite it as a function and return value. the first approach will be something like


@binvalue varbinary(255),
@retvalue varchar(255) OUTPUT AS
		DECLARE @charvalue varchar(255)
		DECLARE @i int
		DECLARE @length int
		DECLARE @hexstring char(16)
		SELECT @charvalue = '0x'
		SELECT @i = 1
		SELECT @length = datalength(@binvalue)
		SELECT @hexstring = '0123456789abcdef'
		WHILE (@i <= @length)
		BEGIN
			DECLARE @tempint int
			DECLARE @firstint int
			DECLARE @secondint int
			SELECT @tempint = convert(int, substring(@binvalue,@i,1))
			SELECT @firstint = floor(@tempint/16)
			SELECT @secondint = @tempint - (@firstint*16)
			SELECT @charvalue = @charvalue +
				substring(@hexstring, @firstint+1, 1) +
				substring(@hexstring, @secondint+1, 1)
			SELECT @i = @i + 1
		END
	SELECT @retvalue=('nr:' + upper(substring(@charvalue,3,32)))
...

Query

USE mdb
DECLARE	@persid varchar(255)
DECLARE	@uuid binary(16),@ret_value varchar(255)
DECLARE	nr_cursor CURSOR
FOR
SELECT	dbo.usp_owned_resource.owned_resource_uuid
FROM	dbo.usp_owned_resource INNER JOIN
		dbo.ca_owned_resource ON dbo.usp_owned_resource.owned_resource_uuid = dbo.ca_owned_resource.own_resource_uuid
WHERE	(dbo.usp_owned_resource.zlast_inv_date < (DATEDIFF(ss,'19700101',GETDATE()) - 7171200)) AND
		(dbo.ca_owned_resource.resource_class=400200) AND
		(dbo.ca_owned_resource.inactive = 0)
	OPEN nr_cursor
	FETCH NEXT FROM nr_cursor INTO @uuid
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		IF (@@FETCH_STATUS <> -2)
		BEGIN
		EXECUTE sp_hexadecimal @uuid,@ret_value OUTPUT
                SELECT @ret_value
		END
		FETCH NEXT FROM nr_cursor INTO @uuid
	END
CLOSE nr_cursor
DEALLOCATE nr_cursor

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 23:39:50

here's the second approach

CREATE FUNCTION gethexadecmal
(
...
	@binvalue varbinary(255)
)RETURNS varchar(255)  AS
BEGIN
		DECLARE @charvalue varchar(255),@ret_value varchar(255)
		DECLARE @i int
		DECLARE @length int
		DECLARE @hexstring char(16)
		SELECT @charvalue = '0x'
		SELECT @i = 1
		SELECT @length = datalength(@binvalue)
		SELECT @hexstring = '0123456789abcdef'
		WHILE (@i <= @length)
		BEGIN
			DECLARE @tempint int
			DECLARE @firstint int
			DECLARE @secondint int
			SELECT @tempint = convert(int, substring(@binvalue,@i,1))
			SELECT @firstint = floor(@tempint/16)
			SELECT @secondint = @tempint - (@firstint*16)
			SELECT @charvalue = @charvalue +
				substring(@hexstring, @firstint+1, 1) +
				substring(@hexstring, @secondint+1, 1)
			SELECT @i = @i + 1
		END
	SELECT @ret_value=('nr:' + upper(substring(@charvalue,3,32)))
RETURN @ret_value
END

Query

USE mdb
DECLARE	@persid varchar(255)
DECLARE	@uuid binary(16)
DECLARE	nr_cursor CURSOR
FOR
SELECT	dbo.usp_owned_resource.owned_resource_uuid
FROM	dbo.usp_owned_resource INNER JOIN
		dbo.ca_owned_resource ON dbo.usp_owned_resource.owned_resource_uuid = dbo.ca_owned_resource.own_resource_uuid
WHERE	(dbo.usp_owned_resource.zlast_inv_date < (DATEDIFF(ss,'19700101',GETDATE()) - 7171200)) AND
		(dbo.ca_owned_resource.resource_class=400200) AND
		(dbo.ca_owned_resource.inactive = 0)
	OPEN nr_cursor
	FETCH NEXT FROM nr_cursor INTO @uuid
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
		IF (@@FETCH_STATUS <> -2)
		BEGIN
		SELECT dbo.gethexadecimal(@uuid)
		END
		FETCH NEXT FROM nr_cursor INTO @uuid
	END
CLOSE nr_cursor
DEALLOCATE nr_cursor

Pr0FiT
Starting Member

7 Posts

Posted - 2008-11-25 : 08:45:44

Rock on. That worked perfectly. Thanks for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 09:41:33

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75346

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 10:32:28

For SQL Server 2008, see this

DECLARE	@a BINARY(2),
	@b VARCHAR(5),
	@c INT

SELECT	@a = 0x4032,
	@b = '4032',
	@c = 4032

SELECT	'_' + CONVERT(VARCHAR(8), @a, 0) + '_',
	'_' + CONVERT(VARCHAR(8), @a, 1) + '_',
	'_' + CONVERT(VARCHAR(8), @a, 2) + '_'

UNION ALL

SELECT	'_' + CONVERT(VARCHAR(8), @b, 0) + '_',
	'_' + CONVERT(VARCHAR(8), @b, 1) + '_',
	'_' + CONVERT(VARCHAR(8), @b, 2) + '_'

UNION ALL

SELECT	'_' + CONVERT(VARCHAR(8), @c, 0) + '_',
	'_' + CONVERT(VARCHAR(8), @c, 1) + '_',
	'_' + CONVERT(VARCHAR(8), @c, 2) + '_'

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:25:47

quote:
Originally posted by Pr0FiT

Rock on. That worked perfectly. Thanks for your help.

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources