Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    41

    Post Unanswered: Variable Column Names

    Hi. I've got the following SQL statement in which I would like to execute a select statement in which the column names are pulled from a variable. The SQL statement is:

    SET @tempSQL = 'SET @tempAddress = (SELECT '+@cftColumnName+' FROM inserted)'
    EXECUTE(@tempSQL)

    The error I am getting states that I must declare the variable @tempAddress, which I find strange because I have done that at the beginning of my procedure.

    What I would like to achieve is to set the variable @tempAddress to the result of the SELECT statement which uses @cftColumnName as a variable column name. Is there possibly a different way to write this expression?

  2. #2
    Join Date
    Feb 2003
    Posts
    41
    I think I might be able to make that slightly more clear.

    How can I assign the result of my dynamic SQL statement to the variable @tempAddress? I can't seem to put a EXEC(...) statement on the right hand side of an = operator, like:

    SET @tempAddress = EXEC(@tempSQL)

    AND, I can't seem to build the 'SET @tempAddress' into the dynamic SQL statement or else I get an error telling me to declare the variable.

    Is there some way to execulte the dynamic SQL statement and assign it's result to a variable?

  3. #3
    Join Date
    Feb 2003
    Posts
    41
    Even a bit more info to work with. Every little bit helps....

    This works:

    SELECT mpEmail1 FROM INSERTED

    So does:

    SELECT mpEmail2 FROM INSERTED

    the variable @cftColumnName is populated as part of a CURSOR/FETCH statement and recieves the values mpEmail1 and mpEmail2.

    What I need to happen is to replace the mpEmail... part of the above select statement with the @cftColumnName variable and still retrieve the same result. I'm then passing this off to a COM object via sp_OACreate.

    Big thanks in Advance =)

    Here's my entire trigger which I sort of omitted to prevent confusion, but I'll include it just in case there is something wrong I mige be missing.


    BEGIN TRIGGER
    -------------------------------------------

    CREATE TRIGGER trgAddSubscriber ON [dbo].[tblMasterPeople]
    FOR INSERT
    AS

    -- Declare Variables
    DECLARE @object int, @desc varchar(255), @src varchar(255)
    DECLARE @retValue int, @retValue2 int, @retValue3 int, @retValue4 int
    DECLARE @conSubId varchar(10), @nonSubId int, @tempAddress varchar(75), @tempSQL nvarchar(255)
    DECLARE @cftName varchar(50), @cftType varchar(50), @cftDelivery varchar(50), @cftColumnName varchar(50)

    -- Create an instance of the ERMS COM object
    exec @retValue = master..sp_OACreate 'ermsNSwrapper.Subscriber', @object OUTPUT
    IF @retValue <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@retValue), Source=@src, Description=@desc
    RETURN
    END

    -- Set a variable to hold the letter 'p' concatenated with the PK id from the newly inserted record
    SET @nonSubId = (SELECT mpId FROM inserted)
    SET @conSubId = 'p' + CAST(@nonSubId AS varchar(9))

    -- Call the AddSubscriber function passing the @conSubId value as the subscriberID
    exec sp_OAMethod @object, 'AddSubscriber', @retValue2 OUTPUT, @subId = @conSubId
    IF @retValue2 <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@retValue2), Source=@src, Description=@desc
    END

    -- here we load all the possible subscriber device types. Loop through each to see if there was a value entered.
    -- If there was we add the subscriber device. Pass the AddDevice function the address along with arguments defining the device type
    DECLARE devicesCursor CURSOR FOR
    SELECT cftType, cftName, cftDelivery, cftColumnName FROM tblContactFullText WHERE cftNotificationDevice = 1


    OPEN devicesCursor
    FETCH NEXT FROM devicesCursor
    INTO @cftType, @cftName, @cftDelivery, @cftColumnName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @tempSQL = 'SELECT '+@cftColumnName+' FROM INSERTED)'
    SET @tempAddress = ********************* RESULT OF THE EXECUTED @tempSQL STATEMENT NEEDS TO BE ASSIGNED TO THIS VARIABLE

    exec sp_OAMethod @object, 'AddDevice', @retValue3 OUTPUT, @myDeviceType=@cftType, @myDeviceName=@cftName, @subId=@conSubId, @delivery=@cftDelivery, @myAddress=@tempAddress
    IF @retValue3 <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@retValue3), Source=@src, Description=@desc
    END
    FETCH NEXT FROM devicesCursor
    INTO @cftType, @cftName, @cftDelivery, @cftColumnName
    END

    CLOSE devicesCursor
    DEALLOCATE devicesCursor

    --next we add a subscription to the users own subscriberID. This is used when notifications are targeted to just one individual
    exec sp_OAMethod @object, 'AddSubscription', @retValue4 OUTPUT, @myDeviceName='myEmailDevice', @subId=@conSubId, @alertSymbol=@conSubId, @locale='en-us'
    PRINT @retValue4
    IF @retValue4 <> 0
    BEGIN
    EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
    SELECT hr=convert(varbinary(4),@retValue4), Source=@src, Description=@desc
    END

    --Destroy the instance of the COM object created by the trigger.
    exec sp_OADestroy @object
    Last edited by mt404; 03-06-03 at 19:10.

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: Variable Column Names

    Originally posted by mt404
    Hi. I've got the following SQL statement in which I would like to execute a select statement in which the column names are pulled from a variable. The SQL statement is:

    SET @tempSQL = 'SET @tempAddress = (SELECT '+@cftColumnName+' FROM inserted)'
    EXECUTE(@tempSQL)

    The error I am getting states that I must declare the variable @tempAddress, which I find strange because I have done that at the beginning of my procedure.

    What I would like to achieve is to set the variable @tempAddress to the result of the SELECT statement which uses @cftColumnName as a variable column name. Is there possibly a different way to write this expression?
    have a look at "sp_execueSQL" (bol).

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    have a look at "sp_execueSQL" (bol).

    Effectively saying

    Build up your SQL Statement using the variables

    ie

    SET @SQLString = "SELECT * FROM " + @TableName

    and then Use ExecuteSQL to Run it

    EXECUTE sp_executesql @SQLString

    GW

  6. #6
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Re: have a look at "sp_execueSQL" (bol).

    Originally posted by GWilliy
    Effectively saying

    Build up your SQL Statement using the variables

    ie

    SET @SQLString = "SELECT * FROM " + @TableName

    and then Use ExecuteSQL to Run it

    EXECUTE sp_executesql @SQLString

    GW
    not really, what i mean was something like
    -----------------------------------------------------------------------
    declare @tempSQL Nvarchar(1000), @Result varchar(1000)

    SET @tempSQL = 'SET @Dummy = (SELECT top 1 name FROM sysobjects order by name )'

    exec sp_executeSQL @TempSQL, N'@Dummy varchar(1000) output', @Dummy=@result output

    print @Result
    -----------------------------------------------------------------------
    (use of sysobject just as an example)

    markus

  7. #7
    Join Date
    Feb 2003
    Posts
    41
    Markus, Sorry for the lengthy delay in getting back to you. I'd like to say thanks, your suggestion really helped me, and I was able to modify it for use within my trigger.

    Thanks again =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •