Trying to use SELECT TOP @Sample_Size within an INSERT INTO block

[naxy]

UPDATE manual_audits
SET manual_audit_system = ( case
when transaction_number in ('10', '14', '9292') then 'G'
when transaction_number in ('401', '202', '8258', '8282', '8217', '6060', '6061') then 'M'
end)
WHERE claims_id = cast(@mclaims_id as nvarchar(10)

 

[naxy]

Sorry, I can't find an edit button. I didn't realize that my Windows Clipboard didn't pick up my text. Here's what my post really SHOULD read:

Hi all,

I'm not really sure what to call my problem, so I will try to be as descriptive as possible. I'm trying to insert records to a new table using partial data from another table. I have a query and cursor before hand to create the set of available records, but then I need to further sort it and only insert an amount of records determined by the user (@Sample_Size). For this, I'm using SELECT TOP @Sample_Size. This is where the problem lies. No matter what I try, all I get is 'Incorrect syntax near 'cast'.' The SQL I'm using is below. Do note that I manually added the DECLARES and SETS since these are normally done via the Stored Procedure. The INSERT INTO (specifically the SELECT TOP block) is the code in question. Am i missing an operator or anything? If I replace the casting with a static number (1, 5, 13, etc), it works just fine.

Thanks all!

DECLARE @DCN char(18),
	@Audit_Type_Code char(1),
	@MClaims_ID int,
	@Sample_Size int

set @DCN = 123
set @Audit_Type_Code = 'S'
set @MClaims_ID = 7529467
set @Sample_Size = 1

INSERT INTO manual_audits
	(date_processed, state_id, claims_id, 
	operator_id, cost_center_number, 
	group_number, member_number, 
	section_number, transaction_number, 
	dcn, audit_type_code, 
	claim_accurate_flag, claim_adjustment_flag, 
	auditor_number, date_audited, 
	claim_form, employee_rebuttal_flag, 
	final_disposition_code, claim_deleted_flag, 
	claim_status_flag, pend_status_flag, 
	last_update_user, last_update_time, 
	manual_audit_system, group_name, 
	FYI_only, Error_Type_Code,
	Error_Condition_Code, Error_Code, 
	Source_Code, Mode_Of_Receipt_Code, 
	Operator_Job_Category, Operator_Supervisor_Group, 
	Operator_Supervisor_ID, Supervisor_Number, Tran_Type_Code)
	SELECT TOP cast(@Sample_Size as nvarchar(2)) Date_processed, state_id, mclaims_id, 
			operator_id, cost_center_nbr, group_nbr, subscriber_nbr, section_nbr, transaction_nbr, 
			@DCN, @Audit_Type_Code, ' ', ' ', '79557', convert(char(10), getdate(),101), '1', 'N', 
			'N', 'N', 'A', -1, 'SYS', convert(char(10), getdate(),101), ' ', ' ', ' ', ' ',
			' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' '
	FROM membership_transactions
	WHERE mclaims_id = cast(@mclaims_id as nvarchar(10))

 

[mrdenny]

Which do you need help with?

Your post doens't match your subject.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vongrunt]

At least in SQL2000, TOP @variable is not allowed. This is changed in SQL2005 though.

Alternative: do SET ROWCOUNT @variable before and SET ROWCOUNT 0 after INSERT statement.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mrdenny]

In that case (I was writing my post while you were reposting yours):

Vongrunt would be correct.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com