Need to set parameters in stored procedures of sql server

Question

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

Pankaj Kumar Choudhary · Answer

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 Choudhary · Answer

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 Meka · Answer

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

?????? ??? · Answer

hi if you got answers can you please email it to me even i have same thing (manoharpalsa@gmail.com)

Abhi me · Answer

Ok.. thanks pankaj :)

Pankaj Kumar Choudhary · Answer

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 me · Answer

Pankaj you have taken username as Exists???

Abhi me · Answer

Thanks dorababu and pankaj... thank you so much.. i will implement them and try

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