Available options with current script > Table or View needed (SQL Server)

Question

So, I have the current script below, but I need to create this as a view and I know you can't use variable within it. I understand there is the option of creating a stored procedure, but I'm not quite sure how to go about.

Create View vwPUs__cwa as (

Declare @salt varchar (25);
DECLARE @Seed int;
DECLARE @LCV tinyint;
DECLARE @CTime DATETIME;

SET @CTime = GETDATE();
SET @Seed = (DATEPART(hh, @Ctime) * 10000000) + (DATEPART(n, @CTime) * 100000) 
  + (DATEPART(s, @CTime) * 1000) + DATEPART(ms, @CTime);
SET @LCV = 1;
SET @Salt = CHAR(ROUND((RAND(@Seed) * 94.0) + 32, 3));

WHILE (@LCV < 25)
BEGIN
    SET @Salt = @Salt + CHAR(ROUND((RAND() * 94.0) + 32, 3));
    SET @LCV = @LCV + 1;

END;

SELECT dc.id,
   sys.Fn_varbintohexsubstring(0, Hashbytes('SHA2_512', @salt + dc.decrypt),
   1, 0)
   AS SaltyHashbrowns,
   dc.firstname,
   dc.lastname,
   dc.statusname,
   dc.processingunit,
   dc.processingunitnumber
FROM   vwdecrypt_cwa dc
WHERE  dc.processingunitnumber IN ( 0201301, 0201302, 0201303, 0201308,
                                0201309, 0201311, 0201312 ) 


;

This is never going to work as a `VIEW`. A `VIEW` is a pseudo-table defined by a `SELECT`. It's definition can't contain multiple statements, only one, and it can't be parametrised. Perhaps you want a table-value function, but you'll need to ensure it's a inline table-value function, as multi-line table value functions are awfully slow (especially if that mlTVF has a `WHILE` loop in it, which are also terribly slow). — Thom A, Mar 15 '21 at 16:39
Also, the `FROM` in the above definition refers to an object called `vwdecrypt_cwa`. That isn't a `VIEW` as well is it? Nested Views very often perform poorly as well. If you must have a `VIEW` that uses the definition of another `VIEW`, repeat that other `VIEW`'s definition in the other. — Thom A, Mar 15 '21 at 16:40
What do you mean you "need" to create as a view. This could and should be a procedure, it cannot be a view - a view is only an abstraction / virtualised table. — Stu, Mar 15 '21 at 16:40
Sorry, guys, I should've noted that I was attempting to create a view, but after some research found that a procedure is the best way. — Ji Kim, Mar 15 '21 at 19:00

score 0 · Answer 1 · answered Mar 15 '21 at 16:44

The Prcoedure you need to create will be along the lines of

create procedure PUs__cwa
as
set nocount on

declare @salt varchar (25), @Seed int, @LCV tinyint=1, @CTime datetime=GetDate()

set @Seed = (DatePart(hh, @CTime) * 10000000) + (DatePart(n, @CTime) * 100000) 
  + (DatePart(s, @CTime) * 1000) + DatePart(ms, @CTime);

set @salt = Char(Round((Rand(@Seed) * 94.0) + 32, 3));

while (@LCV < 25)
begin
    set @salt = @salt + Char(Round((Rand() * 94.0) + 32, 3));
    set @LCV += 1;
end;

select dc.id,
   sys.fn_varbintohexsubstring(0, HashBytes('SHA2_512', @salt + dc.decrypt), 1, 0) as SaltyHashbrowns,
   dc.firstname,
   dc.lastname,
   dc.statusname,
   dc.processingunit,
   dc.processingunitnumber
from vwdecrypt_cwa dc
where dc.processingunitnumber in ( 0201301, 0201302, 0201303, 0201308, 0201309, 0201311, 0201312 )

And you run it in tsql with exec PUs__cwa

Charlieface · Answer 2 · 2021-03-16T17:48:54.443

0

An inline Table-Valued Function would work here. It is effectively treated by the compiler as a parameterized view.

You cannot place RAND() in a UDF, so we must do this in a stored procedure and pass it through:

CREATE OR ALTER FUNCTION vwPUs__cwa (@salt varchar (25))
RETURNS TABLE
AS RETURN
(

SELECT dc.id,
   sys.Fn_varbintohexsubstring(0, Hashbytes('SHA2_512', @salt + dc.decrypt),
   1, 0)
   AS SaltyHashbrowns,
   dc.firstname,
   dc.lastname,
   dc.statusname,
   dc.processingunit,
   dc.processingunitnumber
FROM   vwdecrypt_cwa dc
WHERE  dc.processingunitnumber IN ( 0201301, 0201302, 0201303, 0201308,
                                0201309, 0201311, 0201312 ) 
);
GO

CREATE OR ALTER PROCEDURE PUs__cwa
AS

SET NOCOUNT, XACT_ABORT ON;

DECLARE @salt varchar(25);
DECLARE @Seed int = (DATEPART(hh, @Ctime) * 10000000) + (DATEPART(n, @CTime) * 100000) + (DATEPART(s, @CTime) * 1000) + DATEPART(ms, @CTime);
DECLARE @LCV tinyint = 1;
DECLARE @CTime DATETIME = GETDATE();

WHILE (@LCV < 25)
BEGIN
    SET @LCV = @LCV + 1;
    SET @Salt = CONCAT(@Salt, CHAR(ROUND((RAND() * 94.0) + 32, 3)));
END;

SELECT *
FROM vwPUs__cwa (@salt);

GO

You could also run the salt code in a client application, and pass it through as a parameter to an ad-hoc batch:

SELECT *
FROM vwPUs__cwa (@salt);

edited Mar 16 '21 at 17:48

answered Mar 15 '21 at 16:55

Charlieface

52,284
6
19
43

Hey Charlie, when I run your script I get an error for the 'rand' function. How do I get around this? Msg 443, Level 16, State 1, Procedure GetSalt, Line 14 [Batch Start Line 89] Invalid use of a side-effecting operator 'rand' within a function. – Ji Kim Mar 16 '21 at 15:08
Hum, good point. [There are workarounds](https://stackoverflow.com/questions/31468836/use-rand-in-user-defined-function), but personally I would use `CHECKSUM(NEWID())` instead, bear in mind that returns an `int`. Or you could just run that part of the code in a stored procedure – Charlieface Mar 16 '21 at 15:45
When I run the Select * from vwPUs__cwa(GetSalt() ); I get... Msg 195, Level 15, State 10, Line 138 'GetSalt' is not a recognized built-in function name. – Ji Kim Mar 16 '21 at 17:32
Well yeah, if you can't define the function `GetSalt` because of the `RAND` problem then you can't call it. I will rewrite it as a stored procedure. OK done that now – Charlieface Mar 16 '21 at 17:45

Available options with current script > Table or View needed (SQL Server)

2 Answers2