stored procedure help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-12-08 : 11:04:02

Below is the sample code of stored procedure which has tp pull the data from a table based on the conditions after it should update the status for selected records(select query results).
Please suggest if there is better way to do the below procedure or anything needs to be added in procedure.
Thanks for your help in advance..

create procedure dbo.usp_sample
as

select col1,col2,col3,col4 from table
where status IN (1,1) and flag IN (1,2)
begin
update set status=3,flag=4 where status=1 and flag =1
end
begin
update set status=4,flag=5 where status=1 and flag =2
end

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-08 : 11:21:22

What is the purpose of the Select?

Just do this:


create proc usp_sample
as
Update [Table]
Set Status = (Case When status = 3 and flag = 1 then 3
                   When status = 4 and flag = 2 then 4 end)
    ,Flag = (Case When status = 3 and flag = 1 then 4
                   When status = 4 and flag = 2 then 5 end)

Poor planning on your part does not constitute an emergency on my part.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-12-08 : 12:10:44

Thanks for your immediate response!!

Select results will extracted to a flat file, which storedprocedured will be called in front end.
All the extracted records should be updated with status and flag..

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-12-08 : 12:52:15

DECLARE @Sample TABLE (TID INT, UserID INT,Status tinyint,Flag tinyint)

INSERT @Sample
SELECT 1111, 1,1,1 UNION ALL
SELECT 1112, 1,1,2 UNION ALL
SELECT 1113, 1,1,1 UNION ALL
SELECT 1114, 2,1,2 UNION ALL
SELECT 1115, 2,1,1 UNION ALL
SELECT 1116, 2,1,2 UNION ALL
SELECT 1117, 1,1,1 UNION ALL
SELECT 1118, 1,1,2 UNION ALL
SELECT 1119, 2,1,2 UNION ALL
SELECT 1119, 2,3,4 UNION ALL
SELECT 1119, 2,4,5

select * from @Sample
where status IN (1,1) and flag IN (1,2)

begin
update @Sample set status=3,flag=4 where status=1 and flag =1
end
begin
update @Sample set status=4,flag=5 where status=1 and flag =2
end

--select * from @sample

Please suggest if there is a better way to the write the below query..

I am using the select query in condition since i want to extract the selected records to a flat file based on the filter status and flag after the extract i want to update the status,flag for extracted records in the table..If select failed it should not update the status and flag.
Please let me know if am not clear...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-08 : 15:04:50

I am not sure the purpose of extracting the records first. If none of the records meet the conditions, they won't be updated.

You didn't even try to apply the update method I suggested. As far as a flat file, that would subsequently be updated you could just

do this in a query within SSIS or DTS without having to do the update (i.e., it sounds like you are not updating the actual records, only the extract)


 DECLARE @Sample TABLE (TID INT, UserID INT,Status tinyint,Flag tinyint)


INSERT @Sample
SELECT 1111, 1,1,1 UNION ALL
SELECT 1112, 1,1,2 UNION ALL
SELECT 1113, 1,1,1 UNION ALL
SELECT 1114, 2,1,2 UNION ALL
SELECT 1115, 2,1,1 UNION ALL
SELECT 1116, 2,1,2 UNION ALL
SELECT 1117, 1,1,1 UNION ALL
SELECT 1118, 1,1,2 UNION ALL
SELECT 1119, 2,1,2 UNION ALL
SELECT 1119, 2,3,4 UNION ALL
SELECT 1119, 2,4,5

select 	TID 
	,UserID
	,Status = Case When status = 1 and flag = 1 then 3
             		When status = 1 and flag = 2 then 4 end
    	,Flag = Case When status = 1 and flag = 1 then 4
                    When status = 1 and flag = 2 then 5 end
from @Sample
where status = 1 and Flag in (1,2)

You can change the values exported by using my syntax in a case statement for the desired results.
I noticed you have "status in (1,1) and " which would be better said status = 1

The above produces:


TID     USERID  STATUS   FLAG
1111	1	3	4
1112	1	4	5
1113	1	3	4
1114	2	4	5
1115	2	3	4
1116	2	4	5
1117	1	3	4
1118	1	4	5
1119	2	4	5

Poor planning on your part does not constitute an emergency on my part.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-12-08 : 21:39:56

Sorry Data guru if m not clear with my explaination...

All the extracted records should be updated with
status=3,flag=4 for status=1 and flag =1
and status=4,flag=5 for status=1 and flag =2



DECLARE @Sample TABLE (TID INT, UserID INT,Status tinyint,Flag tinyint)

INSERT @Sample
SELECT 1111, 1,1,1 UNION ALL
SELECT 1112, 1,1,2 UNION ALL
SELECT 1113, 1,1,1 UNION ALL
SELECT 1114, 2,1,2 UNION ALL
SELECT 1115, 2,1,1 UNION ALL
SELECT 1116, 2,1,2 UNION ALL
SELECT 1117, 1,1,1 UNION ALL
SELECT 1118, 1,1,2 UNION ALL
SELECT 1119, 2,1,2 UNION ALL
SELECT 1120, 2,3,4 UNION ALL
SELECT 1121, 2,4,5

Update @Sample
Set Status = (Case When status = 1 and flag = 1 then 3
                   When status = 1 and flag = 2 then 4 end)
    ,Flag = (Case When status = 1 and flag = 1 then 4
                   When status = 1 and flag = 2 then 5 end)

select * from @sample

I want the below result after the updates....

TID    UserID  Status  Flag
----   -----   ------  ----
1111	1	3	4
1112	1	4	5
1113	1	3	4
1114	2	4	5
1115	2	3	4
1116	2	4	5
1117	1	3	4
1118	1	4	5
1119	2	4	5
1120	2	3	4
1121	2	4	5

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-08 : 23:45:17

quote:
Originally posted by sqlfresher2k7

Sorry Data guru if m not clear with my explaination...

All the extracted records should be updated with
status=3,flag=4 for status=1 and flag =1
and status=4,flag=5 for status=1 and flag =2

I guess you weren't clear, or I misunderstood. Given your desired results, defaulting flag/status as the ELSE condition resolves the issue and will produce your specified results.


DECLARE @Sample TABLE (TID INT, UserID INT,Status tinyint,Flag tinyint)

INSERT @Sample
SELECT 1111, 1,1,1 UNION ALL
SELECT 1112, 1,1,2 UNION ALL
SELECT 1113, 1,1,1 UNION ALL
SELECT 1114, 2,1,2 UNION ALL
SELECT 1115, 2,1,1 UNION ALL
SELECT 1116, 2,1,2 UNION ALL
SELECT 1117, 1,1,1 UNION ALL
SELECT 1118, 1,1,2 UNION ALL
SELECT 1119, 2,1,2 UNION ALL
SELECT 1120, 2,3,4 UNION ALL
SELECT 1121, 2,4,5

Update @Sample
Set Status = (Case When status = 1 and flag = 1 then 3
                   When status = 1 and flag = 2 then 4 
			else status end)
    ,Flag = (Case When status = 1 and flag = 1 then 4
                   When status = 1 and flag = 2 then 5
		 else flag end)

select * from @sample

Produces:

Poor planning on your part does not constitute an emergency on my part.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-12-09 : 12:40:42

Thanks Dataguru !!
One more suggestion/help

What if i make a stored procedure which includes select as well as update.Since i want to pull the selected records and if the select is succesfull then i also want to update results of the select query in the sample table with the status changed (which means the records being extracted).So that next time it should not pull the extracted records..

Do you suggest to write separate stored procedure for select and updates.!! instead of one procedure like below..

What could be the potenial issue if i use the below stored procedure...

Create procedure dbo.usp_sample
as

DECLARE @intErrorCode INT

select * from sample
where status= 1 and flag IN (1,2)

Begin Tran

Update @Sample
Set Status = (Case When status = 1 and flag = 1 then 3
When status = 1 and flag = 2 then 4
else status end)
,Flag = (Case When status = 1 and flag = 1 then 4
When status = 1 and flag = 2 then 5
else flag end)
COMMIT TRAN

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Error occurred on Update!'
ROLLBACK TRAN
END

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-09 : 14:18:40

You set the update only. You don't need to first select records. If there are conditions which apply to the select (which would either return or NOT return records) those same conditions can apply to the update. If there are no records meeting the conditions, there would be no update.

I suggest you decide what you are really trying to do. Either you are selecting records which meet criteria, or you are updating records which meet criteria. I don't think you need two procedures, or even both of those things. If the final objective is to update the records, just have a procedure update the records. The update statement you have can be applied to the main table with no ill effect, as if there are no records with status = 1 and flag in (1,2), no records would be updated.

Until you decide what it is you need to do, it is hard for me to issue concerns. I just don't see a point in selecting the records first, since what you have asked to be done can be done with a simple update.

Poor planning on your part does not constitute an emergency on my part.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2007-12-09 : 22:06:07

Let me be clarify on the requirement, firstly i want a Stored Procedure to pull the records with the status 1,1 and flag 1,2 (in turn front end developers will call the stored procedure which they write the code to extract the records to a file).
At the sametime in the stored procedure, i want the extracted records of the file status,flag should be updated in the table (sample) with(3,4)(4,5).. so that next time when there is a extract run it should not be pulled the same records..

Still not clear??

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-10 : 10:18:51

Not really, but I have given you plenty of information for you to sort out your needs. Given that you need to both select the records, and update them....do it in one procedure so it goes like:

1) users/front end pull the data.
2) Records are updated (thus they won't be pulled next time step 1 is run)

Poor planning on your part does not constitute an emergency on my part.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2008-04-03 : 15:07:33

Yes you are correct...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources