Solved

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

Posted on 2013-01-24
8
458 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
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 30 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 180 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
SQL Group By Question 4 20
SQLCMD Syntax 2 15
Parse this column 6 27
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

831 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