Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
SProc (T-SQL) OUT question
Message
 
 
À
18/04/2007 15:28:07
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2000
Divers
Thread ID:
01217449
Message ID:
01217458
Vues:
10
This message has been marked as a message which has helped to the initial question of the thread.
Make UserId ouput parameterb but change in SP only when action is "QUERY".

>I created a table called Setup to hold only one record to show if a certaing process is running or not.
>An Admin through a web-interface will run a quarterly billing process. The process may be interrupted by the user for several reasons, so I'd like to know if it was started, when and by whom, to show the info in the admin web-page next time they re-start.
>
>I created one SP with parameters:
>e.g. (C#):
>
>protected DataAccessLayer oDAL = new DataAccessLayer();
>protected sUserID = "user ID goes here";
>if (oDAL.Progress("QUERY", sUserID))
>{
>  // do something
>}
>
>Parameters are: "START", "STOP", "QUERY" to signify in progress, or finished or check state.
>
>Stored Procedure:
>
>ALTER PROCEDURE dbo.sp_InProgress
>(
>   @action char(5) = 'QUERY',
>   @UserId char(10) = NULL
>)
>AS
>SET NOCOUNT ON
>IF @action NOT IN ('START','STOP','QUERY') SET @action = 'QUERY';
>IF @action = 'START'
>BEGIN
>   IF EXISTS (SELECT InProgress FROM setup)
>   BEGIN
>      UPDATE setup
>      SET inprogress = 1, datetime = getDate(), who = @UserId
>   END
>   ELSE
>   BEGIN
>      INSERT INTO setup (datetime, inprogress, who) VALUES (getDate(), 1, @UserId)
>   END
>END
>ELSE
>IF @action = 'STOP'
>BEGIN
>   IF @UserId IS NULL
>   BEGIN
>      UPDATE setup
>      SET inprogress = 0, datetime = getDate()
>   END
>ELSE
>   BEGIN
>      UPDATE setup
>      SET inprogress = 0, datetime = getDate(), who = @UserId
>   END
>END
>ELSE
>-- 'QUERY'
>BEGIN
>   SELECT * FROM setup
>END
>
>
>Seem simple enough and now I can start it (changing the BIT column InProgress to 1, or 'STOP' to set it to 0
>If I pass 'QUERY' it returns a record. All is fine.
>
>Now I want to add OUT parameters to return also datetime and who started the process if param is 'QUERY'.
>
>The problem is that @UserId is already a partameter I pass when going in. I'd like it to get back only if 'QUERY'. I'm not a wizard at T-SQL. Is there a better way to do this?
>
>TIA,
--sb--
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform