Dynamic Select Statement

please advice me with the below SQL Script
say,I have a cursor to Insert a records in the table if the below condition matches
samay
I have different tables
1. EMPLOYEE
ID Salary TableName
================================== EMP1 25000 Nametable1
EMP3 15000 Nametable2
EMP19 15000 Nametable3
EMP5 10000 Nametable4
2.
Nametable1
ID Name
============================ EMP1 Samay
EMP2 Rahul
3.
Nametable2
ID Name
============================ EMP19 Samay
EMP29 Rahul
Now,
1. I want to get the TableName(@.TableName) from EMPLOYEE where salary = 15000
2. Select a Name from (@.TableName) table
where EMPLOYEE.ID = @.TableName.ID
Declare @.Name
Declare @.TableName
Declare @.ID
Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
Set @.Name = (Select Name from @.TableName where ID = @.ID)
Now if I execute my script which has a cursor it give me an Error
Must declare the variable '@.TableName '. for Last line script
PLease advice me with this1) you need to declare your vbls
2) Dynamic SQL means that you build a string (E.g., @.SQL below) which when all put together is a valid SQL Statement. Then you execute it via exec (@.SQL).
Declare @.SQL varchar(2000)
Declare @.TableName varchar(100)
Declare @.ID int
Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
exec (@.SQL)
--
Tea C.
"KritiVerma@.hotmail.com" wrote:
> please advice me with the below SQL Script
> say,I have a cursor to Insert a records in the table if the below condition matches
> samay
> I have different tables
> 1. EMPLOYEE
> ID Salary TableName
> ==================================> EMP1 25000 Nametable1
> EMP3 15000 Nametable2
> EMP19 15000 Nametable3
> EMP5 10000 Nametable4
> 2.
> Nametable1
> ID Name
> ============================> EMP1 Samay
> EMP2 Rahul
> 3.
> Nametable2
> ID Name
> ============================> EMP19 Samay
> EMP29 Rahul
> Now,
> 1. I want to get the TableName(@.TableName) from EMPLOYEE where salary = 15000
> 2. Select a Name from (@.TableName) table
> where EMPLOYEE.ID = @.TableName.ID
>
> Declare @.Name
> Declare @.TableName
> Declare @.ID
> Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> Set @.Name = (Select Name from @.TableName where ID = @.ID)
> Now if I execute my script which has a cursor it give me an Error
> Must declare the variable '@.TableName '. for Last line script
> PLease advice me with this
>|||Thanks for your Query
I am having a SQl satement as
If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
Drop table ##TEMPDelete
SET @.Name = (Select replace(@.Name,' ','_'))
Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+ @.Name + ' where eid = '+ @.eid
Exec (@.SQL)
Set @. Name = (select @.Name from ##TEMPDelete)
Print AddressTemp
this results back as an error
Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
please advice
"Tea C" wrote:
> Sorry - that was not clear from your original inquiry.
> I can think of 3 way to accomplish this:
> 1) Use a temporary tbl
> if Object_ID('tempdb.dbo.##tmp') is not null
> drop table ##tmp
> Set @.SQL = 'Select top 1 Name as test into ##tmp from ' + @.TableName + ' where ID =' + Str(@.ID)
> exec (@.SQL)
> set @.Name = (select test from ##tmp)
> print @.Name
> 2) Combine your SELECT and INSERT into a single "dynamic" SQL, by doing INSERT after your SELECT.
> Set @.SQL = ' declare @.name varchar(100) Select top 1 Name from ' + @.TableName + ' where ID =' + Str(@.ID) ' + ' -- here goes your insert statement or a call to SP which does the insert'
> 3) Or just combine INSERT and SELECT into a single query
> Set @.SQL = 'insert into NameTable(Name) from Select top 1 Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> exec (@.sql)
> Hopefully, one of these 3 ideas meets your needs...
> Tea C.
>
> "KritiVerma@.hotmail.com" wrote:
> > Thanks for ur reply But I want to store the value into a Variable which i am using to insert a record into an another table
> >
> > i.e for
> > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > exec (@.SQL)
> >
> > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > @.Name = exec (@.SQL) -- or somthing like this
> >
> > Please advice
> >
> > Thanks again
> >
> >
> >
> > "Tea C" wrote:
> >
> > > 1) you need to declare your vbls
> > > 2) Dynamic SQL means that you build a string (E.g., @.SQL below) which when all put together is a valid SQL Statement. Then you execute it via exec (@.SQL).
> > >
> > > Declare @.SQL varchar(2000)
> > > Declare @.TableName varchar(100)
> > > Declare @.ID int
> > >
> > > Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> > > set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> > > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > exec (@.SQL)
> > >
> > > --
> > > Tea C.
> > >
> > >
> > > "KritiVerma@.hotmail.com" wrote:
> > >
> > > > please advice me with the below SQL Script
> > > >
> > > > say,I have a cursor to Insert a records in the table if the below condition matches
> > > > samay
> > > > I have different tables
> > > > 1. EMPLOYEE
> > > > ID Salary TableName
> > > > ==================================> > > > EMP1 25000 Nametable1
> > > > EMP3 15000 Nametable2
> > > > EMP19 15000 Nametable3
> > > > EMP5 10000 Nametable4
> > > >
> > > > 2.
> > > > Nametable1
> > > > ID Name
> > > > ============================> > > > EMP1 Samay
> > > > EMP2 Rahul
> > > > 3.
> > > > Nametable2
> > > > ID Name
> > > > ============================> > > > EMP19 Samay
> > > > EMP29 Rahul
> > > >
> > > > Now,
> > > > 1. I want to get the TableName(@.TableName) from EMPLOYEE where salary = 15000
> > > > 2. Select a Name from (@.TableName) table
> > > > where EMPLOYEE.ID = @.TableName.ID
> > > >
> > > >
> > > > Declare @.Name
> > > > Declare @.TableName
> > > > Declare @.ID
> > > >
> > > > Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> > > >
> > > > set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> > > >
> > > > Set @.Name = (Select Name from @.TableName where ID = @.ID)
> > > >
> > > > Now if I execute my script which has a cursor it give me an Error
> > > >
> > > > Must declare the variable '@.TableName '. for Last line script
> > > >
> > > > PLease advice me with this
> > > >|||If your eID exists as a column in the Address table, then you would change your @.SQL to:
SET @.SQL = 'Select top 1 eID into ##TEMPDelete ......'
After executing the above @.SQL, you will end up with a tmp table ##TempDelete, which will have a single column, named eID. From here you treat your temp table, just like any other table.
e.g.,
Select * from ##tempDelete
then change your last SET statement to:
Set @. Name = (select eID from ##TEMPDelete)
Thanks,
--
Tea C.
"Tea C" wrote:
> Change your last SET statement to:
> Set @. Name = (select AddressTemp from ##TEMPDelete)
> and make sure AddressTemp returns whatever you need to retrun when you insert it into the ##tempDelete. If you need a value from a different field (or fields), you will change your @.SQL to include these additional fields (E.g., select top 1 Address, City, AddressID into ##tempDelete ...etc.). You can then retrieve each of these fields into your vbls after you execute your dynamic SQL (@.SQL).
> declare @.addressID int
> declare @.address varchar(50)
> declare @.city varchar(50)
> Select @.addressID = AddressID, @.Address=Address, @.city=City from ##tempDelete
> -- Just for fun, print
> Print @.AddressID
> -- or you can do (as you are already doing)
> SET @.addressID = (Select AddressID from ##tempDelete)
> SET @.Address = (Select Address from ##TempDelete)
> SET @.City = (Select City from ##tempDelete)
> -- Just for fun print
> print @.address
> Tea C.
>
> "KritiVerma@.hotmail.com" wrote:
> > Thanks for your Query
> >
> > I am having a SQl satement as
> > If(Object_ID('DBName.Dbo.##TEMPDelete')) is not null
> > Drop table ##TEMPDelete
> >
> > SET @.Name = (Select replace(@.Name,' ','_'))
> >
> > Set @.SQL = 'Select top 1 Address as AddressTemp into ##TEMPDelete from '+ @.Name + ' where eid = '+ @.eid
> > Exec (@.SQL)
> >
> > Set @. Name = (select @.Name from ##TEMPDelete)
> > Print AddressTemp
> >
> >
> > this results back as an error
> > Invalid Column name EMP123 (the Value of @.eid instead of the column eId)
> >
> >
> > please advice
> >
> > "Tea C" wrote:
> >
> > > Sorry - that was not clear from your original inquiry.
> > >
> > > I can think of 3 way to accomplish this:
> > >
> > > 1) Use a temporary tbl
> > >
> > > if Object_ID('tempdb.dbo.##tmp') is not null
> > > drop table ##tmp
> > >
> > > Set @.SQL = 'Select top 1 Name as test into ##tmp from ' + @.TableName + ' where ID =' + Str(@.ID)
> > >
> > > exec (@.SQL)
> > > set @.Name = (select test from ##tmp)
> > > print @.Name
> > >
> > > 2) Combine your SELECT and INSERT into a single "dynamic" SQL, by doing INSERT after your SELECT.
> > >
> > > Set @.SQL = ' declare @.name varchar(100) Select top 1 Name from ' + @.TableName + ' where ID =' + Str(@.ID) ' + ' -- here goes your insert statement or a call to SP which does the insert'
> > >
> > > 3) Or just combine INSERT and SELECT into a single query
> > > Set @.SQL = 'insert into NameTable(Name) from Select top 1 Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > exec (@.sql)
> > >
> > > Hopefully, one of these 3 ideas meets your needs...
> > >
> > > Tea C.
> > >
> > >
> > > "KritiVerma@.hotmail.com" wrote:
> > >
> > > > Thanks for ur reply But I want to store the value into a Variable which i am using to insert a record into an another table
> > > >
> > > > i.e for
> > > > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > > exec (@.SQL)
> > > >
> > > > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > > @.Name = exec (@.SQL) -- or somthing like this
> > > >
> > > > Please advice
> > > >
> > > > Thanks again
> > > >
> > > >
> > > >
> > > > "Tea C" wrote:
> > > >
> > > > > 1) you need to declare your vbls
> > > > > 2) Dynamic SQL means that you build a string (E.g., @.SQL below) which when all put together is a valid SQL Statement. Then you execute it via exec (@.SQL).
> > > > >
> > > > > Declare @.SQL varchar(2000)
> > > > > Declare @.TableName varchar(100)
> > > > > Declare @.ID int
> > > > >
> > > > > Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> > > > > set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> > > > > Set @.SQL = 'Select Name from ' + @.TableName + ' where ID =' + Str(@.ID)
> > > > > exec (@.SQL)
> > > > >
> > > > > --
> > > > > Tea C.
> > > > >
> > > > >
> > > > > "KritiVerma@.hotmail.com" wrote:
> > > > >
> > > > > > please advice me with the below SQL Script
> > > > > >
> > > > > > say,I have a cursor to Insert a records in the table if the below condition matches
> > > > > > samay
> > > > > > I have different tables
> > > > > > 1. EMPLOYEE
> > > > > > ID Salary TableName
> > > > > > ==================================> > > > > > EMP1 25000 Nametable1
> > > > > > EMP3 15000 Nametable2
> > > > > > EMP19 15000 Nametable3
> > > > > > EMP5 10000 Nametable4
> > > > > >
> > > > > > 2.
> > > > > > Nametable1
> > > > > > ID Name
> > > > > > ============================> > > > > > EMP1 Samay
> > > > > > EMP2 Rahul
> > > > > > 3.
> > > > > > Nametable2
> > > > > > ID Name
> > > > > > ============================> > > > > > EMP19 Samay
> > > > > > EMP29 Rahul
> > > > > >
> > > > > > Now,
> > > > > > 1. I want to get the TableName(@.TableName) from EMPLOYEE where salary = 15000
> > > > > > 2. Select a Name from (@.TableName) table
> > > > > > where EMPLOYEE.ID = @.TableName.ID
> > > > > >
> > > > > >
> > > > > > Declare @.Name
> > > > > > Declare @.TableName
> > > > > > Declare @.ID
> > > > > >
> > > > > > Set @.ID = (Select ID from EMPLOYEE where salary = 15000)
> > > > > >
> > > > > > set @.TableName = (Select TableName from EMPLOYEE where salary = 15000 and ID = @.ID)
> > > > > >
> > > > > > Set @.Name = (Select Name from @.TableName where ID = @.ID)
> > > > > >
> > > > > > Now if I execute my script which has a cursor it give me an Error
> > > > > >
> > > > > > Must declare the variable '@.TableName '. for Last line script
> > > > > >
> > > > > > PLease advice me with this
> > > > > >