Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    5

    Unanswered: Execute within single ticks

    Hi all -

    I have a script that im trying to write that includes taking the result of a select stmt, and storing it in a variable. When i execute this (within a trigger), it just returns the select as a string. What I really need it to do is put the results in the variable...


    Select @AppUser = 'SELECT TOP 1 UpdatedBy FROM '+ @TableName + ''

    Both variables are declared as varchar(128) the result is also a vachar (even though it's a number)

    Thanks all!

    I want to later call the @AppUser in another part of the code... Full code is here:

    USE [DB]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER trigger [dbo].[Table01_AuditTrigger] on [dbo].[Table01] for insert, update, delete
    as
    declare @bit int ,
    @field int ,
    @maxfield int ,
    @char int ,
    @fieldname varchar(128) ,
    @TableName varchar(128) ,
    @PKCols varchar(1000) ,
    @sql varchar(2000),
    @UpdateDate varchar(21) ,
    @UserName varchar(128) ,
    @Type char(1) ,
    @PKFieldSelect varchar(1000),
    @PKValueSelect varchar(1000),
    @AppUser varchar(128)

    -- pass the table name into the PK check variable
    select @TableName = 'Table01'

    -- date and user
    select @UserName = system_user ,
    @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

    -- Action
    if exists (select * from inserted)
    if exists (select * from deleted)
    select @Type = 'U'
    else
    select @Type = 'I'
    else
    select @Type = 'D'

    -- get list of columns
    select * into #ins from inserted
    select * into #del from deleted

    -- Get primary key columns for full outer join
    select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    where pk.TABLE_NAME = @TableName
    and CONSTRAINT_TYPE = 'PRIMARY KEY'
    and c.TABLE_NAME = pk.TABLE_NAME
    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    -- Get primary key fields select for insert
    select @PKFieldSelect = coalesce(@PKFieldSelect+'+','') + '''' + COLUMN_NAME + ''''
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    where pk.TABLE_NAME = @TableName
    and CONSTRAINT_TYPE = 'PRIMARY KEY'
    and c.TABLE_NAME = pk.TABLE_NAME
    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    select @PKValueSelect = coalesce(@PKValueSelect+'+','') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
    where pk.TABLE_NAME = @TableName
    and CONSTRAINT_TYPE = 'PRIMARY KEY'
    and c.TABLE_NAME = pk.TABLE_NAME
    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    -- make sure there is a key
    if @PKCols is null
    begin
    raiserror('no PK on table %s', 16, -1, @TableName)
    return
    end

    --This will return the top user in the list.
    Select @AppUser = 'SELECT TOP 1 UpdatedBy FROM '+ @TableName + ''

    --build the code to insert into audit table
    select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
    while @field < @maxfield
    begin
    select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
    select @bit = (@field - 1 )% 8 + 1
    select @bit = power(2,@bit - 1)
    select @char = ((@field - 1) / 8) + 1
    if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
    begin
    select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
    select @sql = 'insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName, AppUser)'
    select @sql = @sql + ' select ''' + @Type + ''''
    select @sql = @sql + ',''' + @TableName + ''''
    select @sql = @sql + ',' + @PKFieldSelect
    select @sql = @sql + ',' + @PKValueSelect
    select @sql = @sql + ',''' + @fieldname + ''''
    select @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
    select @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
    select @sql = @sql + ',''' + @UpdateDate + ''''
    select @sql = @sql + ',''' + @UserName + ''''
    select @sql = @sql + ',''' + @AppUser + ''''
    select @sql = @sql + ' from #ins i full outer join #del d'
    select @sql = @sql + @PKCols
    select @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
    select @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
    select @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
    --This is to keep the table clean...

    exec (@sql)
    delete from dbo.Audit where fieldname = 'UpdatedBy'
    delete from dbo.Audit where fieldname = 'UpdatedDate'
    end
    end

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Made an error
    Last edited by Wim; 12-15-11 at 13:20.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Dec 2011
    Posts
    5
    Hi Wim.... I tried that also.... (after adding a missing tick)

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by danimaltex View Post
    Hi Wim.... I tried that also.... (after adding a missing tick)
    Allow me to improve myself ...
    Code:
    declare @SQL nvarchar(500)
    declare @AppUser nvarchar(10)
    declare @TableName nvarchar(50)
    SET @TableName = 'DaTable'
    
    SET @SQL = N'SELECT TOP 1 @AppUser = LastUpdateBy FROM ' + @TableName
    PRINT @SQL
    exec sp_executesql @sql, N'@TableName NVARCHAR(50), @AppUser nvarchar(10) output', 
    	@TableName, @AppUser output
    PRINT @AppUser
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Dec 2011
    Posts
    5
    Thanks Wim...
    Changed the @SQL to @SQL2 (since i was already using it) and commented the 'print' statements..... that did the trick.
    Thank you sir.

Posting Permissions

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