Help with CASE in Stored Procedure

hi guys! Help please..I'm new to sotred procedure. Currently, i have a stored procedure (see Below) but i can't figure out on how to solve the errors(see below). What im trying to do with my stored procedure is to take two(2) parameters, the string to be search(@.Search_Arg) and the search by(@.Search_by) Either by Login name or by firstname. Under my CASE statement, When the @.Search_by is equal to Username i want to select the user with User_login equals to @.Search_Arg and when the @.Search_by is equal to Firstname it will select all user with User_FirstName equals to @.Search_Arg...Any help please..Thanks in advance!

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN

CASE @.Search_By
WHEN 'Username' THEN SELECT * FROM RCPS_UserAccount WHERE User_login = @.Search_Arg
WHEN 'Firstname' THEN SELECT * FROM RCPS_UserAccount WHERE User_FirstName = @.Search_Arg
END
END

Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 7
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 9
Incorrect syntax near the keyword 'WHEN'.
Msg 156, Level 15, State 1, Procedure sp_User_Search, Line 10
Incorrect syntax near the keyword 'END'.what you have there is a dynamic search condition. read this for various techniques:

http://www.sommarskog.se/dyn-search.html

in particular, this might suit you:

select col1, col2, col3 from RCPS_UserAccount
where
(User_login = @.user_login or @.user_login is null) and
(User_FirstName = @.user_firstname or @.user_firstname is null)|||Case can return only one value at a time
use of 'Select *' is not allowed in case statement. What you can do is :

CREATE PROCEDURE sp_User_Search
@.Search_Arg varchar(50),
@.Search_By varchar(20)
AS
BEGIN
SELECT * FROM RCPS_UserAccount
WHERE
CASE
When @.Search_By = 'Username' then
User_login
When @.Search_By = 'Firstname' then
User_FirstName
End
= @.Search_Arg
END
END

...but I think way suggested by jezemine is better than this one.sql

Help with Agent Jobs

Hi
I am suddenly receiving a number fo failed jobs, upon looking at the
history, there is very little to go on, any help on where i can look to
solve this would be great. The only error i receive is below, I am the
owner.
The job failed. The Job was invoked by User EFMG\InglisG. The last step to
run was step 1 (Update Clients).Hi,
In the Job History screen click the "Job step details" and look into the
details messages of each step id.
You will get more details there to troubleshoot.
Thanks
Hari
MCDBA
"Gordon" <inglisg@.edfd.com> wrote in message
news:OLfX#s4IEHA.228@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am suddenly receiving a number fo failed jobs, upon looking at the
> history, there is very little to go on, any help on where i can look to
> solve this would be great. The only error i receive is below, I am the
> owner.
> The job failed. The Job was invoked by User EFMG\InglisG. The last step
to
> run was step 1 (Update Clients).
>

Help to solve this problem!

I'm a beginer!

I host my web on a hosting service provider. But when I access an error occur:

Server Error in '/' Application.

---

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.DetailsView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.DetailsView.EnsureDataBound() +181 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

---

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

give this a shot:

In the SQL Management Studio, RightClick the SQL server instance, Choose
Properties, and under Select a Page list, click Connections. Now you should
see a checkbox labelled "Allow Remote Connections to This Server". Make sure
it is checked.

hope this helps -- jp