Solved

how to assign value of a query inside a variable.

Posted on 2004-10-12
9
519 Views
Last Modified: 2012-06-22
declare @check varchar(30)
******************----------------------------------------********************
set @check = execute('select'+ ' ' + 'mname'+ ' '   +'from table3' )
******************----------------------------------------********************
print '@check'

where my table name is table3 and mname is the FieldName. If i will not assign the result of execute query into a variable than the query is delievering the result quiet nicely but as this is the small part of my code and the code requires to store the result of this query into a variable like this only. So any help from database experts will be highly appreciated.
0
Comment
Question by:anugrah
  • 3
  • 3
9 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12285894
In SQL Server, like this:

DECLARE @check nvarchar(300), @sql nvarchar(1000)
SET @sql = 'SELECT Top 1 mname FROM Table3'
EXEC sp_executesql @sql, @check output
PRINT @check
0
 
LVL 1

Author Comment

by:anugrah
ID: 12286225
thanx dear it is working but now i am facing another problem in my actual code. As this code was just a part of trigger and the value which u are storing in @check, I want to use that as
If update('@check')
     { CALL "Employee"."dbo"."WorkingTrigger"(@check,@table_name1) }

but now i am facing a error in if update line as
Incorrect syntax near '@sel2'.
so can u please check it out or point me what exactly the error i am facing
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12287037
>If update('@check')
You should not use quote marks around variable names.

Also, it looks like you're trying to CALL a trigger, which doesn't work in any system I know of.

Which database are you using, and where/how are you trying to execute the last code you posted?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:anugrah
ID: 12294898
even if i am trying to use if update(@check), even then it is not working. See below the commented portion of trigger. Here is the code of that

--drop trigger tu_ECA
--delete auditlog
--update EmployeeCorrespondenceAddress set Address1='Toutti' where EmployeeID='Anuj'
create
trigger tu_ECA on EmployeeCorrespondenceAddress for update
as
      select * into myinserted from inserted
      ALTER TABLE myinserted ADD ChangeType VARCHAR(20) default 'update' with Values,ChangeDate varchar(20) default getdate() with Values,Table_name varchar(30) default 'EmployeeCorrespondenceAddress' with Values, UserName varchar(30) default user_Name() with Values
      
      select * into mydeleted from deleted
      
      declare @table_name1 varchar(30), @check nvarchar(50), @sql nvarchar(1000)
      set @table_name1 = 'EmployeeCorrespondenceAddress'
      
      declare mycolumns2 cursor for
       select column_name,data_type
       from information_schema.columns
      where
      table_name = @table_name1 and
      column_name <> 'IID' and
      column_name not like 'fk%'
      order by data_type
      declare @column_name1 varchar(100), @data_type1 char(30)
      open mycolumns2
      fetch next from mycolumns2 into @column_name1,@data_type1
      while @@fetch_status = 0
            begin

      set @sql  = 'select'+ '(i.'+@column_name1 + ')'+' '+ 'from myinserted as i inner join mydeleted as d on'+' '+ 'i.'+@column_name1+ '=' +'d.'+ @column_name1+ ' '+
           'where (i.'+ @column_name1 + '<> d.'+@column_name1+' '+ 'and not' + ' (i.'+@column_name1+' '+ 'is null and d.' + @column_name1+ ' '+ 'is null)'+')'      
      EXEC sp_executesql @sql, @check output
      --print '@check'
      
      
      --IF UPDATE(@check)
            --print '@check'
            --{ CALL "Employee"."dbo"."WorkingTrigger"(@check,@table_name1) }
            
            fetch next from mycolumns2 into @column_name1,@data_type1
      end
            close mycolumns2
            deallocate mycolumns2
      drop table myinserted
      drop table mydeleted
go



0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12302191
What do you want to do with the result of @check? And please confirm if you're using SQL Server.
0
 
LVL 1

Author Comment

by:anugrah
ID: 12305614
hi to all ,

let me make you the problemvery clear .

forget about all previously posted data.here  i give you fresh and simple code to know what exactly i am trying to do. read explaination below code as there is only one very small error in arrow marked line in code.

--------------------------------------------------------------------------------------------------------
create
trigger tu_ECA on EmployeeCorrespondenceAddress for update
as
      select * into myinserted
      from inserted
      ALTER TABLE myinserted ADD ChangeType VARCHAR(20) default 'update' with Values,ChangeDate varchar(20) default getdate() with Values,Table_name varchar(30) default 'EmployeeCorrespondenceAddress' with Values, UserName varchar(30) default user_Name() with Values
      
      select * into mydeleted
      from deleted
      
      declare @table_name1 varchar(30), @sel1 varchar(20), @sel2 varchar(20)
      set @table_name1 = 'EmployeeCorrespondenceAddress'
      
      declare mycolumns2 cursor for
       select column_name,data_type
       from information_schema.columns
      where
      table_name = @table_name1 and
      column_name <> 'IID' and
      column_name not like 'fk%'
      order by data_type
      declare @column_name1 varchar(100), @data_type1 char(30)
      open mycolumns2
      fetch next from mycolumns2 into @column_name1,@data_type1
      while @@fetch_status = 0
      begin

 ------------->         if updated(@column_name1)
                  {
                             CALL "Employee"."dbo"."AuditInserted"(@column_name1,@table_name1)
                            }
                 fetch next from mycolumns2 into @column_name1,@data_type1

      end
            close mycolumns2
            deallocate mycolumns2
      drop table myinserted
      drop table mydeleted
go
---------------------------------------------------------------------------------------------------------

as we all know that if update() takes input as column name .
but it accepts when i give exact value of column name .

as soon as i want to make it a little bit dynamic and pass a parameter (i:e variable @column_name1) insted of actual column name like employeeName or employeeId it gives syntax error.

i have made all possible attempts i:e using variable without quotes with quotes but no use .

hopes you have understood the problem .

eagerly waiting for your reply.

thanks in advance
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 50 total points
ID: 12316972
I'm afraid there is no direct solution to this problem. You cannot use a variable in the UPDATE syntax, since it is expecting an unquoted column name, not a string as its 'input'. The only solution would be to use dynamic SQL. THe problem with this is that the scope of the IF UPDATE syntax is such that it can only be used within a trigger, but the scope has changed when you call EXEC or sp_executeSQL, and so the IF UPDATE will fail.
The only solution is to use a manual check to see if inserted.col1 <> deleted.col1. This can be done in various forms using dynamic SQL - e.g.

declare @SQL varchar(1000)
set @SQL = '
if exists (select 1 from inserted i join deleted d on i.PrimaryKey = d.PrimaryKey
and i.' + @column_name1 + ' <> d.' + @column_name1 + ' )
exec  AuditInserted ' + @column_name1 + ',' + @table_name1
exec (@SQL)

alternatively, you could use dynamic SQL to return the value of @column_name1 to 2 variables from the inserted and deleted table, and then in standard SQL do a comparison of these.


0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now