Lorna70
asked on
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
[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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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 ...
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 
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
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
ASKER
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
Thanks, Lorna
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great thanks - all working now :-)
e.g.
Open in new window
Just be wary it will fall over horribly if you varchar is not a number in any rows.