Update & Select in one?!

[Abbas2891]

Hello,

I am having difficulty finding a way to convert this code into a stored
procedure on SQL Server...Here is the original code

    IF Request("updflag" & n) = "Y" then 
 
 
                cmdTemp.CommandText = " SELECT ast.asset_id, " & _ 
                                      " mast.meter_read_due_dt, mast.meter_sort, 
mast.update_flag " & _ 
                                      " From asset ast, meter_asset mast" & _ 
                                      " WHERE (ast.asset_id='" & Request("assetcode" 
& n) & "')" & _ 
                                      " AND   (ast.asset_id = mast.asset_id)" 
 
 
                cmdTemp.CommandType = adCmdText                                         'Text Command 
                Set cmdTemp.ActiveConnection = BEConn                           'Set database connection 
                objRS.Open cmdTemp, , adOpenKeyset, adLockOptimistic 
                If Not objRS.EOF and Not objRS.BOF then 
                        sAssetCD = objRS.Fields("asset_id").value 
                        dtMeterDate = objRS.Fields("meter_read_due_dt") 
                        objRS.Fields("meter_sort") = Trim(Request("newsort" & n)) 
                        objRs.Fields("update_flag") = "Y" 
                        objRS.Update 

Now I have figured out that you can do if exists (select.....)begin <do update> end

But I need records returned to my ASP code as well. Is this possible?

[Abbas2891]

Hello,

I am having difficulty finding a way to convert this code into a stored
procedure on SQL Server...Here is the original code

    IF Request("updflag" & n) = "Y" then 
 
 
                cmdTemp.CommandText = " SELECT ast.asset_id, " & _ 
                                      " mast.meter_read_due_dt, mast.meter_sort, 
mast.update_flag " & _ 
                                      " From asset ast, meter_asset mast" & _ 
                                      " WHERE (ast.asset_id='" & Request("assetcode" 
& n) & "')" & _ 
                                      " AND   (ast.asset_id = mast.asset_id)" 
 
 
                cmdTemp.CommandType = adCmdText                                         'Text Command 
                Set cmdTemp.ActiveConnection = BEConn                           'Set database connection 
                objRS.Open cmdTemp, , adOpenKeyset, adLockOptimistic 
                If Not objRS.EOF and Not objRS.BOF then 
                        sAssetCD = objRS.Fields("asset_id").value 
                        dtMeterDate = objRS.Fields("meter_read_due_dt") 
                        objRS.Fields("meter_sort") = Trim(Request("newsort" & n)) 
                        objRs.Fields("update_flag") = "Y" 
                        objRS.Update 

Now I have figured out that you can do if exists (select.....)begin <do update> end

But I need records returned to my ASP code as well. Is this possible?

Solved...I think anyway!!

CREATE PROCEDURE dbo.p_update_asset
@asset_code varchar(20),
@newsort varchar(20),
@assetID varchar(20) OUTPUT,
@duedate datetime OUTPUT,
@update varchar(20) OUTPUT,
@sort varchar(20) OUTPUT
AS
 
DECLARE @rowcount int
DECLARE @ID varchar(20)
 
SELECT @assetID = ast.asset_id, 
       @duedate = mast.meter_read_due_dt, 
       @sort = mast.meter_sort, 
       @update = mast.update_flag 
FROM asset ast, meter_asset mast 
WHERE (ast.asset_id = @asset_code) 
       AND (ast.asset_id = mast.asset_id) 
 
SET @rowcount = @@rowcount
SET @ID = @assetID
 
If @rowcount <> 0
BEGIN
  UPDATE meter_asset
  SET meter_sort = @newsort,
  update_flag = 'Y'
  WHERE (@assetID = @asset_code)
     AND (@assetID = meter_asset.asset_id)
END
GO