Post reply

Techniques for passing extra information into procedures and triggers

  • February 11, 2009 at 7:57 am

    #73457

    I think I read an article some time ago regarding an uncommon, but very useful technique for passing specific information into sql code, using some kind of attribute.

    As I recall, it was setting some attribute in the connection, that the following sql code could collect and act upon, like for example setting nocount on, and then evaluating that in a procedure (obviously I wouldn't use that case, but you get my meaning)

    For the life of me, I can't remember more details, but I think it could prove useful to the system I'm designing now, and would love to hear from anyone who has an idea what I'm talking about.

    Regards

    Martin

  • February 11, 2009 at 8:01 am

    #942168

    I think what you are talking about is CONTEXT_INFO;

    you can store some information in there, and it is specific to the connection; typical examples might be the username from my application, which is different than the SQL server login that was used to connect.

    here's a coding example:

    [font="Courier New"]

    DECLARE @var VARBINARY(128)

    SET @var = CAST(N'Hello World' AS VARBINARY(128))

    SET CONTEXT_INFO @var

    GO

    -- Select the context information

    DECLARE @sess VARBINARY(128), @var NVARCHAR(64)

    SET @sess = (SELECT context_info FROM MASTER.dbo.sysprocesses

    WHERE spid = @@spid)

    SET @var  = CAST(@sess AS NVARCHAR(64))

    PRINT @var

    SELECT CAST(context_info AS NVARCHAR(64)) AS RESULTS FROM MASTER.dbo.sysprocesses

    WHERE spid = @@spid

    DECLARE @var VARBINARY(128),

            @res NVARCHAR(64)

    SET @var = CAST(N'Hello World' AS VARBINARY(128))

    PRINT @var

    --results: 0x480065006C006C006F00200057006F0072006C006400

    SET @res = CAST(@var AS NVARCHAR(64))

    PRINT @res

    --results: Hello World

    --The same but using CONVERT:

    SET @var = CONVERT(VARBINARY(128), (N'Bananas and Oranges'))

    PRINT @var

    --results: 0x420061006E0061006E0061007300200061006E00640020004F00720061006E00670065007300

    SET @res = CONVERT(NVARCHAR(64),@var)

    PRINT @res

    --results: Bananas and Oranges

    [/font]

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 11, 2009 at 8:37 am

    #942213

    You nailed it, perfect, thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply