i am trying to run a stored procedure and there is a problem in this particular code


DECLARE @MidVchar char(10), @DOBdate smalldatetime, @PreId int

Select @MidVchar = datename(yy, getdate()) + 'FRE'
Select @PreId = max(MemberCodeint) from LoginRegFree

Select @MidVchar = @MidVchar & @PreId

and the error message is
Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the varchar value '2006FRE ' to a column of data type int.


Here MemberCodeint is type int (as identity)
and @MidVchar is Varchar

thanks in advance

ashok sharma

First the "&" is a bitwise operator. If you are trying to concatenate strings you use "+"
Second you are trying to concatenate a char var (@MidVhar) with an integer (@PreID)
you need to convert the interger var
Something like
Select @MidVchar = @MidVchar + convert(varchar(10),@PreId)

Jim

i have already tried this, the query is running but result is unexpedted

as per query the result must be 2006FRE1 ----- PreId = 1
but the result is 2006FRE ----- PreId is not concanated

and there is no error

ashok

what do you mean there is no error, your post says there is an error..

What are you trying to do?

Hi,

i mean to say the changes you told to make, i changed. and after that there is no error but the result is unexpected.

i changed my code to
DECLARE @MidVchar char(10), @DOBdate smalldatetime, @PreId int

Select @MidVchar = datename(yy, getdate()) + 'FRE'
Select @PreId = max(MemberCodeint) from LoginRegFree
Select @MidVchar = @MidVchar + convert(varchar(10),@PreId)


as per query the result must be 2006FRE1 ----- PreId = 1
but the result is 2006FRE ----- PreId is not concanated



ashok

Hi,

You need to change @MidVchar from char(10) to varchar(10) or varchar(what-ever-number-you-need).

Gert

thanks gert

Probem Solved

can you tell me now how can i add preceeding zeros. for example when i run my sp the result is 2006FRE1 BUT i want it like
2006FRE00001

so how to add preceeding zeros in my @PreId (the length of this field is 5 character means till 99999)


thanks
ashok

Hi again.

Maybe something like this?

Select @MidVchar = @MidVchar + RIGHT('00000'+convert(varchar(10),@PreId),5)

Gert

Nice one Gert!

hi Gert

great solution

thanks
ashok