Error when creating a table from a function

I am creating a function to run on a table to return a recordset with Hashbyte coulmns. getting an error

the error is

Msg 102, Level 15, State 1, Procedure fn_AssignRowMD5SHA1, Line 35 [Batch Start Line 15]
Incorrect syntax near '@SQL'

CREATE FUNCTION fn_AssignRowMD5SHA1
(
	-- Add the parameters for the function here
	@TableName sysname
	, @SchemaName sysname
	,@PrimaryKeyName sysname
	)
	RETURNS @query TABLE (
            md5rowdata VARCHAR(max),
	        sha1rowdata VARCHAR(max),
	        ID bigint	
            )
AS

BEGIN
DECLARE @datacolumns AS varchar(max)
DECLARE @SQL AS varchar(max)
SET @datacolumns = (SELECT Stuff(
        (
        Select ',  ' + C.COLUMN_NAME
        From INFORMATION_SCHEMA.COLUMNS As C
        Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
            And C.TABLE_NAME = T.TABLE_NAME
        Order By C.ORDINAL_POSITION
        For Xml Path('')
        ), 1, 2, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
WHERE T.TABLE_NAME=@TableName AND T.TABLE_SCHEMA=@SchemaName)

@SQL = 'SELECT HASHBYTES(''MD5'',CONCAT(' + @datacolumns + ')) md5rowdata
            ,HASHBYTES(''SHA1'',CONCAT(' + @datacolumns + ')) sha1rowdata 
            ,' + @PrimaryKeyName + ' FROM ' + @SchemaName + '.' + @TableName 

INSERT @query
EXEC @SQL
RETURN

END
GO

Open in new window

ok the example was missing a set @sql but still returned

Msg 443, Level 16, State 14, Procedure fn_AssignRowMD5SHA1, Line 35 [Batch Start Line 0]
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

I can do this with in a stored procedure and it would work for what i want so I guess a stored procdure is what i get

creater procedure spAssignRowMD5SHA1 
	-- Add the parameters for the stored procedure here
	@TableName sysname
	, @SchemaName sysname
	,@PrimaryKeyName sysname
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
DECLARE @datacolumns AS varchar(max)

SET @datacolumns = (SELECT Stuff(
        (
        Select ',  ' + C.COLUMN_NAME
        From INFORMATION_SCHEMA.COLUMNS As C
        Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
            And C.TABLE_NAME = T.TABLE_NAME
        Order By C.ORDINAL_POSITION
        For Xml Path('')
        ), 1, 2, '') As Columns
From INFORMATION_SCHEMA.TABLES As T
WHERE T.TABLE_NAME=@TableName AND T.TABLE_SCHEMA=@SchemaName)

EXEC('SELECT HASHBYTES(''MD5'',CONCAT(' + @datacolumns + ')) md5rowdata
            ,HASHBYTES(''SHA1'',CONCAT(' + @datacolumns + ')) sha1rowdata 
            ,' + @PrimaryKeyName + ',getdate() ds  FROM ' + @SchemaName + '.' + @TableName )
return
END

GO

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 67

Jim HornSQL Server Data DudeCommented: 2017-03-16

Just for kicks and giggles, before the INSERT @query EXEC @sql add this line, rerun, and copy-paste into this question what is generated.

PRINT @sql

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

TylerSR. Data Systems AdministratorAuthor Commented: 2017-03-16

LVL 73

Scott PletcherSenior DBACommented: 2017-03-16

You can't use EXEC dynamic SQL within a function. That's prohibited because of side effect / state issues.

thanks for pointing out my slip-up I found i could get the same from an Stored Procedure.

Or from the function even. I don't think you really need dynamic SQL, since I believe you can use a variable in the HASHBYTES function:

SELECT HASHBYTES('MD5', @datacolumns)

I don't see any strings actually being CONCATenated, so I'm not 100% clear on what you're trying to do.

Good luck with future qs.

Error when creating a table from a function

Who is Participating?