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 this
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
>
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||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, Ci
ty, AddressID into ##tempDelete ...etc.). You can then retrieve each of these fields into your vbls after you execute your dynamic SQL (@.SQL).[vbcol=seagreen]
> 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:
sql

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 = 1500
0
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 a
ll put together is a valid SQL Statement. Then you execute it via exec (@.SQ
L).
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 conditio
n 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 = 15
000
> 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
>|||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 INSE
RT after your SELECT.
Set @.SQL = ' declare @.name varchar(100) Select top 1 Name from ' + @.Table
Name + ' 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 ' + @.T
ableName + ' where ID =' + Str(@.ID)
exec (@.sql)
Hopefully, one of these 3 ideas meets your needs...
Tea C.
"KritiVerma@.hotmail.com" wrote:
[vbcol=seagreen]
> 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:
>|||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:
[vbcol=seagreen]
> 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 IN
SERT after your SELECT.
> Set @.SQL = ' declare @.name varchar(100) Select top 1 Name from ' + @.Tab
leName + ' where ID =' + Str(@.ID) ' + ' -- here goes your insert statemen
t 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:
>|||Change your last SET statement to:
Set @. Name = (select AddressTemp from ##TEMPDelete)
and make sure AddressTemp returns whatever you need to retrun when you inser
t 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 ##tempDelet
e
-- 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:
[vbcol=seagreen]
> 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:
>|||If your eID exists as a column in the Address table, then you would change y
our @.SQL to:
SET @.SQL = 'Select top 1 eID into ##TEMPDelete ......'
After executing the above @.SQL, you will end up with a tmp table ##TempDelet
e, 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 i
nto the ##tempDelete. If you need a value from a different field (or fields), you w
ill change your @.SQL to include these additional fields (E.g., select top 1 Address,
Ci
ty, AddressID into ##tempDelete ...etc.). You can then retrieve each of these fields into y
our vbls after you execute your dynamic SQL (@.SQL).[vbcol=seagreen]
> declare @.addressID int
> declare @.address varchar(50)
> declare @.city varchar(50)
> Select @.addressID = AddressID, @.Address=Address, @.city=City from ##tempDel
ete
> -- 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:
>