CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
Baba Fooka
Baba Fooka

Reputation: 47

Exec in a function SQL

I have a table where i am storing multiple values like below,

ColumnA ColumnB ColumnC
Book1 Author1 Station1
Book2 Author2 Station1
Book3 Author3 Station3

Now i wanted to have all books grouped by a particular station in a single row like below

Book1 Book2 Book3 Station
Author1 Author2 NULL Station1
NULL NULL Station2 Station2

Now i have achieved this using following XML approach

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SET @cols = STUFF(
             (
                 SELECT DISTINCT
                        ','+QUOTENAME(books.ColumnA)
                 FROM [dbo].[Books] books FOR XML PATH(''), TYPE
             ).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query = 'SELECT [ColumnC], '+@cols+'from (SELECT [ColumnC],
       [ColumnB] AS [Author],
       [ColumnA] AS [BookCols]
FROM [dbo].[Books]
)x pivot (max(Author) for BookCols in ('+@cols+')) p';

I am facing challenge to use this in any function or View as i have another complex query and I have to use this query within that query to have a single dataset. What is the best approach to use above in a select statement.

Upvotes: 0

Views: 3750

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7928

The syntax you are using to create your query (@query) is called Dynamic SQL (AKA dSQL). You cannot use DSQL in a function, you need to use a stored procedure.

Here's a simple table valued function; I'm taking a number(@N) and adding 1 to it via Dynamic SQL. The correct syntax would be:

CREATE FUNCTION dbo.fnPlusOne(@N INT)
RETURNS TABLE AS RETURN

  EXEC(CONCAT('SELECT 1+',@N));
GO

This returns this error:

Msg 156, Level 15, State 1, Procedure fnPlusOne, Line 4 [Batch Start Line 2]
Incorrect syntax near the keyword 'EXEC'.

With a function you need to define what columns or atomic values, including the data type for each. When you use Dynamic SQL there is no way for SQL Server to know what to return because, with DSQL, you can return anything.

A stored procedure, on the other hand, can run DSQL. Here's the same logic as a stored proc:

CREATE PROC dbo.PlusOne @N INT
AS
  DECLARE @query NVARCHAR(MAX) = CONCAT('SELECT 1+',@N);
  
  EXEC(@query);
GO

You would execute like so:

EXEC dbo.PlusOne 10;

Your proc would look something like this (I can't test this though):

CREATE PROC dbo.yourproc AS
BEGIN
  DECLARE @cols VARCHAR(MAX), @query NVARCHAR(MAX);

    SET @cols = STUFF(
                 (
                     SELECT DISTINCT
                            ','+QUOTENAME(books.ColumnA)
                     FROM [dbo].[Books] books FOR XML PATH(''), TYPE
                 ).value('.', 'nvarchar(max)'), 1, 1, '');
    SET @query = 'SELECT [ColumnC], '+@cols+'from (SELECT [ColumnC],
           [ColumnB] AS [Author],
           [ColumnA] AS [BookCols]
    FROM [dbo].[Books]
    )x pivot (max(Author) for BookCols in ('+@cols+')) p';
END
GO

Upvotes: 0

Related Questions