Solved

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

Posted on 2013-01-24
8
451 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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 40 total points
Comment Utility
CONVERT(INT, @catId)
0
 
LVL 6

Expert Comment

by:deiaccord
Comment Utility
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 30 total points
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 6

Expert Comment

by:liija
Comment Utility
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
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 180 total points
Comment Utility
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
Comment Utility
Great thanks - all working now :-)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

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 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…
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now