Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-24
8
Medium Priority
?
463 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
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!

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

604 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