Cannot use the OUTPUT option when passing a constant to a stored procedure.

When we use Output parameter in a Stored procedure, There is a chance to get the following Error... Why?, and How to solve it ?

Msg 179, Level 15, State 1, Line 3

Cannot use the OUTPUT option when passing a constant to a stored procedure.

/*Creating a stored procedure*/

If OBJECT_ID('Usp_Procedure1') Is Not Null

Drop Proc Usp_Procedure1

Go

Create Proc Usp_Procedure1
(

@Param1 Varchar(50) Output

)As

Begin

Set Nocount On

Select @Param1 = 'SQL Server Administration'

End

Go

/*Executing the stored procedure*/

Exec Usp_Procedure1 'SQL Server BI' Output

Go
 
The following error occurred...

Msg 179, Level 15, State 1, Line 2

Cannot use the OUTPUT option when passing a constant to a stored procedure.

Why?

- The reason is, we have one Output parameter in a stored procedure(Usp_Procedure1), Normally, Output parameters can be used in both directions (Input and Output).

- So, when we access the stored procedure, Output keyword is required only to return the data through the Output parameters. If we don't want to return any data through the Output parameters then, The Output keyword should not be used.

If we want to return any data through the Output parameter:

Declare @P1 Varchar(50)

Exec Usp_Procedure1 @P1 Output

Select @P1

Go

- Output keywork should be used along with parameter @P1

 

If we don't want to return any data through the Output parameter:
Exec Usp_Procedure1 'SQL Server BI'

Go

- Output parameter should not be used when passing constant value instead of variable.

Conclusion:

1. Output keyword should not be used when we pass the constant value to the stored procedure directly instead of a variable.

Exec Usp_Procedure1 'SQL Server BI'

Go

2. Output keyword should be used when the value should be returned from the stored procedure through the variable.

Declare @P1 Varchar(50)

Exec Usp_Procedure1 @P1 Output

Select @P1

Go

Column, parameter, or variable #1: Cannot find data type XYZ

The following Error occurred when we refer a user defined datatype which was created in another database. Even in different database or creating temporary tables ( #tables (or) ##tables ). Temporary tables created in Tempdb database.

/*Creating User defined datstype in DB1 database*/
Use DB1
Go
Create Type UDT_EMail From NVarchar(100)
Go

/*Refering the type when creating Temporary table*/
Create Table #Table1
(
Id Int Identity(1,1),
EMail UDT_EMail
)
 
The following error occurred
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #2: Cannot find data type UDT_EMail.
 
#Table1 will be created in Tempdb database, So, It could not find any data type in Tempdb database. But, It can be refered by Table variable..

/*Refering the user defined type in Table varible*/ 
Use DB1
Go
Declare @Tbl_Variable Table
(
EMail UDT_EMail
)

Is there any way to refer the Type in another database using three part naming convension ?

/*Refering the type using three part naming convension*/
Use DB1
Go
Create Table #Table11
(
Id Int Identity(1,1),
EMail DB1.dbo.UDT_EMail
)

The following error occurred
 
Msg 117, Level 15, State 2, Line 4
The type name 'DB1.dbo.UDT_EMail' contains more than the maximum number of prefixes. The maximum is 1.

Because, The type allows only 1 prefix, not morethan 1.

Note: No way to refer the User defined type in Tempdb (or) another database unless the Type created in that particular database itself. Even the type was created in Master database.

Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

When we pass a parameter value using the name(called Named parameter), should be little focus on that.

/*Creating a stored procedure*/
Create Proc Usp_Sample
(@Para1 Int,
@Para2 Int,
@Para3 Int
)As
Begin
Set Nocount on
Select @Para1 + @Para2 + @Para3
End
Go

/*#1. Executing the procedure*/
Exec Usp_Sample @Para1=1, 2, 3
Go

It'll throw an error...
Msg 119, Level 15, State 1, Line 1
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

/*#2. Executing the procedure*/
Exec Usp_Sample 1, @Para2=2, 3
Go

It'll also throw an error...
Msg 119, Level 15, State 1, Line 1
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Reason is...
- Whenever the parameter value is passed using Named Parameter(ie: @Name=Value), rest of the all parameters next to the named parameter also should be Named parameter.

i:e
1. If a stored procedure have 3 parameters(IN).
2. If the First parametere used with parameter name(named parameter) then, all the remaining (Second, Third) parameters also should be named parameter.

Exec Usp_Sample @Para1=1, @Para2=2, @Para3=3
Go

3. If the Second parametere used with parameter name(named parameter) then, the Third parameters also should be named parameter.But, the First parameter no need to be a Named parameter.

Exec Usp_Sample 1, @Para2=2, @Para3=3
Go

4. If the Third parametere used with parameter name(named parameter) then, the First, Second parameters no need to be a Named parameter.

Exec Usp_Sample 1, 2, @Para3=3
Go