CODEWITHSUNDEEP
sqlsql-server
mandes
mandes

Reputation: 105

Dynamic query SQL where condition

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?

Upvotes: 1

Views: 106

Answers (2)

Liya Tansky
Liya Tansky

Reputation: 249

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

  1. you need to make sure you have correct number of apostrophes

  2. if @psRegionCode is number, why you ltrim it? if it is a string why you cast it?

Upvotes: 2

sagi
sagi

Reputation: 40481

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.

Upvotes: 2

Related Questions