sp_executesql driving me mad! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_executesql driving me mad!

Author

Topic

DorsetBloke
Starting Member

3 Posts

Posted - 2011-07-15 : 06:44:05

Hi,

I cannot find an explanation of this anywhere that doesn't assume you have been working in SQL for years, and probably know it anyway. I'm going around in circles. Every forum or blog mentions parameters but none of them explain what they do or what they're for. They seem to include the most complex example possible that confuses even more.

I have some dynamic SQL in a stored procedure to page through results 10 at a time. I have the SQL ready, but I cannot for the life of me work out how I execute the SQL statement within the TOP 10 SELECT where I've marked it below.

Someone please help!

Many thanks.

--------

CREATE PROCEDURE SearchProperties

@PageNumber int,
@Country VARCHAR(100),
@Region VARCHAR(100)
@SortColumn VARCHAR(20)
AS

DECLARE @SQL AS VARCHAR(4000)

SELECT @SQL = 'SELECT RowID=ROW_NUMBER() OVER (ORDER BY '
IF @SortColumn = 'Price Descending'
SELECT @SQL = @SQL + 'Euros DESC),'
IF @SortColumn = 'Price Ascending'
SELECT @SQL = @SQL + 'Euros ASC),'
IF @SortColumn = 'Latest'
SELECT @SQL = @SQL + 'Date DESC),'
IF @SortColumn = 'Most Popular'
SELECT @SQL = @SQL + 'viewed DESC),'
IF @SortColumn = 'Bedrooms'
SELECT @SQL = @SQL + 'Bedrooms DESC),'
IF @SortColumn is NULL
SELECT @SQL = @SQL + 'Euros DESC),'

SELECT @SQL = @SQL + 'Count(*) OVER() As TotalRecords,Country,Region,TotalRows=Count(*) OVER()'

SELECT @SQL = @SQL + ' FROM Properties WHERE DeleteMarker <> Yes '
IF @Country is not null
SELECT @SQL = @SQL + ' AND Country = ' + @Country
IF @Region is not null
SELECT @SQL = @SQL + ' AND Region = ' + @Region

BEGIN

Select Top(10) * FROM
(
>>> IN HERE <<<
) A
WHERE A.RowId > ((@PageNumber-1)*10)

END

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-07-15 : 07:27:21

DECLARE @TMP TABLE (RowId INT)
DECLARE @PageNumber INT
SET @PageNumber=2
DECLARE @SQL AS NVARCHAR(4000)

SET @SQL='SELECT NUMBER FROM MASTER..SPT_VALUES WHERE NUMBER<20 AND TYPE=''P'''

INSERT INTO @TMP
EXECUTE SP_EXECUTESQL @SQL

Select Top(10) * FROM
@TMP
WHERE RowId > ((@PageNumber-1)*10)

--------------------
Rock n Roll with SQL

DorsetBloke
Starting Member

3 Posts

Posted - 2011-07-15 : 07:36:22

Isn't that using a temporary table?

I'd read that the best and most efficient way to do paging with a stored procedure was with the ROWNUM command of SQL2005 and that temporary tables weren't a great way to do it.

Any idea why I'm getting an incorrect syntax error in SQL Manager 2005 if I put:

EXECUTE SP_EXECUTESQL @SQL

Where I marked >>> IN HERE <<< in my code?

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-07-15 : 07:47:47

You cannot select straight from sp_executesql, that's what you are trying to do. First you need to capture the result of sp_executesql, this is what the temporary table is doing.

--------------------
Rock n Roll with SQL

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-07-15 : 07:53:46

Can you try this:

SET @PageNumber=(@PageNumber-1)*10
SELECT @SQL = 'SELECT TOP(10) * FROM ('
SELECT @SQL = @SQL + 'SELECT RowID=ROW_NUMBER() OVER (ORDER BY '
SELECT @SQL = @SQL + .....your remaining select query
SELECT @SQL = @SQL + ')TAB '
SELECT @SQL = @SQL + 'WHERE TAB.RowId > ' + CAST(@PageNumber AS VARCHAR)

EXECUTE SP_EXECUTESQL @SQL

--------------------
Rock n Roll with SQL

DorsetBloke
Starting Member

3 Posts

Posted - 2011-07-18 : 05:47:01

I print the SQL out to see it, thus:

SELECT TOP(10) * FROM (SELECT RowID=ROW_NUMBER() OVER (ORDER BY Euros DESC),Country,Region,Euros FROM Properties WHERE Country='France')TAB WHERE TAB.RowId > 20

The programme tells me:

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-18 : 05:52:45

declare your @SQL as nvarchar


declare @SQL nvarchar(max)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources