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.
Author |
Topic |
Neal44
Starting Member
17 Posts |
Posted - 2010-06-20 : 06:49:11
|
Hi,I have a stored procedure that contains several parameters which I need to populate using values within a SQL table. I have tried various methods but can't get the correct syntax.Stored Procedure:USE [myDB]GO/****** Object: StoredProcedure [dbo].[usp_SAV_LSR_PrepareAA] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[usp_SAV_LSR_PrepareAA]@AB_Id AS nvarchar(100),@Priority AS nvarchar(100),@A_Code AS nvarchar(100),@Test_Sequence AS smallintAS BEGIN DECLARE @rc int UPDATE dbo.LSR_Prepare SET AB_Id = @AB_Id, Priority = @Priority, A_Code = @A_Code, Test_Sequence = @Test_Sequence WHERE AB_Id = @AB_Id AND A_Code = @A_Code AND Test_Sequence = @Test_Sequence /* how many rows were affected? */ SELECT @rc = @@ROWCOUNT IF @rc = 0 BEGIN INSERT INTO dbo.LSR_Prepare ( AB_Id, Priority, A_Code, Test_Sequence ) VALUES ( @AB_Id, @Priority, @A_Code, @Test_Sequence ) END END 2 methods I tried are:Example 1EXEC dbo.usp_SAV_LSR_PrepareAA (SELECT AB_Id, Priority, A_Code, Line_Id FROM dbo.LSR_Prepare_Build) Example 2EXEC [dbo].[usp_SAV_LSR_PrepareAA] @AB_Id = ABAgri_Id, @Priority = Priority, @A_Code = Analysis_Code, @Test_Sequence = Line_IdFROM dbo.LabSampleResults_Prepare_Build Any ideas please?Thanks in advance,Neal |
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-06-20 : 12:45:40
|
First, you need to declare the variables that you want to use - then you populate the variables, and finally you use the parameters to pass the values into the procedure:DECLARE @AB_Id nvarchar(100) ,@Priority nvarchar(100) ,@A_Code nvarchar(100) ,@Test_Sequence int;SELECT @AB_Id = x.AB_Id ,@Priority = x.Priority ,@A_Code = x.A_Code ,@Line_Id = x.Line_Id FROM dbo.LSR_Prepare_Build xEXECUTE dbo.usp_SAV_LSR_PrepareAA @AB_Id ,@Priority ,@A_Code ,@Test_Sequence;Now, this will work as long as you have a single row in the table dbo.LSR_Prepare_Build. I'm going to guess that this is not the case and you have multiple rows. I think you want to modify the way you are approaching this problem instead of trying to loop through the values.If you post the actual problem you are trying to solve, I think we can come up with a better performing set based update. |
|
|
Neal44
Starting Member
17 Posts |
Posted - 2010-06-20 : 17:04:26
|
Jeff W,Thanks for your help.I have a calling Stored Procedure which is:USE [MyDB]GO/****** Object: StoredProcedure [dbo].[usp_SAV_LSR_Prepare_Call] ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[usp_SAV_LSR_Prepare_Call]ASDECLARE @A_Code nvarchar(100)DECLARE @AB_Id nvarchar(100)DECLARE @Id bigintDECLARE @Priority nvarchar(100)DECLARE @RowNum bigintDECLARE @Test_Sequence smallintDECLARE SQLUpdateSample CURSOR FORSELECT x.AB_Id, x.Priority, x.A_Code, x.Line_Id FROM dbo.LSR_Prepare_Build xOPEN SQLUpdateSampleFETCH NEXT FROM SQLUpdateSample INTO @IdSET @RowNum = 0 WHILE @@FETCH_STATUS = 0 BEGIN SET @RowNum = @RowNum + 1 BEGIN SELECT @AB_Id = x.AB_Id, @Priority = x.Priority, @A_Code = x.A_Code, @Test_Sequence = x.Line_Id FROM dbo.LSR_Prepare_Build x WHERE x.Line_Id = @RowNum EXECUTE dbo.usp_SAV_LSR_PrepareAA @AB_Id, @Priority, @A_Code, @Test_Sequence END FETCH NEXT FROM SQLUpdateSample INTO @Id END CLOSE SQLUpdateSample DEALLOCATE SQLUpdateSample On executing the calling SP, the following error is produced:Msg 16924, Level 16, State 1, Procedure usp_SAV_LSR_Prepare_Call, Line 16Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.(1 row(s) affected) NOTE: No rows are inserted/updateddbo.LSR_Prepare_Build Example:AB_Id, Priority, A_Code, Line_IdFC001, Amended, 0903, 1FC001, Amended, 0903, 2FC001, Amended, 0903, 3Basically these 2 SP's are used within a SSIS package 'For-Each loop'. You are right there may be several rows from dbo.LSR_Prepare_Build which need to be INSERTED/UPDATED in dbo.LSR_Prepare. Obviously, this is dependant on whether the row is found or not. The Line_Id (Test_Sequence) is the line number from a xml source file.dbo.LSR_Prepare_Build has few rows (between 1 and 20) compared to dbo.LSR_Prepare (approx 1,000,000). dbo.LSR_Prepare_Build is loaded and unloaded within the loop (it is loaded each time from a xml file).Thanks in advance for any further assistance,Neal |
|
|
niechen861102
Starting Member
9 Posts |
Posted - 2010-06-20 : 21:40:03
|
this is dependant on whether the row is found or not.spam removed |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-06-21 : 00:04:21
|
A couple of things:1) You have declared your cursor to return 4 values, but you are only fetching one. Change your fetch statement to:FETCH NEXT FROM SQLUpdateSample INTO @AB_Id, @Priority, @A_Code, @Test_Sequence;2) With the above change, you no longer need to select the data from the table again - the variables will be set to the next row in the cursor.3) Add an order by to your cursor to insure you process the rows in the table in the order you want/need them to be processed.4) You probably don't need a cursor at all, but it is hard to tell without seeing the actual data that you are importing. |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-21 : 02:27:01
|
Aren't you overcomplicating things here? Why can't you just do everything in one go like the following example? No parameters, no cursor, everything set-based:ALTER PROCEDURE [dbo].[usp_SAV_LSR_PrepareAA]ASBEGINDECLARE @rc intUPDATE dbo.LSR_Prepare SET AB_Id = @AB_Id, Priority = @Priority, A_Code = @A_Code, Test_Sequence = @Test_Sequence FROM dbo.LSR_Prepare a INNER JOIN dbo.LSR_Prepare_Build b ON a.AB_Id = b.AB_Id AND a.A_Code = b.A_Code AND a.Test_Sequence = b.Test_Sequence SELECT @rc = @@ROWCOUNT IF @rc = 0 INSERT INTO dbo.LSR_Prepare ( AB_Id, Priority, A_Code, Test_Sequence ) SELECT AB_Id, Priority, A_Code, Line_Id FROM dbo.LSR_Prepare_BuildEND - LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
|