-
Notifications
You must be signed in to change notification settings - Fork 159
Expand file tree
/
Copy pathsp.sql
More file actions
40 lines (36 loc) · 804 Bytes
/
Copy pathsp.sql
File metadata and controls
40 lines (36 loc) · 804 Bytes
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Routines to set up stored procedures
USE chatscript;
GO
IF OBJECT_ID('uspGetUserData', 'P') IS NOT NULL
DROP PROCEDURE uspGetUserData;
GO
CREATE PROCEDURE uspGetUserData (
@userid varchar(100)
)
AS
SELECT [file]
FROM userfiles
WHERE userid = @userid;
RETURN;
GO
IF OBJECT_ID('uspSetUserData', 'P') IS NOT NULL
DROP PROCEDURE uspSetUserData;
GO
CREATE PROCEDURE uspSetUserData (
@userid AS VARCHAR(100),
@userdata AS VARBINARY(max)
)
AS
BEGIN
MERGE INTO userfiles AS t
USING
(SELECT pk=@userid, f1= @userdata) AS s
ON t.userid = s.pk
WHEN MATCHED THEN
UPDATE SET userid=s.pk, [file]=s.f1
WHEN NOT MATCHED THEN
INSERT (userid, [file])
VALUES (s.pk, s.f1);
END;
RETURN
GO