Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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'
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
PRINT @sql
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
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
You can't use EXEC dynamic SQL within a function. That's prohibited because of side effect / state issues.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial