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 View?

This summary is not available. Please click here to view the post.

Dynamic query help SQL 2005

I'm having a problem in getting a dynamic query in a sp working and the
code is below so can someone please help me? It would be very much
appreciated.

Thanks
Girogio

------------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Search_Profile]
@.Country NVARCHAR(100) = null,
@.County NVARCHAR(100) = null,
@.Town NVARCHAR(100) = null,
@.AType bit,
@.PageIndex int,
@.NumRows int,
@.UsersCount int Output
AS
BEGIN

DECLARE @.where_clause NVARCHAR(500);

IF @.Country IS NOT NULL
BEGIN
SET @.where_clause = @.where_clause + ' AND aCountry = "' + @.Country +
'"'
END

IF @.County IS NOT NULL
BEGIN
SET @.where_clause = @.where_clause + ' AND aCounty = "' + @.County + '"'
END

IF @.Town IS NOT NULL
BEGIN
SET @.where_clause = @.where_clause + ' AND aTown = "' + @.Town + '"'
END

IF @.AType IS NOT NULL
BEGIN
SET @.where_clause = @.where_clause + ' AND Independent = "' +
Convert(NVARCHAR, @.AType) + '"'
END

DECLARE @.Query1 NVARCHAR(1000);
SET @.Query1 = 'SELECT @.UsersCount=(SELECT COUNT(*) FROM CustomProfile
WHERE aActive = 1 ' + @.where_clause
exec(@.Query1)

DECLARE @.startRowIndex int;
SET @.startRowIndex = (@.PageIndex * @.NumRows) +1;

DECLARE @.Query2 NVARCHAR(1000);

SET @.Query2 = 'WITH UsersProfiles as (
SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row, t.UserId,
t.apubName, t.aCounty, t.aTown, u.UserName
FROM CustomProfile t, vw_aspnet_Users u
WHERE t.UserID = u.UserID AND aActive = 1 ' + @.where_clause

Declare @.Query3 NVARCHAR(1000)

SET @.Query3 = '
SELECT UserId, apubName, aCounty, aTown, UserName
FROM ' + @.Query2 +
' WHERE Row BETWEEN ' + Convert(NVARCHAR, @.startRowIndex) + ' AND ' +
Convert(NVARCHAR, @.startRowIndex+@.NumRows-1)

exec(@.Query3)

END(george_Martinho@.hotmail.com) writes:
> I'm having a problem in getting a dynamic query in a sp working and the
> code is below so can someone please help me? It would be very much
> appreciated.

I certainly helps if you are more specific about what your problems
are. The one thing I caught at a glance is that you are using " as a
string delimiter. This is possible if QUOTED_IDENTIFIER is off, but
there is functionality that requires this setting to be on, so don't
do that.

Check out my article about dynamic search conditions on
http://www.sommarskog.se/dyn-search.html. There are some examples
that very similar to what you are doing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Girogio,

One problem I noticed is that the first time (actually everytime) you try to
set @.Where_Clause, It will always set @.Where_Clause to Null. Since
@.Where_Clause is Null to begin with.

Try This

DECLARE @.where_clause NVARCHAR(500);

Set @.where_clause = '' -- <New Line

--....Other Stuff as before

I didn't test this but I hope it helps.

-Dick Christoph
<george_Martinho@.hotmail.com> wrote in message
news:1139838631.991677.194800@.g47g2000cwa.googlegr oups.com...
> I'm having a problem in getting a dynamic query in a sp working and the
> code is below so can someone please help me? It would be very much
> appreciated.
> Thanks
> Girogio
> ------------
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER PROCEDURE [dbo].[Search_Profile]
> @.Country NVARCHAR(100) = null,
> @.County NVARCHAR(100) = null,
> @.Town NVARCHAR(100) = null,
> @.AType bit,
> @.PageIndex int,
> @.NumRows int,
> @.UsersCount int Output
> AS
> BEGIN
> DECLARE @.where_clause NVARCHAR(500);
> IF @.Country IS NOT NULL
> BEGIN
> SET @.where_clause = @.where_clause + ' AND aCountry = "' + @.Country +
> '"'
> END
> IF @.County IS NOT NULL
> BEGIN
> SET @.where_clause = @.where_clause + ' AND aCounty = "' + @.County + '"'
> END
> IF @.Town IS NOT NULL
> BEGIN
> SET @.where_clause = @.where_clause + ' AND aTown = "' + @.Town + '"'
> END
> IF @.AType IS NOT NULL
> BEGIN
> SET @.where_clause = @.where_clause + ' AND Independent = "' +
> Convert(NVARCHAR, @.AType) + '"'
> END
>
> DECLARE @.Query1 NVARCHAR(1000);
> SET @.Query1 = 'SELECT @.UsersCount=(SELECT COUNT(*) FROM CustomProfile
> WHERE aActive = 1 ' + @.where_clause
> exec(@.Query1)
> DECLARE @.startRowIndex int;
> SET @.startRowIndex = (@.PageIndex * @.NumRows) +1;
> DECLARE @.Query2 NVARCHAR(1000);
> SET @.Query2 = 'WITH UsersProfiles as (
> SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row, t.UserId,
> t.apubName, t.aCounty, t.aTown, u.UserName
> FROM CustomProfile t, vw_aspnet_Users u
> WHERE t.UserID = u.UserID AND aActive = 1 ' + @.where_clause
> Declare @.Query3 NVARCHAR(1000)
> SET @.Query3 = '
> SELECT UserId, apubName, aCounty, aTown, UserName
> FROM ' + @.Query2 +
> ' WHERE Row BETWEEN ' + Convert(NVARCHAR, @.startRowIndex) + ' AND ' +
> Convert(NVARCHAR, @.startRowIndex+@.NumRows-1)
> exec(@.Query3)
> END

Dynamic query help

I'm having a problem in getting a dynamic query in a sp working and the
code is below so can someone please help me? It would be very much
appreciated.
Thanks
Girogio
---
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Search_Profile]
@.Country NVARCHAR(100) = null,
@.County NVARCHAR(100) = null,
@.Town NVARCHAR(100) = null,
@.AType bit,
@.PageIndex int,
@.NumRows int,
@.UsersCount int Output
AS
BEGIN
DECLARE @.where_clause NVARCHAR(500);
IF @.Country IS NOT NULL
BEGIN
SET @.where_clause = @.where_clause + ' AND aCountry = "' + @.Country +
'"'
END
IF @.County IS NOT NULL
BEGIN
SET @.where_clause = @.where_clause + ' AND aCounty = "' + @.County + '"'
END
IF @.Town IS NOT NULL
BEGIN
SET @.where_clause = @.where_clause + ' AND aTown = "' + @.Town + '"'
END
IF @.AType IS NOT NULL
BEGIN
SET @.where_clause = @.where_clause + ' AND Independent = "' +
Convert(NVARCHAR, @.AType) + '"'
END
DECLARE @.Query1 NVARCHAR(1000);
SET @.Query1 = 'SELECT @.UsersCount=(SELECT COUNT(*) FROM CustomProfile
WHERE aActive = 1 ' + @.where_clause
exec(@.Query1)
DECLARE @.startRowIndex int;
SET @.startRowIndex = (@.PageIndex * @.NumRows) +1;
DECLARE @.Query2 NVARCHAR(1000);
SET @.Query2 = 'WITH UsersProfiles as (
SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row, t.UserId,
t.apubName, t.aCounty, t.aTown, u.UserName
FROM CustomProfile t, vw_aspnet_Users u
WHERE t.UserID = u.UserID AND aActive = 1 ' + @.where_clause
Declare @.Query3 NVARCHAR(1000)
SET @.Query3 = '
SELECT UserId, apubName, aCounty, aTown, UserName
FROM ' + @.Query2 +
' WHERE Row BETWEEN ' + Convert(NVARCHAR, @.startRowIndex) + ' AND ' +
Convert(NVARCHAR, @.startRowIndex+@.NumRows-1)
exec(@.Query3)
ENDThere are lots of problems with your dynamic SQL,
@.where_clause is always NULL (NULL + string gives NULL)
@.UsersCount will never get set as it is in the wrong scope
Your CTE should be at the start of the statement, not the middle
You should be able to avoid dynamic SQL by doing
something like this.
ALTER PROCEDURE [dbo].[Search_Profile]
@.Country NVARCHAR(100) = null,
@.County NVARCHAR(100) = null,
@.Town NVARCHAR(100) = null,
@.AType bit,
@.PageIndex int,
@.NumRows int,
@.UsersCount int Output
AS
BEGIN
SET NOCOUNT ON
SELECT @.UsersCount=COUNT(*) FROM CustomProfile
WHERE aActive = 1
AND (@.Country IS NULL OR aCountry=@.Country)
AND (@.County IS NULL OR aCounty=@.County)
AND (@.Town IS NULL OR aTown=@.Town)
AND (@.AType IS NULL OR Independent=@.AType)
DECLARE @.startRowIndex int;
SET @.startRowIndex = (@.PageIndex * @.NumRows) + 1;
WITH UsersProfiles as (
SELECT ROW_NUMBER() OVER (ORDER BY t.apubName) as Row,
t.UserId, t.apubName, t.aCounty, t.aTown, u.UserName
FROM CustomProfile t, vw_aspnet_Users u
WHERE t.UserID = u.UserID AND aActive = 1
AND (@.Country IS NULL OR aCountry=@.Country)
AND (@.County IS NULL OR aCounty=@.County)
AND (@.Town IS NULL OR aTown=@.Town)
AND (@.AType IS NULL OR Independent=@.AType)
)
SELECT UserId, apubName, aCounty, aTown, UserName
FROM UsersProfiles
WHERE Row BETWEEN @.startRowIndex AND @.startRowIndex+@.NumRows-1
END

Dynamic Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category, event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJ
CREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
David Portas
SQL Server MVP
|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
David Portas
SQL Server MVP
|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt =
'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net
sql

Dynamic Order By!

Hi all, I am new to store procedures and am trying to write one with a
dynamic or..
Below is my feeble attempt if anyone can point me in the right direction
that would be great.
CREATE PROCEDURE get_event_list
@.order_column INT = null
@.order_mode = varchar(4)
AS
BEGIN
SELECT event_code,event_title,event_topic,event_category,event_id FROM
event
ORDER BY
//select a table column based on a numeric value in @.order_column
parameter
//Then determine orientation by value in @.order_mode
//If order_mode = "asc" then the query is to be order ASC and vica versa.
END
AJCREATE PROCEDURE get_event_list
(@.order_column INT = NULL,
@.order_mode VARCHAR(4))
AS
BEGIN
SELECT event_code, event_title, event_topic, event_category, event_id
FROM Event
ORDER BY
CASE WHEN @.order_mode='ASC'
AND @.order_column=1 THEN event_code END ASC,
CASE WHEN @.order_mode='ASC'
AND @.order_column=2 THEN event_title END ASC,
...
CASE WHEN @.order_mode='DESC'
AND @.order_column=1 THEN event_code END DESC,
CASE WHEN @.order_mode='DESC'
AND @.order_column=2 THEN event_title END DESC,
...
END
--
David Portas
SQL Server MVP
--|||What I also should say is that you'll probably find you will get better
performance by including all the possible SELECTs in your SP, enclosing each
in an IF statement. If you prefer a single query solution then you can use
the CASE method I have given, or you could try using Dynamic SQL.
--
David Portas
SQL Server MVP
--|||Hey guys,
How do I create views in MSSQL where the source is coming from an Oracle
table?
Thanks.
neil|||Anthony Judd wrote:
> Hi all, I am new to store procedures and am trying to write one with a
> dynamic or..
> Below is my feeble attempt if anyone can point me in the right direction
> that would be great.
Here is some example I have. I know it is not a fancy solution, but it
is flexible...
CREATE PROCEDURE SomeList
@.akt CHAR(1) = '1',
@.OrderBy VARCHAR(50) = 'NazivUcionice',
@.OrderSort VARCHAR(10) = 'ASC'
AS
DECLARE @.SQLstmt varchar (1000)
SELECT @.SQLstmt = 'SELECT U.ID AS ElementID,
U.Naziv
U.Vrsta,
U.Tip,
U.Vlasnistvo,
U.Aktivna,
U.Broj_mjesta
FROM U
WHERE U.Aktivna=' +@.akt + '
ORDER BY ' + @.OrderBy + ' ' + @.OrderSort
EXEC (@.SQLstmt)
GO
--
Tomislav Bilic
ICQ: 1824223
http://www.escapestudio.net|||Using the Oracle OLEDB Provider, either create a linked server or use
OPENROWSET and specify the connection string.
--
David Portas
SQL Server MVP
--

Dynamic For Loop

My below code returns a union fine in northwind. However, I'd like add some
FOR LOOP's while building the SELECT statements to cut down on the redundant
code. As you can see, each @.SQLx statement has a variable from 1 to 3. I
created this exmple to get an idea of using a FOR LOOP to cut down on code.
CODE:
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000), @.SQL3
varchar(4000)
declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
@.dtEndDate1 datetime
declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
datetime, @.dtEndDate3 datetime
declare @.typeID int, @.debug int
set @.typeID = '1'
set @.dtStartDate1='19960101'
set @.dtEndDate1='19961231'
set @.dtStartDate2='19970101'
set @.dtEndDate2='19971231'
set @.dtStartDate3='19980101'
set @.dtEndDate3='19981231'
set @.dtYear1='1996'
set @.dtYear2='1997'
set @.dtYear3='1998'
SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
set @.debug = 0
IF @.typeID = 1
BEGIN
SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
END
IF @.typeID = 2
BEGIN
SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
END
SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate1, 112) + ''''
SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate2, 112) + ''''
SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate3, 112) + ''''
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
IF @.debug = 1
PRINT @.SQL
ELSE
EXEC(@.SQL)Scott
SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
See soem examples
DECLARE @.i INT
SET @.i=1
WHILE @.i<100
BEGIN
INSERT INTO Table VALUES (@.i)
SET @.i=@.i+1
END
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
> My below code returns a union fine in northwind. However, I'd like add
> some FOR LOOP's while building the SELECT statements to cut down on the
> redundant code. As you can see, each @.SQLx statement has a variable from 1
> to 3. I created this exmple to get an idea of using a FOR LOOP to cut down
> on code.
> CODE:
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000),
> @.SQL3 varchar(4000)
> declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
> @.dtEndDate1 datetime
> declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
> datetime, @.dtEndDate3 datetime
> declare @.typeID int, @.debug int
> set @.typeID = '1'
> set @.dtStartDate1='19960101'
> set @.dtEndDate1='19961231'
> set @.dtStartDate2='19970101'
> set @.dtEndDate2='19971231'
> set @.dtStartDate3='19980101'
> set @.dtEndDate3='19981231'
> set @.dtYear1='1996'
> set @.dtYear2='1997'
> set @.dtYear3='1998'
> SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
> SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
> SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
> set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
> set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
> set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
> set @.debug = 0
> IF @.typeID = 1
> BEGIN
> SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
> END
> IF @.typeID = 2
> BEGIN
> SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
> SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
> SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
> END
> SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate1, 112) + ''''
> SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate2, 112) + ''''
> SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate3, 112) + ''''
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
> IF @.debug = 1
> PRINT @.SQL
> ELSE
> EXEC(@.SQL)
>|||A better way would be something like this:
SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
MAX(O.Freight) AS maxoffreight
FROM Customers AS C
JOIN Orders AS O
ON C.customerid = O.customerid
WHERE O.orderdate BETWEEN @.startdate AND @.enddate
GROUP BY YEAR(O.orderdate) ;
David Portas
SQL Server MVP
--|||I guess what I'm asking is there a way in SQL to create dynamic variables?
So for example, my code would loop 3 times and each time my variables like
@.dtStartDate1 would increase from @.dtStartDate1 to @.dtStartDate2 and then
@.dtStartDate3.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aBO7zFBGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Scott
> SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
> See soem examples
> DECLARE @.i INT
> SET @.i=1
> WHILE @.i<100
> BEGIN
> INSERT INTO Table VALUES (@.i)
> SET @.i=@.i+1
> END
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
>|||I would do it like that except we have custom year begin and end dates and i
didn't want to take time to write a custom function.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134997569.867513.55250@.f14g2000cwb.googlegroups.com...
>A better way would be something like this:
> SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
> MAX(O.Freight) AS maxoffreight
> FROM Customers AS C
> JOIN Orders AS O
> ON C.customerid = O.customerid
> WHERE O.orderdate BETWEEN @.startdate AND @.enddate
> GROUP BY YEAR(O.orderdate) ;
> --
> David Portas
> SQL Server MVP
> --
>|||scott wrote:
> I would do it like that except we have custom year begin and end dates and
i
> didn't want to take time to write a custom function.
>
Are your annual begin and end dates fixed in the Calendar year or are
they based on some specific non-Gregorian calendar rules? If it's a
Calendar year, just use DATEADD to offset the date by the required
number of days. If it's specific company calendar logic, create a
calendar table and populate it with the information about your
customer's calendar. Join the calendar table into the query. Either
way, you don't need dynamic SQL.
David Portas
SQL Server MVP
--

Dynamic field question

Can someone point me in the right direction to solve the following
(basic) SQL problem below using SQL Server:

Let's say I have a table like this that lists people's likes:

CREATE TABLE likes (
myname VARCHAR (60),
travel BIT,
eatingout BIT,
disco BIT,
swimming BIT);

Let's say I put the following data inside this table:

INSERT INTO likes VALUES ('Darren', 1,0,0,1)
INSERT INTO likes VALUES ('John',1,1,0,1)
INSERT INTO likes VALUES ('Peter',0,0,0,0)
INSERT INTO likes VALUES ('Jill',0,0,0,1)

Then what I want is to create a (view? Or function? I am not sure),
called likes_details' that when I send this query:

SELECT myname, likes FROM likes_details

Returns the following:

Myname likes
DarrenTraveling and swimming
JohnTraveling, eating out and swimming
PeterDone not like anything
JillSwimming only

Please! Can anyone help!!

Thank you in advance.Darren,

The mess-ed up short term workaround to your problem with the existing
schema is:

SELECT myName,
COALESCE( NULLIF (
CASE travel WHEN 1 THEN 'Travelling, '
ELSE SPACE(0) END +
CASE eatingout WHEN 1 THEN 'eating out, '
ELSE SPACE(0) END +
CASE disco WHEN 1 THEN 'disco, '
ELSE SPACE(0) END +
CASE swimming WHEN 1 THEN 'swimming, '
ELSE SPACE(0) END, SPACE(0)),
'Do not like anything')
FROM likes ;

Now, the real solution to your problem is that you need to overhaul your
schema, it has values as column names, under-normalized and thus unusable. A
good way of representing this information would be like:

CREATE TABLE Persons (
Person_id INT NOT NULL PRIMARY KEY,
PersonName VARCHAR(10) NOT NULL,
...);
CREATE TABLE Hobbies (
Hobby_id INT NOT NULL PRIMARY KEY,
HobbyDesc VARCHAR(20) NOT NULL,
...);
CREATE TABLE PersonHobbies(
Person_id INT NOT NULL
REFERENCES Persons(Person_id),
Hobby_id INT NOT NULL
REFERENCES Hobbies(Hobby_id)
PRIMARY KEY (Person_id, Hobby_id)) ;

The primary keys in the Persons table & Hobbies tables are assigned with the
assumption that there could be other relevant attributes associated with
these entities, otherwise using Name & Desc as keys are just fine. The data
for these tables, based on the information you provided could be like:

INSERT Persons SELECT 1, 'Darren' ;
INSERT Persons SELECT 2, 'John' ;
INSERT Persons SELECT 3, 'Peter' ;
INSERT Persons SELECT 4, 'Jill' ;
GO
INSERT Hobbies SELECT 1, 'travel' ;
INSERT Hobbies SELECT 2, 'eatingout' ;
INSERT Hobbies SELECT 3, 'disco' ;
INSERT Hobbies SELECT 4, 'swimming' ;
GO
INSERT PersonHobbies SELECT 1, 1 ;
INSERT PersonHobbies SELECT 1, 4 ;
INSERT PersonHobbies SELECT 2, 1 ;
INSERT PersonHobbies SELECT 2, 2 ;
INSERT PersonHobbies SELECT 2, 4 ;
INSERT PersonHobbies SELECT 4, 4 ;
GO

The above schema represents a m-to-m relationship between Persons and
Hobbies. It allows you to add persons and hobbies to the system without
having to alter the tables and facilitates efficient querying. Now, you can
have a SQL statement like:

SELECT p1.PersonName, h1.HobbyDesc, ...
FROM Persons p1
LEFT OUTER JOIN PersonHobbies ph1
ON p1.Person_id = ph1.Person_id
LEFT OUTER JOIN Hobbies h1
ON h1.Hobby_id = ph1.Hobby_id

Get the resultset to your client application & cross tab the data to the
format with comma, add words like "and" etc for the requirements for
display.

--
Anith

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.