上次问过类似的问题。学习到很多。但我上次对问题没有理解清楚。
这次先说一下问题:
1.要在数据库端实现一个类似于session的东西。我将一个数据存储起来,在一个用户会话期间,要想调用这个数据,直接取出就行。就是例如数据库中的SET CONTEXT_INFO { binary_str | @binary_var }机制。但其存储大小有限制!所以要想办法实现一个类似的机制。就如web中的session一样,一次set,在会话期间多次get.
2.要在函数中使用。
3.过去说的那个表变量,只是在每次还要调用函数,如下:
ALTER function [dbo].[SelectUser](@v_name nvarchar(50))
returns @t_param table(name nvarchar(50))
as
begin
insert into @t_param (name) values(@v_name)
return
end
select * from student s,SelectUser('admin') b where s.name=b.name
本人现在没分了,只有这点。请大家不要介意。我很急的,工作中的问题。
请高手指点。
下面是context_info的使用,大家参考:
SET CONTEXT_INFO { binary_str | @binary_var } Arguments
binary_str
Is a binary constant, or a constant that is implicitly convertible to binary, to associate with the current session or connection.
@ binary_var
Is a varbinary or binary variable holding a context value to associate with the current session or connection.
Remarks
The preferred way to retrieve the context information for the current session is to use the CONTEXT_INFO function. Session context information is also stored in the context_info columns in the following system views:
sys.dm_exec_requests
sys.dm_exec_sessions
sys.sysprocesses
SET CONTEXT_INFO cannot be specified in a user-defined function. You cannot supply a null value to SET CONTEXT_INFO because the views holding the values do not allow for null values.
SET CONTEXT_INFO does not accept expressions other than constants or variable names. To set the context information to the result of a function call, you must first include the result of the function call in a binary or varbinary variable.
When you issue SET CONTEXT_INFO in a stored procedure or trigger, unlike in other SET statements, the new value set for the context information persists after the stored procedure or trigger is completed.
Examples
A. Setting context information by using a constant
The following example demonstrates SET CONTEXT_INFO by setting the value and displaying the results. Note that querying sys.dm_exec_sessions requires SELECT and VIEW SERVER STATE permissions, whereas using the CONTEXT_INFO function does not.
Copy Code
SET CONTEXT_INFO 0x01010101
GO
SELECT context_info
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GOB. Setting context information by using a function
The following example demonstrates using the output of a function to set the context value, where the value from the function must be first placed in a binary variable.
Copy Code
DECLARE @BinVar varbinary(128)
SET @BinVar = CAST(REPLICATE( 0x20, 128 ) AS varbinary(128) )
SET CONTEXT_INFO @BinVar
SELECT CONTEXT_INFO() AS MyContextInfo;
GO