Dynamic query SQL where condition

Question

I want to attach numeric value in dynamic query but I am an getting error:

Conversion failed when converting the varchar value ' + @psRegionCode + ' to data type smallint

My query is:

SET @psRegionCode = UPPER(LTRIM(RTRIM(@psRegionCode)))

IF (@psRegionCode <> 0)
BEGIN
    SET @sSQLStr = @sSQLStr + ' ' + 'AND reg.region_cd = ''' + @psRegionCode + ''''
END

Things which I tried:

SET @psRegionCode = UPPER(LTRIM(RTRIM(@psRegionCode)))
IF (@psRegionCode <> 0)
BEGIN
    SET @sSQLStr = @sSQLStr + ' ' +
        'AND reg.region_cd = ' + cast(@psRegionCode as nvarchar(10) ''
END

Can somone please help me with this?

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented May 25, 2016 at 6:39

Liya Tansky · Accepted Answer · 2016-05-25 07:08:25Z

2

 IF (@psRegionCode <> 0)
 BEGIN
     SET @sSQLStr = @sSQLStr + 
         ' AND reg.region_cd = ' + cast(@psRegionCode as nvarchar(10))

you need to make sure you have correct number of apostrophes
if @psRegionCode is number, why you ltrim it? if it is a string why you cast it?

edited May 25, 2016 at 7:08

answered May 25, 2016 at 6:46

Liya Tansky

2491 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mandes Over a year ago

it was string previously

Liya Tansky Over a year ago

So now you don't need the first statement, if it is not a string. Is the dynamic sql working?

Liya Tansky Over a year ago

perhaps you will edit your question with more information about @sSQLStr?

mandes Over a year ago

Dear Liya , when i print my Query it print like'' WHERE repStat.rep_state_flg = 'Y' AND reg.region_cd = ' + @psRegionCode + ' it shoud print WHERE repStat.rep_state_flg = 'Y' AND reg.region_cd = 3

Liya Tansky Over a year ago

why after psRegionCode you still have + ' ? here is an example (I skip variable sign @, becuase it doesn't let me put it here) declare psRegionCode smallint = 2 declare sSQLStr nvarchar(200) set sSQLStr = '' SET sSQLStr = sSQLStr + ' AND reg.region_cd = ' + cast(psRegionCode as nvarchar(10)) select sSQLStr It returns AND reg.region_cd = 2

sagi · Accepted Answer · 2016-05-25 07:06:39Z

2

Well, by your syntax I'm guessing SQL-Server? Any way I think your second query should work, you are just missing a parenthesis :

IF (@psRegionCode <> 0)
BEGIN
    SET @sSQLStr = @sSQLStr + 
        ' AND reg.region_cd = ' + cast(@psRegionCode as nvarchar(10)) 
END

You can't concat a number into a string without the conversion.

edited May 25, 2016 at 7:06

answered May 25, 2016 at 6:43

sagi

40.6k7 gold badges66 silver badges87 bronze badges

5 Comments

Liya Tansky Over a year ago

there is a problem with '

mandes Over a year ago

still facing same problem

sagi Over a year ago

What type is @psRegionCode ?

mandes Over a year ago

@sagi I remove that first line upper lrtim an rtrim but still getting same error

mandes Over a year ago

when i print my Query it print like'' WHERE repStat.rep_state_flg = 'Y' AND reg.region_cd = ' + @psRegionCode + ' it shoud print WHERE repStat.rep_state_flg = 'Y' AND reg.region_cd = 3

Collectives™ on Stack Overflow

Dynamic query SQL where condition

2 Answers 2

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related