/* error in the following stored procedure */ DECLARE @OutPutValue VARCHAR(100)

Question

/* error in the following stored procedure */

DECLARE @OutPutValue VARCHAR(100)

EXEC spExample 'CodeProject', @OutPutValue OUTPUT

PRINT @OutPutValue

*/

CREATE PROCEDURE usp_Example

@parameter1 VARCHAR(100),

@parameter2 VARCHAR(200) OUTPUT

DECLARE @parameter3 VARCHAR(100)

SET @parameter3 = ' Your development resources.'

IF @parameter1 IS NOT NULL AND LEN(@parameter1) > 1

SELECT @parameter2 = 'The '

+ @parameter1

+ @parameter3

ELSE SELECT @parameter2 = 'Ramesh is cool!'

RETURN

END

GO

DECLARE @parameter2 AS VARCHAR(200)

EXEC usp_Example '', @parameter2 output

SELECT @parameter2 AS NAME

Explanation / Answer

CREATE TABLE tbl_Students

(
[Studentid] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [nvarchar](200) NOT NULL,
[Lastname] [nvarchar](200) NULL,
[Email] [nvarchar](100) NULL
)
Insert into tbl_Students (Firstname, lastname, Email)
Values('Vivek', 'Johari', 'vivek@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
Values('Pankaj', 'Kumar', 'pankaj@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
Values('Amit', 'Singh', 'amit@abc.com')

Insert into tbl_Students (Firstname, lastname, Email)
Values('Manish', 'Kumar', 'manish@abc.comm')

Insert into tbl_Students (Firstname, lastname, Email)
Values('Abhishek', 'Singh', 'abhishek@abc.com')
Create Procedure Procedure-name
(
Input parameters ,
Output Parameters (If required)
)
As
Begin
Sql statement used in the stored procedure
End
Create PROCEDURE Getstudentname(

@studentid INT --Input parameter , Studentid of the student

)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END
Create PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR(200) OUT -- Out parameter declared with the help of OUT keyword
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END
Alter PROCEDURE GetstudentnameInOutputVariable
(

@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR (200) OUT, -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname,
@StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END

Create Procedure InsertStudentrecord
(
@StudentFirstName Varchar(200),
@StudentLastName Varchar(200),
@StudentEmail Varchar(50)
)
As
Begin
Insert into tbl_Students (Firstname, lastname, Email)
Values(@StudentFirstName, @StudentLastName,@StudentEmail)
End

Execute Getstudentname 1
Exec Getstudentname 1

Declare @Studentname as nvarchar(200) -- Declaring the variable to collect the Studentname
Declare @Studentemail as nvarchar(50) -- Declaring the variable to collect the Studentemail
Execute GetstudentnameInOutputVariable 1 , @Studentname output, @Studentemail output
select @Studentname,@Studentemail -- "Select" Statement is used to show the output from Procedure

create proc myproc
AS
return 'SSS'

declare @ret varchAR(max)
exec sp_executesql N'exec @ret =procc',
N'@ret varchar(MAX) OUTPUT',@ret = @ret OUTPUT select @ret as result

declare @bla varchar(100)
select @bla = sp_Name 9999, 99989999, 'A', 'S', null

DECLARE @Output VARCHAR(100)

CREATE TABLE #tmpTable
(
OutputValue VARCHAR(100)
)
INSERT INTO #tmpTable (OutputValue)
EXEC dbo.sp_name 9999, 99989999, 'A', 'S', null

SELECT
@Output = OutputValue
FROM
#tmpTable

DROP TABLE #tmpTable

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.