st.proc convert to sql script
-
I have a st.proc which basically checks through all rows ina tables to do some data manipulation . Since its only few thousand rows i am using cursor for this. Is it possible to get this sql run as a script rather than as a st.proc. no input parameters are used in the st.proc. Any suggestions/help on this will be greatly appreciated.
TIA
-
Can you post your current code (alter anything confidential please)
-
This is justa sample st.proc wherein i am using a cursor within a cursor. if i execute it thru st.proc it works fine. Is it something that can be executes as a sql script..Thanks
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Remove_Duplicate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Remove_Duplicate]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC Remove_Duplicate
as
DECLARE GetDuplicateSNO CURSOR FOR
SELECT distinct serial_no--, count(*)
FROM Table1 nolock
where serial_no in (281,282)
OPEN GetDuplicateSNO
DECLARE @total_count int
DECLARE @dup_serial_number int --for getting the duplicate serial_number
FETCH NEXT FROM GetDuplicateSNO INTO @dup_serial_number--, @total_count
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE GetDuplicateRow CURSOR FOR
SELECT serial_no,col1,col2,col3 from Table1 WHERE SERIAL_NO= @dup_serial_number
DECLARE @SERIAL_NO int
DECLARE @col1 int
DECLARE @col2 varchar(25)
DECLARE @col3 varchar(15)
DECLARE @OLD_SERIAL_NO int
OPEN GetDuplicateRow
FETCH NEXT FROM GetDuplicateRow INTO @SERIAL_NO,@col1,
@col2,@col3
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Table2 VALUES (@serial_no,
@col1,@col2,@col3
 
--Creates new Row with old existing dataDELETE Table1 WHERE SERIAL_NO = @dup_serial_number
FETCH NEXT FROM GetDuplicateRow INTO @SERIAL_NO,@col1,
@col2,@col3
END
CLOSE GetDuplicateRow
DEALLOCATE GetDuplicateRow
FETCH NEXT FROM GetDuplicateSNO INTO @dup_serial_number--, @total_count
END
CLOSE GetDuplicateSNO
DEALLOCATE GetDuplicateSNO
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Just curious, but what advantages are you trying to get by running this as a script over a stored procedure?
-
...and why are you using a cursor?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I agree why?
This has only two possibilities
SELECT distinct serial_no--, count(*)
FROM Table1 nolock
where serial_no in (281,282)
and that is the result will only be working on 281 and 282.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply