March 24, 2010 at 6:30 pm
Here is the code:
/* Create a user-defined table type
CREATE TYPE InsertIDs AS TABLE
( id INT NOT NULL, primary key (id) )
GO
CREATE TYPE ClearIDs AS TABLE
( id INT NOT NULL, primary key (id) )
GO
*/
IF OBJECT_ID ( 'dbo.usp_sc_customer_tx', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.usp_sc_customer_tx;
GO
CREATE PROCEDURE dbo.usp_sc_customer_tx @I IDs READONLY, @C IDs READONLY, @sscd nvarchar(50)
AS
DECLARE@today Date=CONVERT( date, GETDATE())
DECLARE @listatus_code_id Int,
@sch_key char(30),
@no_override_fl bit,
@rc int
EXECUTE @rc = [dbo].[usp_getSC] @sscd, @liStatus_code_id OUTPUT ,@sch_key OUTPUT ,@no_override_fl output
IF @liStatus_code_id IS NOT NULL
BEGIN
-- Insert any new status history records
insert status_code_history (company_id, status_code_id, status_code_dt,
customer_id, SavedRowVersion, sch_key_dup, last_updated_user_id)
select customer.company_id, @listatus_code_id, @today,
customer.customer_id,customer.RowVersion,@sch_key,-1
from customer
inner join status_code_history sch on customer.customer_id = sch.customer_id AND
(override_fl = 1 OR cleared_fl = 1 AND status_code_id = @listatus_code_id)
inner join @I on @I.id = customer.customer_id
-- clear any codes needing cleared
update status_code_history set cleared_by_user_id = -1, cleared_dt = @today, cleared_fl = 1
FROM customer
inner join status_code_history sch on customer.customer_id = sch.customer_id AND
(override_fl = 0 and cleared_fl = 0 AND status_code_id = @listatus_code_id)
inner join @C on @C.id = customer.customer_id
END
Here is the message:
Msg 137, Level 16, State 1, Procedure usp_sc_customer_tx, Line 22
Must declare the scalar variable "@I".
Msg 137, Level 16, State 1, Procedure usp_sc_customer_tx, Line 30
Must declare the scalar variable "@C".
I don't understand What I am doing wrong...
Thanks,
Mike
March 24, 2010 at 7:27 pm
CREATE PROCEDURE dbo.usp_sc_customer_tx
@I InsertIDs READONLY,
@C ClearIDs READONLY,
@sscd nvarchar(50)
You had "IDs" instead of the two different table types you created. Since your two types look to be identical, you could have gotten away with one in your example. But if you think their definitions might diverge in the future, its not a bad idea to start with two.
To pass a table-valued parameter, you have to create a TYPE for the table, and reference that type when declaring the parameters to your stored procedure. Basically the stored procedure requires a pre-existing definition of the structure of your table-variable, which is supplied by the TYPE object.
Here is the example from Books Online ( http://msdn.microsoft.com/en-us/library/bb510489.aspx).
Search for the topic: Table-Valued Parameters (Database Engine) for the complete writeup.
The TYPE in the example below is called "LocationTableType". You correctly added READONLY after your table variables in the CREATE STORED PROCEDURE. Notice in the code following the creation of the procedure, that you also reference the same TYPE to create a table variable that is then passed to the procedure.
Using table types can save you a lot of cut-and-paste when new columns are added, altered, or dropped from your table variable. They're not just mandatory, they're a good thing. Just remember that if you are passing a lot of rows that are going to go through complex processing, performance is probably going to be better if you use a #temporary table instead of a table-variable.
/* Create a table type. */
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE usp_InsertProductionLocation
@TVP LocationTableType READONLY
AS
SET NOCOUNT ON
INSERT INTO [AdventureWorks].[Production].[Location]
([Name]
,[CostRate]
,[Availability]
,[ModifiedDate])
SELECT *, 0, GETDATE()
FROM @TVP;
GO
/* Declare a variable that references the type. */
DECLARE @LocationTVP
AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
SELECT [Name], 0.00
FROM
[AdventureWorks].[Person].[StateProvince];
/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;
GO
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 24, 2010 at 9:49 pm
Sorry about that. Here is the definition I used:
/* Create a user-defined table type
CREATE TYPE IDs AS TABLE
( id INT NOT NULL, primary key (id) )
*/
I still get the error shown in the main message.
Mike
March 25, 2010 at 6:59 am
When all code is posted, and posted correctly, we have a far better chance of getting the answer right on the first try. Please repost everything.
/* Create a user-defined table type
CREATE TYPE IDs AS TABLE
( id INT NOT NULL, primary key (id) )
*/
The above code is commented. Have you actually run it and verified that a user-defined table type has been created in the database where you are trying to create the procedure?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply