passing input - 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

General SQL Server Forums

New to SQL Server Programming

passing input

Author

Topic

kidaduo
Starting Member

45 Posts

Posted - 2007-10-31 : 08:47:50

Need help.

I’m trying to pass inputs from one stored proc to another but I’m having problems in passing them.

First proc (input_passing) should pass the following inputs – FirstName, LastNmae and RecordID to the second proc (input_receive) – then the second proc will diplay the outcome – YES/NO if there is a match.
------------------------------------------------------------------------------------------
First proc (input_passing)
-------------------------------------------------------------------------------------------------------
DECLARE
@RecordID VARCHAR(11),
@FirstName VARCHAR(60),
@LastName VARCHAR(60)

--DECLARE tbInputs CURSOR FOR
SELECT
RecordID,
FirstName,
LastName

FROM
tbInputs

OPEN tbInputs

FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName

WHILE @@FETCH_STATUS = 0
BEGIN
--GET RECORD
SET @RecordID = RecordID from dbo.tbInputs)

END

FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName

END

CLOSE tbInputs
DEALLOCATE tbInputs
exec input_receive
----------------------------------------------------------------------------------------------------------------- Second proc. (input_receive)

CREATE PROCEDURE input_receive
@RecordID VARCHAR(11) = NULL,
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@RecordMatch BIT OUTPUT,
@MatchedOn VARCHAR(400) OUTPUT
AS

-- Declarations
DECLARE @Count INT
DECLARE @Found BIT
DECLARE @AlertID DECIMAL(18,0)
DECLARE @AlertCreateDate DATETIME
-- END Declarations

-- ******* Initializations **********
SET @Count = 0
SET @Found = 0
-- DEFAULT to No File Match
SET RecordMatch = 0
SET @MatchedOn = 'NO FOUND'
-- ******* END Initializations **********

-- First check to see if the RecordID Matches
IF( @RecordID IS NOT NULL AND @RecordID <> '' )
BEGIN
IF( CAST( REPLACE(@RecordID, '-', '') AS DECIMAL(18,0)) > 0 )
BEGIN
SELECT @Count = COUNT(*)
FROM tbAlert
WHERE REPLACE(RecordID, '-', '') = REPLACE(@RecordID, '-', '')

IF( @Count > 0 )
BEGIN
SET @RecordMatch = 1
SET @MatchedOn = 'FOUND : RecordID'
RETURN
END
END
END

Josephine

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-31 : 09:12:08

Try this - you need to do something wit the output parameters or get rid of them.

declare @RecordMatch BIT ,
@MatchedOn VARCHAR(400)

--DECLARE tbInputs CURSOR FOR
SELECT
RecordID,
FirstName,
LastName

FROM
tbInputs

OPEN tbInputs

FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName

WHILE @@FETCH_STATUS = 0
BEGIN
exec input_receive @RecordID, @FirstName, @LastName, @RecordMatch out, @MatchedOn out

FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
END

END

CLOSE tbInputs
DEALLOCATE tbInputs

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources