asked on

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

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Tyler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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

Tyler

ASKER

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

Scott Pletcher

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.

Scott Pletcher

Good luck with future qs.