We help IT Professionals succeed at work.

Error when creating a table from a function

Tyler
Tyler asked
on
108 Views
Last Modified: 2018-04-19
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

Comment
Watch Question

SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
TylerSR. Data Systems Administrator
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You can't use EXEC dynamic SQL within a function.  That's prohibited because of side effect / state issues.
TylerSR. Data Systems Administrator

Author

Commented:
thanks for pointing out my slip-up  I found i could get the same from an Stored Procedure.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Good luck with future qs.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions