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)
 Populating SP parameters with table values

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_SAV_LSR_PrepareAA]

@AB_Id AS nvarchar(100),
@Priority AS nvarchar(100),
@A_Code AS nvarchar(100),
@Test_Sequence AS smallint

AS

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 1
EXEC dbo.usp_SAV_LSR_PrepareAA (SELECT AB_Id, Priority, A_Code, Line_Id FROM dbo.LSR_Prepare_Build)


Example 2
EXEC [dbo].[usp_SAV_LSR_PrepareAA] @AB_Id = ABAgri_Id, @Priority = Priority, @A_Code = Analysis_Code, @Test_Sequence = Line_Id
FROM 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 x

EXECUTE 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.
Go to Top of Page

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 ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[usp_SAV_LSR_Prepare_Call]

AS

DECLARE @A_Code nvarchar(100)
DECLARE @AB_Id nvarchar(100)
DECLARE @Id bigint
DECLARE @Priority nvarchar(100)
DECLARE @RowNum bigint
DECLARE @Test_Sequence smallint

DECLARE SQLUpdateSample CURSOR FOR
SELECT x.AB_Id, x.Priority, x.A_Code, x.Line_Id FROM dbo.LSR_Prepare_Build x
OPEN SQLUpdateSample
FETCH NEXT FROM SQLUpdateSample
INTO @Id
SET @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 16
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

(1 row(s) affected)

NOTE: No rows are inserted/updated

dbo.LSR_Prepare_Build Example:

AB_Id, Priority, A_Code, Line_Id
FC001, Amended, 0903, 1
FC001, Amended, 0903, 2
FC001, Amended, 0903, 3

Basically 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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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]
AS
BEGIN

DECLARE @rc int

UPDATE 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_Build

END


- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page
   

- Advertisement -