how to concatenate in sql server

[cmrhema]

Hello,
Now I want to concatenate the pin numbers which I have underlined in the code.
But somehow it does not.
What's the correct way
Kindly help
Regards
cmrhema

 
declare @CrdPinNo varchar(100)
declare @CrdNo nvarchar(100)
declare @count int
declare @inc int
declare @concat nvarchar(200)
declare @Result nvarchar(50)
declare @desc nvarchar(500)
 
 
select @count=count(cardno) from vendorcardvalidation where cardvalue=1500 and flag=0
print @count
if (@count)>2
begin
 
    declare cur1 cursor for
select top(2) pin,cardno from vendorcardvalidation where cardvalue=1500 and flag=0 order by cardno
 
    open cur1
 
    fetch next from cur1 into @CrdPinNo,@CrdNo
 
    while(@@fetch_status=0)
    begin
 
 
update vendorcardvalidation set flag=1 where cardno=@CrdNo
 select @concat='@concat'+@CrdPinNo
                            print @concat
 
 
 
    fetch next from cur1 into @CrdPinNo,@CrdNo
    end
    close cur1
    deallocate cur1    
            set @desc=@concat
            set @Result='Success'
print @desc
end
else
begin
    set @desc='Inadequate numbers'
    set @Result='Failure'
end

[ck9663]

Hello,
Now I want to concatenate the pin numbers which I have underlined in the code.
But somehow it does not.
What's the correct way
Kindly help
Regards
cmrhema

 
declare @CrdPinNo varchar(100)
declare @CrdNo nvarchar(100)
declare @count int
declare @inc int
declare @concat nvarchar(200)
declare @Result nvarchar(50)
declare @desc nvarchar(500)
 
 
select @count=count(cardno) from vendorcardvalidation where cardvalue=1500 and flag=0
print @count
if (@count)>2
begin
 
    declare cur1 cursor for
select top(2) pin,cardno from vendorcardvalidation where cardvalue=1500 and flag=0 order by cardno
 
    open cur1
 
    fetch next from cur1 into @CrdPinNo,@CrdNo
 
    while(@@fetch_status=0)
    begin
 
 
update vendorcardvalidation set flag=1 where cardno=@CrdNo
 select @concat='@concat'+@CrdPinNo
                            print @concat
 
 
 
    fetch next from cur1 into @CrdPinNo,@CrdNo
    end
    close cur1
    deallocate cur1    
            set @desc=@concat
            set @Result='Success'
print @desc
end
else
begin
    set @desc='Inadequate numbers'
    set @Result='Failure'
end

try

select @concat='@concat'+ cast(@CrdPinNo as varchar)

[cmrhema]

try

select @concat='@concat'+ cast(@CrdPinNo as varchar)

Thanks
But this returns only the last pin not both the values

[amitpatel66]

Dont use dingle quotes because you are refering to value of the variable and not to the had coded value '@concat'

Try:

SELECT @concat = @concat + @pincode
print @concat

[sayedul]

Try this (should work):

 
select @concat = isnull(@concat,'') + @CrdPinNo
 
 

[cmrhema]

Try this (should work):

 
select @concat = isnull(@concat,'') + @CrdPinNo
 
 

Thanks all three of you
Infact solved it in a different way
Now the procedure looks very different

ALTER procedure [dbo].[VTELcardvalidation1] 
@CardValue1 int,
@CardValue2 int,
@CardValue3 int,
@Result nvarchar(50)output
 
as
begin
 
declare @CrdPinNo varchar(100)
declare @CrdNo nvarchar(100)
declare @count int
declare @inc int
declare @concat nvarchar(200)
 
create table #temptable(crno nvarchar(50),pno nvarchar(50))
 
 
--First Card
 
--Change here
select @count=count(cardno) from vendorcardvalidation where cardvalue=1500 and flag=0
 
if (@count)>@CardValue1
    begin
declare cur1 cursor for
select top(@CardValue1) pin,cardno from vendorcardvalidation where cardvalue=1500 and flag=0 order by cardno
open cur1
fetch next from cur1 into @CrdPinNo,@CrdNo
while(@@fetch_status=0)
begin
update vendorcardvalidation set flag=1 where cardno=@CrdNo
insert into #temptable values(1500,@CrdPinNo)
fetch next from cur1 into @CrdPinNo,@CrdNo
end
close cur1
deallocate cur1    
set @Result='Success'
end
else
begin
set @Result='Failure'
end
select * from #temptable
drop table #temptable 
end

I was supposed to fetch records from database and if the cardvalues(denomination of 500,1000 etc) exists update the flag to 1.
This means that the card has been sold out.
I created a temporary table, stored all the values in it and dropped it later.

Actually what ever i return will in turn will be stored in datatable(asp.net) and later on processed.

thanks again all.
regards
cmrhema