EXEC sp_executesql issue with my group clause

Question

Post reply

EXEC sp_executesql issue with my group clause

shannonjk

SSCarpal Tunnel

Points: 4286
More actions
December 30, 2009 at 1:08 pm

#136971

I have used EXEC sp_executesql in quite a few situations. I was building something simple today for testing, and I keep getting this error;
"Each GROUP BY expression must contain at least one column that is not an outer reference."
Here is a test code that generates this error, so clearly it is my composition of my SQL statement that is at fault :-D. What am I doing wrong?
CREATE TABLE #TEST (ID Bit)
INSERT INTO #TEST VALUES (1)
DECLARE @TestSQL nvarchar(Max), @sql nvarchar(Max), @Parameters nvarchar(Max)
SET @TestSQL = 'ID'
SET @sql = 'SELECT @_TestSQL FROM #TEST GROUP BY @_TestSQL'
SET @Parameters = N'@_TestSQL nvarchar(max)'
EXEC sp_ExecuteSQL @sql, @Parameters, @_TestSQL = @TestSQL
Link to my blog http://notyelf.com/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jcdyntek Ten Centuries Points: 1153 More actions · Answer 1

Try this

CREATE TABLE #TEST (ID Bit)

INSERT INTO #TEST VALUES (1)

DECLARE @TestSQL nvarchar(Max), @sql nvarchar(Max), @Parameters nvarchar(Max)

SET @TestSQL = 'ID'

SET @sql = 'SELECT '+@TestSQL+' FROM #TEST GROUP BY '+@TestSQL

--SET @Parameters = N'@_TestSQL nvarchar(max)'

EXEC sp_ExecuteSQL @sql --, @Parameters, @_TestSQL = @TestSQL

shannonjk SSCarpal Tunnel Points: 4286 More actions · Answer 2

Thank you however, this completely changes the use of having parameterized dynamic SQL...

Surely there is a solution that doesn't involve rearranging the usage of sp_executesql

Link to my blog http://notyelf.com/

jcdyntek Ten Centuries Points: 1153 More actions · Answer 3

I don not believe you can pass table or a column names as a parameters to sp_executesql in 2005, but you must interpolate it into the SQL string as I stated earlier.

you could also declare variables and such in the sql string, something like this

DECLARE @SQLString nvarchar(500);

SET @SQLString =

N'declare @Atable table (a int,b int);insert into @Atable values(1,2);select * from @atable';

EXECUTE sp_executesql @SQLString

jcdyntek Ten Centuries Points: 1153 More actions · Answer 4

Ok, I ran some tests in 2005 and confirmed that you can not pass fields or tables values in parameters for sp_executesql in 2005. If you try and pass a parameter value for the column, it will run, but will insert the literal value of the variable and output that value for every row. The order by, group by, and the table name do not work.

In 2008 you can use variables for the column names and it works, however, you still will not be able to do group by or order by with the variables, and passing values for table names still does not work.