Need to set parameters in stored procedures of sql server

Abhi me

11y1.1k

Hello,

I have task and i got struck with these SQL server Stored procedures. Am confused and not understanding on how to declare them and write the parameterss. Mainly the select statements for the functionality .

Please can one help me in writing these

following is the table showing stored procedures requirements

SP Name	Input Parameters	Output Parameters	Functionality
Sp_Insurance_UsernameExists	@username nvarchar(50)	@returnValue int	Should check if the passed in username exists in the webcrendetials table. IF exists, return 1 otherwise return 0
Sp_Insurance_InsertCustomer	All the columns of customer table except ID (as ID is an identity column) and also the columns of the Web Credentials table	@ID int	Should insert a row in the customer table and return the ID of the created row
Sp_Insurance_InsertCustomerOrder	All the columns from the order table and the ID of the customer who was inserted by the above stored proc.	@ID int	Insert the row in the order and return the order ID

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

?????? ???11y ago
hi if you got answers can you please email it to me even i have same thing ([email protected])
Abhi me11y ago
Ok.. thanks pankaj :)
Pankaj Kumar Choudhary11y ago
Hello Abhi I used this Exist variable for store the status
SET @EXIST=( SELECT COUNT(*) from dbo.webcrendetials e where E.username = @username)

In This Command Exist Variable contain the number of user that's name is =username

IF @EXIST> 0
SET @EXIST = 1
ELSE
SET @EXIST = 0

On the Behalf of previous value of EXIST Variable we store the new value in EXIST

SELECT @EXIST;

This command retrieve the value from EXIST Variable

If you have any doubt then please ask
Abhi me11y ago
Pankaj you have taken username as Exists???
Abhi me11y ago
Thanks dorababu and pankaj... thank you so much.. i will implement them and try
Pankaj Kumar Choudhary11y ago
Use this SP for Your Second query

CREATE PROC Sp_Insurance_InsertCustomer(@COL1 DATATYPE, @COL2 DATATYPE .............@COLn DATATYPE)
AS
BEGIN
INSERT INTO customer VALUES (@COL1,@COL2..........@COLn)
SELECT MAXI(ID) FROM customer
END

You can write 3rd query in same way
Pankaj Kumar Choudhary11y ago
Hello abhi you can useSP for your first QUERY
Create procedure Sp_Insurance_UsernameExists( @username nvarchar(50))
AS
BEGIN
DECLARE @EXIST INT;
SET @EXIST=( SELECT COUNT(*) from dbo.webcrendetials e where E.username = @username)
IF @EXIST> 0
SET @EXIST = 1
ELSE
SET @EXIST = 0
SELECT @EXIST;
END
Dorababu Meka11y ago
First thing never use SP_ for the stored procedures that you are creating.

Create procedure Sp_Insurance_UsernameExists @username nvarchar(50)
@out_return_value INT
BEGIN
SELECT COUNT(*) from table where columnname= @username
IF @@ROWCOUNT = 0
SET @out_return_value = 0
ELSE
SET @out_return_value = 1

2nd and 3rd stored procedure

you don't need @Id here, if you would like to return use
SELECT @@IDENTITY after your insert query, which will give you the last inserted id