?
Solved

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

Posted on 2013-01-24
8
Medium Priority
?
461 Views
Last Modified: 2013-01-24
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
Comment
Question by:Lorna70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 160 total points
ID: 38813783
CONVERT(INT, @catId)
0
 
LVL 6

Expert Comment

by:deiaccord
ID: 38813785
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
 
LVL 6

Assisted Solution

by:deiaccord
deiaccord earned 120 total points
ID: 38813793
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38813826
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
 
LVL 6

Expert Comment

by:liija
ID: 38813838
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
 

Author Comment

by:Lorna70
ID: 38813860
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 720 total points
ID: 38813892
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
 

Author Comment

by:Lorna70
ID: 38814629
Great thanks - all working now :-)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question