• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

How do I cast a string (varchar) parameter to Int before querying the DB?

The following returns an error 'Incorrect syntax near @catId' so I can only assume my syntax for my convert is wrong??:

[spListProductsByCatOrAll]
(
@catId varchar(3)
)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    declare @sql nvarchar(max)
   
    -- Insert statements for procedure here
      set @sql = 'SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By FROM Products'
    If @catId <> 0
       set @sql = @sql + ' WHERE Cat_ID = '+CONVERT(@catId AS INT)
      Else
         set @sql = @sql + ' WHERE Cat_ID <> 40 AND Cat_ID <> 41 AND Cat_ID <> 42'

    set @sql = @sql +' ORDER BY Sequence'
      Execute(@sql)

END
0
Lorna70
Asked:
Lorna70
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
Barry CunneyCommented:
CONVERT(INT, @catId)
0
 
deiaccordCommented:
You're nearly there but you need to use CAST not convert

e.g.

select cast ('1234' as int)

Open in new window


Just be wary it will fall over horribly if you varchar is not a number in any rows.
0
 
deiaccordCommented:
Just to add, CAST is an ANSI standard command, CONVERT is a sql server specific command but has additional date/datetime options that CAST does not.

Both are implemented using CONVERT internaly in SQL server so there is no performance difference. CAST is more portable between systems being an ANSI standard so is more likely to remain stable without being changed which is why I recommend it over CONVERT.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, inside your code, you DON'T need to cast to int, because you build a SQL string anyhow..

I would avoid dynamic sql altogether ...
[spListProductsByCatOrAll]
(
@catId int
)
AS
BEGIN
   SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By 
   FROM Products
   WHERE (  ( @catId  = 0  AND Cat_ID NOT IN ( 40 , 41 , 42 )  )
                OR ( @catid <> 0 AND Cat_ID = @catID )
                )  
   ORDER BY Sequence
END 

Open in new window

0
 
liijaCommented:
Well, there is also another error.
Your @Sql variable is varchar.
So you cannot write
SET @sql = @SQL + CAST(@CastID AS INT)

Instead you have to convert castid into char - you cannot combine chars with int.
SET @sql = @sql + CAST(@Castid AS nVARCHAR)

Edit: oops - the same was in previous tip by angelIII already... sorry
0
 
Lorna70Author Commented:
Thanks Angell - you helped me on this the last time but my problem is that the @catId parameter is coming from my ASP.Net program as a String, not an Int so that's why I've changed my code again!  I like your solution best (although will award points to the others) but could you please tell me how I convert or cast the @catId into an int in your solution.
Thanks, Lorna
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sure:

[spListProductsByCatOrAll]
(
@catId varchar(20)
)
AS
BEGIN
 DECLARE @pCatID int
  SET @pCatID = ISNULL(CAST(@catID as int), 0)

   SELECT Product_ID, Description, Cat_ID, Product_Title,Created_By 
   FROM Products
   WHERE (  ( @pCatID = 0  AND Cat_ID NOT IN ( 40 , 41 , 42 )  )
                OR ( @pCatid <> 0 AND Cat_ID = @pCatID )
                )  
   ORDER BY Sequence
END 

Open in new window

0
 
Lorna70Author Commented:
Great thanks - all working now :-)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now