April 24, 2006 at 2:08 pm
Hello, I have a somewhat of a simple stupid problem but i still cant do it.
I've tried for a couple of days but i cant get it right.
I need to create a loop that loops just as many times as there are records in a table, a count (*).
So i want to create a variable say @countvar that holds the amount of records. and then do a while loop
while (@i < @countvar)
But i cant get the last variable, @amount to actually contain the variable.
It becomes null.
here is the relevant code:
declare
@sql nvarchar(50)
declare
@output nvarchar(50)
DECLARE
@ParmDefinition NVARCHAR(500)
declare
@amount nvarchar(50)
declare
@i int
set
@sql = N'select count(*) from Medborgare as Medborgare'
SET
@ParmDefinition = N'@level tinyint,
@amount varchar(30) OUTPUT'
exec
sp_executesql
@sql,
@ParmDefinition
,
@level
= @i,
@amount
= @output OUTPUT
SELECT
@amount
April 24, 2006 at 2:36 pm
Something is missing where are you setting @Amount to a value,
second if your using a variable in the sp_Executesql you have to set it. Note where bolded setting @level to a value.
where are you setting @Amount.
Why do you need dynamic sql top set @level?
declare @sql nvarchar(50)
declare @output nvarchar(50)
DECLARE @ParmDefinition NVARCHAR(500)
declare @amount nvarchar(50)
declare @i int
set @sql = N'select @level = count(*) from Medborgare as Medborgare'
SET @ParmDefinition = N'@level tinyint OUTPUT,
@amount varchar(30) OUTPUT'
exec sp_executesql @sql,
@ParmDefinition,
@level = @i OUTPUT,
@amount = @output OUTPUT
SELECT @amount
April 24, 2006 at 4:57 pm
that did it. thanks a million.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply