i want to create a cursor inside a procedure which takes
parameters and i want the cursor to produce a customised
error if the user inputs a null

Copy the following in Query Analyzer:

SET NOCOUNT ON

IF OBJECT_ID('XTable') IS NOT NULL
DROP TABLE XTable

CREATE TABLE XTable
(
CustomerID INT,
Name VARCHAR(20)
)
GO

INSERT INTO XTable VALUES (1,'First')
INSERT INTO XTable VALUES (1,'Second')
INSERT INTO XTable VALUES (2,'Third')
INSERT INTO XTable VALUES (2,'Fourth')
INSERT INTO XTable VALUES (3,'Fifth')
INSERT INTO XTable VALUES (3,'Sixth')

GO

IF OBJECT_ID('XProcedure') IS NOT NULL
DROP PROCEDURE XProcedure

GO
CREATE PROCEDURE XProcedure
@X INT = null
AS

IF @X IS NULL
BEGIN
PRINT 'Hey Akash! You forgot to supply the parameter'
END

ELSE
BEGIN
DECLARE @vCustomerID INT
DECLARE @vName VARCHAR(20)
DECLARE @c INT
SET @C = 0

DECLARE C1 CURSOR
FOR
SELECT * FROM XTable WHERE CustomerID = @X

OPEN C1

FETCH NEXT FROM C1
INTO @vCustomerID, @vName

WHILE @@FETCH_STATUS =0
BEGIN
SET @C = @C +1
PRINT 'Row Number: ' + CONVERT(VARCHAR(5),@c)
PRINT @vName
FETCH NEXT FROM C1
INTO @vCustomerID, @vName
END
CLOSE C1
DEALLOCATE C1
END
GO

--Execute the XProcedure here
EXECUTE XProcedure


---
Rohtash Kapoor
http://www.sqlmantra.com



[quoted text, click to view]

Hi Jain!

One think I didn't understand is that why you want to use cursors to raise
an error if the user inputs a null value. Will this work for you?

CREATE PROCEDURE getUserInfo
@UserID int
as

-- Check input parameters
IF @UserID is null)
BEGIN
RAISERROR 1111,'User Id is cannot be null'
END

---Do some operations using cursors

GOTO ExitProc

ErrorHandler:
BEGIN
RAISERROR @ErrorNumber @ErrorString
END

ExitProc:
--
--
--

- Kanth



[quoted text, click to view]