Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
SProc (T-SQL) OUT question
Message
 
 
To
18/04/2007 15:28:07
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Environment versions
SQL Server:
SQL Server 2000
Miscellaneous
Thread ID:
01217449
Message ID:
01217458
Views:
11
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--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform