December 19, 2019 at 9:20 am
Hi, I have a TVF that pulls column details from sys.tables so that it can produce some dynamic sql to generate a hashbyte.
This needs to be dynamic as it will be reused on multiple tables.
The result of the TVF is then used by a Scalar Function to create the hashbyte.
I am having used joining up the end result and could do with some assistance please.
The TVF is:
ALTER FUNCTION [DED].[TVF_TEST_MINUS_ONE] (
@Table VARCHAR(MAX)
)
RETURNS TABLE
AS
RETURN
SELECT
CONCAT(c.name,
' ',
t.name,
CASE WHEN t.name in ('int','Date','datetime') THEN ' ' ELSE '(' + CONVERT(varchar(20), c.max_length)+') ' END,
CASE WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL'end,
CASE WHEN i.is_primary_key = 1 THEN ' Primary Key' ELSE '' END,',') AS [String],
C.Name,
ISNULL(i.is_primary_key,0) AS is_primary_key,
CASE WHEN t.name = 'Date' THEN '''20991231''' ELSE CASE WHEN c.is_nullable = 0 THEN '-1' ELSE 'NULL'END end AS [Is_Nullable]
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID(@Table)
the Scalar function is:
[Code]
CREATE FUNCTION
DED.FN_Hashbytes(
@Table VARCHAR(MAX),
@Hashtype VARCHAR(20)
)
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @String varchar(MAX),
@Result VARBINARY(8000),
@sql VARCHAR(MAX);
set
@String =
(STUFF((
SELECT '+''|'' + ISNULL(CONVERT(NVARCHAR(100),' + '[' + name + ']),'''''+')'
FROM
ded.[TVF_TEST_MINUS_ONE](@Table)
FOR XML PATH('')
), 1, 1, ''));
SET @sql = '(select HASHBYTES(' + @Hashtype + ' , ' + @string+')'
EXEC(@sql)
SET @Result =
return @result
END
[/Code]
I need to use the result of the exec(@sql) line to assign to @Result.
Any ideas?
Cheers,
Dave
December 19, 2019 at 9:27 am
You could insert the result of the EXEC statement into a temp table and set the value of @result from there. What I recommend you do, though, is use sp_executesql instead of EXEC and assign an output parameter.
John
December 19, 2019 at 9:52 am
Hi John, thanks for the reply.
I have implemented it as follows:
but I am now getting this error when I call the function.
Will this go away if I got back to exec(@sql) use the temp table method?
Cheers,
Dave
alter FUNCTION
DED.FN_Hashbytes(
@Table VARCHAR(MAX),
@Hashtype VARCHAR(20)
)
RETURNS varbinary(8000)
AS
BEGIN
DECLARE @String varchar(MAX),
@sql VARCHAR(MAX),
@i INT,
@params nvarchar(1000),
@Result varbinary(8000);
set
@String =
(STUFF((
SELECT '+''|'' + ISNULL(CONVERT(NVARCHAR(100),' + '[' + name + ']),'''''+')'
FROM
ded.[TVF_TEST_MINUS_ONE](@Table)
FOR XML PATH('')
), 1, 1, ''));
SET @params = N'@Hash_Result VARBINARY(8000) OUTPUT';
SET @sql = '(select HASHBYTES(' + @Hashtype + ' , ' + @string+')'
EXEC sp_executesql @sql, @params, @HASH_Result = @Result OUTPUT;
return @Result
END
December 19, 2019 at 9:53 am
Msg 557, Level 16, State 2, Line 3
Only functions and some extended stored procedures can be executed from within a function.
December 19, 2019 at 10:53 am
Will this go away if I got back to exec(@sql) use the temp table method?
No, because, as the error message implies, you can't have dynamic SQL in a function. I overlooked that in my original reply. I think you'll need to use a stored procedure instead.
John
December 19, 2019 at 1:45 pm
Hi, I have a TVF that pulls column details from sys.tables so that it can produce some dynamic sql to generate a hashbyte.
This needs to be dynamic as it will be reused on multiple tables.
The result of the TVF is then used by a Scalar Function to create the hashbyte.
I am having used joining up the end result and could do with some assistance please.
If there are several possible tables, but you know the names of them in advance, can't you just code it non-dynamically with IF ELSE's on @TableName?
December 19, 2019 at 3:29 pm
If it's not Azure, i.e. you have access to the master db, create a proc in the master db and you can use it from any db and it will run in the context of that db. (It would have to be a proc, not a TVF, however.)
If you'd like to pursue that, let me know, and I can post code for it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 20, 2019 at 9:40 am
Hi all, thanks for the replies.
I am writing the SPROC to replace the function.
The below is where I am up to but I am getting a null result from SELECT(@SQL_FINAL)
any ideas? I think its to do with the output variable in the dynamic SQL (never used them before).
Cheers,
Dave
DECLARE
@Table VARCHAR(MAX) = 'ded.TABLE',
@Hash_type VARCHAR(20) = 'SHA1',
@Hash_SQL VARCHAR(MAX),
@SQL_FINAL VARCHAR(MAX),
@@temp_outer VARCHAR(MAX);
SET @Hash_SQL = 'DECLARE
@SQL varchar(max) =
( ''DECLARE @@temp_inner varchar(max); set @@temp_inner = (select convert(varchar(max),ded.fn_hashbytes(''''' + @Hash_type + ''''', ' + '''''' + @Table +''''')))
EXECUTE sp_executesql @SQL, N''''@@temp_inner varchar(max) OUTPUT'''', @@temp_inner = @@temp_outer OUTPUT'');'
--SELECT @SQL
EXEC (@Hash_SQL);
SET @SQL_FINAL = @@temp_outer
SELECT(@SQL_FINAL)
December 20, 2019 at 9:44 am
I should add that testing the dynamic SQL gives the correct result to @@temp_inner.
December 20, 2019 at 10:20 am
You need to build your SQL string dynamically, as you have done, and then pass it to sp_executesql, which should not be part of the dynamic SQL. Here's some pseudo-code:
DECLARE @SQL
DECLARE @Table = 'myTable'
DECLARE @Output
SET @SQL = 'SELECT @Output = MAX(OutputColumn) FROM ' + @Table
EXEC sp_executesql @SQL, '@Output int OUTPUT'
SELECT @Output
Also, if any of those varchar columns are parameters to your stored procedure, make sure you read and understand about SQL injection.
John
December 20, 2019 at 11:32 am
Hi John, thanks for the reminder about SQL injection. I think we are ok in this case.
I have the following which works perfectly.
Cheers for the help everyone.
Dave
DECLARE
@Table VARCHAR(MAX) = 'ded.TABLE',
@Hash_type VARCHAR(20) = 'SHA1',
@@temp_outer Nvarchar(max),
@SQL nvarchar(max);
SET @SQL =
( 'DECLARE @@temp_outer nvarchar(max);
set @@temp_inner = (select convert(nvarchar(max),ded.fn_hashbytes(''' + @Hash_type + ''', ' + '''' + @Table +''')))')
--SELECT @SQL
EXECUTE sp_executesql @SQL, N'@@temp_inner nvarchar(max) OUTPUT', @@temp_inner = @@temp_outer OUTPUT
--EXECUTE sp_executesql @@temp_mid
SELECT @@temp_outer
December 20, 2019 at 2:58 pm
Hi John, thanks for the reminder about SQL injection. I think we are ok in this case.
Despite your thoughts, you're still concatenating the table name without checking to see if it's really a table name, which means that 1) it is a possible SQL Injection point, 2) it will not pass a PEN test, and 3) this is why SQL Injection is still such a problem in the world. People think they'll be "ok in this case".
If this ever becomes a stored procedure, you've brought the possibility of SQL Injection into your house. It takes very little to verify that an actual table name has been passed in. Be safe... just do it.
I also see no reason for the hashbyte type to be variable in nature for this code but that should also be verified if it's going to ultimately be a parameter in a stored procedure.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply