Please Help with SQL Statement
-
Can someone take a look at this sql statement and find out what the heck is wrong with it? I can't seem to see anything wrong, but I get the error:
Msg 8114, Level 16, State 1, Procedure sp_autohunt, Line 0
Error converting data type nvarchar to int.
(1 row(s) affected)
Here is the whole procedure:
ALTER PROCEDURE [dbo].[sp_autohunt]
-- Add the parameters for the stored procedure here
@startrow int,
@maxrows int,
@state varchar(50),
@sortexpression varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @sql nvarchar(4000)
SET @sql=
'SELECT id, ViewDate, AutoID, Fullname, Person, Color, Price, State
FROM(SELECT ViewDate, AutoID, Fullname, Person, Color, Price, State,
ROW_NUMBER() OVER(ORDER BY' + @sortexpression + 'DESC)
as id FROM Automobiles a WHERE State=' + @state + ')
as AutoInventory WHERE id BETWEEN ' + cast(@startrow as nvarchar(1)) + '
AND (' + cast(@startrow as nvarchar(1)) + ' + ' + cast(@maxrows as nvarchar(2)) + ') - 1'
EXEC sp_autohunt @sql
END
-
Try changing
as AutoInventory WHERE id BETWEEN ' + cast(@startrow as nvarchar(1)) + '
AND (' + cast(@startrow as nvarchar(1)) + ' + ' + cast(@maxrows as nvarchar(2)) + ') - 1'
EXEC sp_autohunt @sql
to
as AutoInventory WHERE id BETWEEN ' + cast(@startrow as nvarchar(10)) + '
AND ' + cast(@startrow+@maxrows -1 as nvarchar(10))
EXEC (@sql)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Try this slightly modified version and show us what it prints when called:
ALTER PROCEDURE [dbo].[sp_autohunt]
-- Add the parameters for the stored procedure here
@startrow int,
@maxrows int,
@state varchar(50),
@sortexpression varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @sql nvarchar(4000)
SET @sql='SELECT id, ViewDate, AutoID, Fullname, Person, Color, Price, State
FROM(SELECT ViewDate, AutoID, Fullname, Person, Color, Price, State,
ROW_NUMBER() OVER(ORDER BY ' + @sortexpression + ' DESC) as id
FROM Automobiles a
WHERE State=' + @state + ') as AutoInventory
WHERE id BETWEEN ' + cast(@startrow as nvarchar(1)) + '
AND (' + cast(@startrow as nvarchar(1)) + ' + ' + cast(@maxrows as nvarchar(2)) + ') - 1'
PRINT @sql
EXEC sp_autohunt @sql
END
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Thanks for responding.
Here is what I get when I print the @sql
SELECT id, ViewDate, AutoID, Fullname, Person, Color, Price, State
FROM(SELECT ViewDate, AutoID, Fullname, Person, Color, Price, State,
ROW_NUMBER() OVER(ORDER BY price DESC) as id
FROM Automobiles a
WHERE State=md) as AutoInventory
WHERE id BETWEEN 0
AND (0 + 30) - 1
Msg 8114, Level 16, State 1, Procedure sp_autohunt, Line 0
Error converting data type nvarchar to int.
(1 row(s) affected)
I'm still getting the error.
-
Oooh rats! Why didn't I see this before?...
I think that you've got some unintended recursion here. The last line of your stored procedure "sp_autohunt" is this: "EXEC sp_autohunt @sql" which means that it is trying to call itself and passing in that big SQL string to the @startrow parameter--> thus your error.
Since that appears to be a SQL command that you are building, my guess is that you really wanted to execute it with sp_ExecuteSql, like so:
ALTER PROCEDURE [dbo].[sp_autohunt]
-- Add the parameters for the stored procedure here
@startrow int,
@maxrows int,
@state varchar(50),
@sortexpression varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @sql nvarchar(4000)
SET @sql='SELECT id, ViewDate, AutoID, Fullname, Person, Color, Price, State
FROM(SELECT ViewDate, AutoID, Fullname, Person, Color, Price, State,
ROW_NUMBER() OVER(ORDER BY ' + @sortexpression + ' DESC) as id
FROM Automobiles a
WHERE State=' + @state + ') as AutoInventory
WHERE id BETWEEN ' + cast(@startrow as nvarchar(1)) + '
AND (' + cast(@startrow as nvarchar(1)) + ' + ' + cast(@maxrows as nvarchar(2)) + ') - 1'
PRINT @sql
EXEC sp_ExecuteSql @sql
END
So try this instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
I figured it out after fiddling around with it. In the end, it was a simple solution, a one of sql grammar you might say. The problem was the issue with single quotes in a sql string. Because @state was a string parameter, its value needed to be surrounded by single quotes. However, single quotes is used in the query itself. So what to do? I just made them double quotes, and the results worked out perfectly. Thanks for your help though, I appreciate it.
The final procedure was this:
DECLARE @sql nvarchar(4000)
SET @sql='SELECT id, ViewDate, AutoID, Fullname, Person, Color, Price, State
FROM(SELECT ViewDate, AutoID, Fullname, Person, Color, Price, State,
ROW_NUMBER() OVER(ORDER BY ' + @sortexpression + ' DESC) as id
FROM Automobiles a
WHERE State=''' + @state + ''' ) as AutoInventory WHERE id BETWEEN ' + cast(@startrow as nvarchar(10)) + '
AND ' + cast(@startrow+@maxrows -1 as nvarchar(10))
Print @sql
EXEC (@sql)
-
Glad it worked out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply