troubleshooting Question

Error when creating a table from a function

Avatar of Tyler
TylerFlag for United States of America asked on
Microsoft SQL Server
6 Comments2 Solutions111 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros