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 = 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
> > > > > >

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:
>

Dynamic query, local cursor variable and global cursors

Hi all.

I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.

So, first things first: let me explain what I need to do. I am

designing a web application that will allow users to consult info

available in a SQL2000 database. The user will enter the search

criterea, and hopefully the web page will show matching results.

The problem is the results shown aren't available per se in the DB, I

need to process the data a bit. I decided to do so on the SQL Server

side, though the use of cursors. So, when a user defines his search

criteria, I run a stored procedure that begins by building a dynamic

sql query and creating a cursor for it. I used a global cursor in order

to do so. It looked something like this:

SET @.sqlQuery = ... (build the dinamic sql query)

SET @.cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @.sqlQuery

EXEC @.cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor

This works fine, if there's only one instance of the

stored procedure running at a time. Should another user connect to the

site and run a search while someone's at it, it'll fail due to the

atempt to create a cursor with the same name.

My first thought was to make the cursor name unique, which led me to:

...

SET @.cursorName = 'myCursor' + @.uniqueUserID

SET @.cursorQuery = 'DECLARE '+ @.cursorName + 'CURSOR FAST_FORWARD FOR ' + @.sqlQuery

EXEC @.cursorQuery

...

The problem with this is that I can't do a FETCH NEXT FROM @.cursorName since

@.cursorName is a char variable holding the cursor name, and not a

cursor variable. So to enforce this unique name method the only option

I have is to keep creating dynamic sql queries and exucting them. And

this makes the sp a bitch to develop and maintain, and I'm guessing it

doesn't make it very performant.

So I moved on to my second idea: local cursor variables. The problem with

this is that if I create a local cursor variable by executing a dynamic

query, I can't extract it from the EXEC (or sp_executesql) context, as

it offers no output variable.

I guess my concrete questions are:

Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?

Anybody sees another way arround this?Thanks in advance,

Carlos

First off, let me just say that I kind of hate myself for the answer I am going to give you, because almost certainly the processing you are trying to do with a cursor in SQL could be done easier/better/faster/etcer outside of T-SQL, and you would be far happier with the final result.

On the other hand, I am pretty sure there is a way to do this, using sp_executesql by passing a parameter of type cursor to the proc:

declare @.query nvarchar(max), @.number int, @.mainCursor cursor

set @.query = ' set @.cursor = cursor for select 1 as number
open @.cursor'

exec sp_executesql @.query,N'@.cursor cursor output',@.mainCursor output

fetch next from @.maincursor into @.number
select @.number

Good luck with this, but seriously consider doing this outside of T-SQL :)

|||Hey Louis.

I can only say I was amazed to see that your piece of code worked,

since I had tried about the same thing a while ago and it didn't work.

So I did a little digging to see what the difference was between your

implementation and mine. And then I discovered something odd: if you

put the OPEN @.cursor after the sp_executesql command, instead of in it, you get an error saying that your variable has no cursor allocated to it. Go figure.

Well, I guess this is part of why you're telling me to give up T-SQL.

Believe me, I'm no masochist. I know this would be much easier if I did

it on the webserver's side, where I have a beautiful JVM eager to do

the job. But I have to disagree when you say it would be faster.

The trouble is that I have to go through a great amount of data to

display but a few lines of result to the user. The overhead involved in

transfering all this data to another system is just too great (it's a

web application, so the time scale is very short: a few seconds will be

enough to hamper it). So I guess I'll have to live with it, right?

Anyways, many thanks for your help. Problem solved. Moving on.

Carlos

Dynamic Query

I am trying to write some code to go through all the tables in my database
and do a row count. What I have done so far is open a cursor to sysobject
and loop round the tables in it. Then I am building a query in a string
"SELECT COUNT(*) FROM" + tablename . My next step is to EXEC the created
string. The problem is getting the result back from the count, can anybody
tell me how I do this?
thanks
Gav--Try this
Use Northwind
Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
SET @.tablename = 'Employees'
SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
print @.Count
~Bala|||Select object_name(id), rows from sysindexes where indid<1
Madhivanan|||<balacr@.gmail.com> wrote in message
news:1111658074.290946.83940@.f14g2000cwb.googlegroups.com...
> --Try this
> Use Northwind
> Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
> SET @.tablename = 'Employees'
> SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
> exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
> print @.Count
> ~Bala
>
Ths was great until I move the code from my Dev box to one of the servers
I want to run this on. Seems the sp_executeSQL does not exist, any ideas
why?
Gav|||Which version of SQL server are you using?
I am not sure whether 'sp_ExecuteSQL' was on SQL 7
Bala|||They are all on the same version SQL 2000 SP3a.. Only difference is the box
I am now trying to run it on is a SAP database server, box I wrote it on was
not.
Gav
<balacr@.gmail.com> wrote in message
news:1111665107.148760.270850@.g14g2000cwa.googlegroups.com...
> Which version of SQL server are you using?
> I am not sure whether 'sp_ExecuteSQL' was on SQL 7
> Bala
>|||Can you tell me the exact error that you are getting?|||Could not find stored procedure 'sp_executeSQL'.
<balacr@.gmail.com> wrote in message
news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
> Can you tell me the exact error that you are getting?
>|||My guess is that the server is case sensitive. The name of the procedure is
sp_executesql, not
sp_executeSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
> Could not find stored procedure 'sp_executeSQL'.
> <balacr@.gmail.com> wrote in message
> news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
>|||Spot on. Thanks to everyone for their help. :o)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwvlAeHMFHA.576@.TK2MSFTNGP15.phx.gbl...
> My guess is that the server is case sensitive. The name of the procedure
is sp_executesql, not
> sp_executeSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
>

Dynamic Cursor/ Dynamic SQL Statement

I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

Using the examples given in Books Online returns compilation errors. See below.

Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

James

-- SQL -----

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;

--Error-------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.using the following seem to be achieving what i wanted.
but i would still like to know how to use
1. PREPARE
2. EXECUTE
i.e. under what circumstances would you use those 2?
It must be there for a reason.

James

--Working SQL-----

DECLARE @.sql nvarchar(4000)
SET @.sql = 'DECLARE @.name nvarchar(128) ' +
'DECLARE test_cursor CURSOR FOR SElECT name FROM class_category ' +
'OPEN test_cursor ' +
'FETCH NEXT FROM test_cursor INTO @.name ' +
'BEGIN ' +
'PRINT @.name ' +
'FETCH NEXT FROM test_cursor INTO @.name ' +
'END '
EXECUTE sp_executesql @.sql|||You seem to be confusing the VB environment with the Transact-SQL environment. The VB code executes only on the client side, and that is where a PREPARE might make sense. The Transact-SQL code executes only on the server side, and you can't explicitly prepare code there (at least under normal circumstances).

-PatP

Dynamic Cursor versus Forward Only Cursor gives Poor Performance

Hello,

I have a test database with table A containing 10,000 rows and a table
B containing 100,000 rows. Rows in B are "children" of rows in A -
each row in A has 10 related rows in B (ie. B has a foreign key to A).

Using ODBC I am executing the following loop 10,000 times, expressed
below in pseudo-code:

"select * from A order by a_pk option (fast 1)"
"fetch from A result set"
"select * from B where where fk_to_a = 'xxx' order by b_pk option
(fast 1)"
"fetch from B result set" repeated 10 times

In the above psueod-code 'xxx' is the primary key of the current A
row. NOTE: it is not a mistake that we are repeatedly doing the A
query and retrieving only the first row.

When the queries use fast-forward-only cursors this takes about 2.5
minutes. When the queries use dynamic cursors this takes about 1 hour.

Does anyone know why the dynamic cursor is killing performance?
Because of the SQL Server ODBC driver it is not possible to have
nested/multiple fast-forward-only cursors, hence I need to explore
other alternatives.

I can only assume that a different query plan is getting constructed
for the dynamic cursor case versus the fast forward only cursor, but I
have no way of finding out what that query plan is.

All help appreciated.

KevinPlease explain what you are trying to do here. Cursors are usually best
avoided and typically perform much less efficiently than set-based
solutions. If you describe the problem in more detail someone should be able
to suggest an alternative that doesn't use a cursor. Post DDL (CREATE TABLE
statements), some sample data (INSERT statements) and show your required
result.

--
David Portas
SQL Server MVP
--

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
peleg
On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
.....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com
|||thnaks alot
"amish" wrote:

> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>
|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
>
>
>
>
>
> - Show quoted text -
:-)

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
pelegOn Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com|||thnaks alot
"amish" wrote:

> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
:-)

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
pelegOn Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
.....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com|||thnaks alot
"amish" wrote:
> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> > i have this code in which i define a @.TempTableCursor
> > but is there a way that the TargetTable will be
> > TargetTable1,TargetTable2,..TargetTable(i)
> > so when i define set the @.TempTableCursor it will have in the
> > defenition the TargetTable with a dynamic changing number?
> > [code]
> > Declare @.TempTableCursor cursor
> > Set @.TempTableCursor = Cursor Local FAST_FORWARD
> > For Select * From TargetTable
> > [/code]
> >
> > thnaks in advance
> > peleg
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
> > On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> > > i have this code in which i define a @.TempTableCursor
> > > but is there a way that the TargetTable will be
> > > TargetTable1,TargetTable2,..TargetTable(i)
> > > so when i define set the @.TempTableCursor it will have in the
> > > defenition the TargetTable with a dynamic changing number?
> > > [code]
> > > Declare @.TempTableCursor cursor
> > > Set @.TempTableCursor = Cursor Local FAST_FORWARD
> > > For Select * From TargetTable
> > > [/code]
> > > thnaks in advance
> > > peleg
> > declare @.sql nvarchar(4000)
> > declare @.table varchar(100)
> > set @.table = 't'
> > set @.sql = N'
> > set @.cur = cursor for
> > select
> > * from ' + @.table + '; open @.cur'
> > exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> > output
> > if cursor_status('variable', '@.cur') = 1
> > begin
> > .....................
> > .....................
> > end
> > if cursor_status('variable', '@.cur') >= 0
> > close @.cur
> > deallocate @.cur
> > Regards
> > Amish Shah
> >http://shahamish.tripod.com- Hide quoted text -
> - Show quoted text -
:-)

Dynamic Cursor Generation..

Hi Everybody,
I have a probs with dynamic generation.
I am writing the probs
======================================
create proc test
as
declare @.query varchar(500)
set @.query = 'select * from table'
--------------
declare mycur Cursor for Select * from table |
open mycur |
--------------
but instate of above block how can I dynamically generate this query?
------------
declare mycur Cursor for exec (@.query) |
------------
Or tell me the way.
Regards
Arijit Chatterjeehi, the problem was not clear what u want to do dynamiclly.
if u want to ftech rows thru proc, u can code like

create proc test
as
use db
exec(select * from table)

this might work

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||On 4 Nov 2003 21:04:27 -0800, arijitchatterjee123@.yahoo.co.in (Arijit
Chatterjee) wrote:
>but instate of above block how can I dynamically generate this query?
>------------
>declare mycur Cursor for exec (@.query) |
>------------

The way I've done stuff like this in the past is to stuff the results
into a temp table and run a cursor off that.

Example:
declare @.SQLstring varchar(100)
create table #foo (foovar int)

select @.SQLString = 'select distinct foovar from footable'

insert into #foo exec(@.SQLString)

declare foo_cursor for select distinct foovar from #foo
etc.

Obviously you really want to do this type of stuff when there is no
other way around it.

>Or tell me the way.
>Regards
>Arijit Chatterjee

dynamic cursor - sorting in declaration

Hello everybody!

I have a small table "ABC" like this:

id_position | value
--------
1 | 11
2 | 22
3 | 33

I try to use a dynamic cursor as below.
When the statement "order by id_position" in declare part of the cursor_abc
is omitted - cursor work as it should.
But when the statement "order by id_position" is used, cursor behave as
static one.
What's the matter, does anybody know?

Code:

declare @.id_position as int, @.value as int

DECLARE cursor_abc CURSOR
FOR
select id_position, value from abc
order by id_position

set nocount on
open cursor_abc
FETCH NEXT FROM cursor_abc
INTO @.id_position, @.value

WHILE @.@.FETCH_STATUS = 0
BEGIN

print @.id_position
print @.value
print '---------'

update abc set value=666 --next reading should give value=666

FETCH NEXT FROM cursor_abc
INTO @.id_position, @.value

END

CLOSE cursor_abc
DEALLOCATE cursor_abc
GO

Regards

Lucasukasz W. wrote:

Quote:

Originally Posted by

I try to use a dynamic cursor as below.

Cursors should be avoided if at all possible.

Quote:

Originally Posted by

print @.id_position
print @.value
print '---------'

Is this just a quick-and-dirty test? If you're trying to generate an
actual production file like this, then you should seriously consider
having the database output raw data, and using some separate tool to
apply formatting.

Quote:

Originally Posted by

update abc set value=666 --next reading should give value=666

This is obviously dummy code. What are you actually trying to do
here - apply some function and use the result to control which row is
output next? What does that function look like? It may be possible
to rewrite the whole thing without cursors; failing that, you should
seriously consider having the database output data unsorted, or sorted
in a simple fashion, and using some separate tool (possibly the same
one used to apply formatting) to apply the complex sort rule.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

In many years of writing SQL, I have seldom found a need for a cursor.
They usually run 1-2 orders of magnitude slwoer than a relational
solution.

When someone uses one, it is generally becasue they are mimicing a
magnetic tape file system, and probably violating the basic principle
of a tiered architecture that display is done in the front end and
never in the back end. This a more basic programming principle than
just SQL and RDBMS.

Finally, id_position is not an ISO-11179 data element name and it makes
no sense. Identifier of what? Position of what? You have two
adjectives without a noun. But I bet you mant it to be PHYSICAL
location because you are mimicing a magnetic tape file system, instead
of using SQL for an RDBMS.

What is your real problem? Show us and perhaps we can help you.

Dynamic Cursor

I am trying to use a dynamic cursor in a stored procedure:
The code looks like this :

/************************************************** ***
set @.sFormula = 'Monthlyformula'
set @.sStartDate = '02/01/2004'
set @.sEndDate = '02/01/2004'

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '+ @.sStartDate +' and '+ @.sEndDate +')' )
/************************************************** ***

And this is what it is interpreting

select populateid From appgridrows where histdisplaygrid = 3 And Monthlyformula Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between 02/01/2004 and 02/01/2004)

My problem is Is there anyway that I can put the quotes before those dates('02/01/2004') so that my cursor has some records returned

Thanks in advance

SKwhat happens if you try

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '''+ @.sStartDate +''' and '''+ @.sEndDate +''')' )

or is it

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between ''+ @.sStartDate +'' and ''+ @.sEndDate +'')' )|||Dynamic SQL AND a Cursor..

Johnny...tell him/her they've won...

Is this inside a sproc?

What are you ultimatley trying to do?

In other words, what action is applied to the cursor rows?|||Originally posted by Paul Young
what happens if you try

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '''+ @.sStartDate +''' and '''+ @.sEndDate +''')' )

or is it

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between ''+ @.sStartDate +'' and ''+ @.sEndDate +'')' )

Either of the Methods deosnt work|||I am trying to get records from a sql into cursor, in which the sql is determined by the input parameters for that SP. Thats why the sql is determined dynamically

Thanks

Originally posted by Brett Kaiser
Dynamic SQL AND a Cursor..

Johnny...tell him/her they've won...

Is this inside a sproc?

What are you ultimatley trying to do?

In other words, what action is applied to the cursor rows?

Dynamic cursor

Hi All…

I need to bind a DataGrid to server dynamic cursor.

Please help!

That is such a 'bad' and boneheaded idea, that there is no way I would offer help other than to suggest you 're-think' the issue.

Using a dynamic cursor in that manner means that locks will be placed on rows, and most likely, tables, and it will be up to the user's whim to close the form and release those locks -meaning that other users will be waiting, and waiting, and waiting.

It's rarely a good idea to use cursors, and in this scenario, that is NOT a good plan...

Dynamic ALTER TABLE and CURSOR

I'd like to create a dynamic ALTER TABLE and CURSOR based on a variable. Is
it possible ?If you really wanna do that dynamically by a Statement you should go for
that example which can be easily implemented in a trigger.
Create Table VarTestTable
(
COlumn1 INT
)
GO
DECLARE @.TableName varchar(50)
SET @.TableName = 'VarTestTable'
EXEC ('ALTER TABLE ' + @.TableName + ' ADD Column2 INT')
GO
Select * from VarTestTable
But beware of the curse of dynamic SQL :
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jean Dupont" <Jean Dupont@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F79E4618-EA42-44AC-9550-383E44EC0E67@.microsoft.com...
> I'd like to create a dynamic ALTER TABLE and CURSOR based on a variable.
> Is
> it possible ?|||Thank you. I didn't know what I did because I tried before to ask the
question and it didn't worked. But your very clear exemple is working very
well, even with cursors.
Thanks again.
"Jens Sü?meyer" a écrit :

> If you really wanna do that dynamically by a Statement you should go for
> that example which can be easily implemented in a trigger.
> Create Table VarTestTable
> (
> COlumn1 INT
> )
> GO
> DECLARE @.TableName varchar(50)
> SET @.TableName = 'VarTestTable'
> EXEC ('ALTER TABLE ' + @.TableName + ' ADD Column2 INT')
> GO
> Select * from VarTestTable
> But beware of the curse of dynamic SQL :
> http://www.sommarskog.se/dynamic_sql.html
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Jean Dupont" <Jean Dupont@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:F79E4618-EA42-44AC-9550-383E44EC0E67@.microsoft.com...
>
>