CREATE PROCEDURE GetActiveServerURL
(
@ActiveURL VARCHAR(100) OUTPUT
)
AS
BEGIN
SELECT @ActiveURL = RedirectURL FROM HealthCheck WHERE IsPrimary=1 AND Status=1
END
Error Message : Procedure or function 'GetActiveServerURL' expects parameter '@ActiveURL', which was not supplied.
As I have declared ActiveURL as output parameter stil it expects as input
You must mention in your call that the parameter is output also.
DECLARE @ActiveURL VARCHAR(100)
EXEC GetActiveServerURL @ActiveURL OUTPUT
Here how to execute SP from C#
string activeURL;
using(var conn = new SqlConnection(connectionString))
using(var command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "GetActiveServerURL";
command.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@ActiveURL", SqlDbType.VarChar, 100);
param.Direction = ParameterDirection.Output,
command.Parameters.Add(param);
conn.Open();
command.ExecuteNonQuery();
activeURL = param.Value;
}
You need to call the procedure with a parameter and include the OUTPUT directive.
SET @ActiveURL = 'http://'
EXEC GetActiveServerURL @ActiveURL OUTPUT
CREATE PROCEDURE GetActiveServerURL
(
DECLARE @ActiveURL VARCHAR(100) OUTPUT
)
AS
BEGIN
SELECT @ActiveURL = RedirectURL FROM HealthCheck WHERE IsPrimary=1 AND Status=1
END
EXEC GetActiveURL @ActiveURL OUTPUT
Error : Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Procedure GetActiveServerURL, Line 3 Incorrect syntax near 'OUTPUT'. Msg 137, Level 15, State 1, Procedure GetActiveServerURL, Line 7 Must declare the scalar variable "@ActiveURL"
DECLARE @ActiveURL VARCHAR(100); EXEC GetActiveServerURL @ActiveURL OUTPUT ;