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 Parameters S.P.

Using a Stored procedure, i want to sort my records according to a dynamic
field... Could be sort by firstName, CompanyName, UserID... and so on...
Receiving value in @.orderingBy, but i can't seem to find a way to properly
integrate it into my query, juse into the 'order by'...
If any could help...
CREATE PROCEDURE [dbo].[SelectContactListTEST]
@.orderingBy nvarchar (255)
AS
SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid
FROM UserObject UO INNER JOIN
WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN (0, 1)) AND
(UO.Hidden = 0)
ORDER BY "+ @.orderingBy + "How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"lp_rochon" wrote:

> Using a Stored procedure, i want to sort my records according to a dynamic
> field... Could be sort by firstName, CompanyName, UserID... and so on...
> Receiving value in @.orderingBy, but i can't seem to find a way to properly
> integrate it into my query, juse into the 'order by'...
> If any could help...
>
> CREATE PROCEDURE [dbo].[SelectContactListTEST]
> @.orderingBy nvarchar (255)
> AS
> SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid
> FROM UserObject UO INNER JOIN
> WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN (0, 1)) A
ND
> (UO.Hidden = 0)
> ORDER BY "+ @.orderingBy + "|||Thank alot, it does work..
But now, to next question:
I the same aspect, i got my 'where' statement that i need to be dynamic...
As:
WHERE (UO.customer_type = 'wholesaler')
could become:
WHERE (UO.customer_type = 'wholesaler') AND (UO.A = 'XX') AND (UO.B =
'ZZ')
to do that, i want to pass 1 variable (string) as:
WHERE (UO.customer_type = 'wholesaler') @.whereString
know how to do this?
"Alejandro Mesa" wrote:

> How do I use a variable in an ORDER BY clause?
> http://www.aspfaq.com/show.asp?id=2501
>
> AMB
>|||And now to a new problem...
Calling my procedure, passing this
EXEC SelectContactListTEST '0','1','0','lastname','ASC'
I'm getting the following error:
erver: Msg 245, Level 16, State 1, Procedure SelectContactListTEST, Line 9
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
nvarchar value 'TEST' to a column of data type bit.
I really don't see were this 'TEST' column comes from...
See below for code
----
--
CREATE PROCEDURE [dbo].[SelectContactListTEST]
@.activeSearch bit,
@.activeSearch2 bit,
@.hiddenSearch bit,
@.orderingBy nvarchar (255),
@.orderingWay nvarchar (255)
AS
SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid, UO.company AS
company, UO.ContOwner AS contowner, UO.u_first_name AS firstname,
UO.u_last_name AS lastname, UO.u_email_address AS
email, UO.Hidden AS hidden, UO.Activated AS activated, UO.d_date_created AS
datecreated,
A.u_city AS city, A.u_country_code AS country,
A.u_region_code AS region
FROM UserObject UO INNER JOIN
Addresses A ON UO.g_user_id = A.g_id
WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN
(@.activeSearch,@.activeSearch2)) AND (UO.Hidden = @.hiddenSearch)
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then Activated
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then Activated
end
END
GO
---
"Alejandro Mesa" wrote:

> How do I use a variable in an ORDER BY clause?
> http://www.aspfaq.com/show.asp?id=2501
>
> AMB
>|||On Tue, 8 Feb 2005 09:03:01 -0800, lp_rochon wrote:

>And now to a new problem...
>
>Calling my procedure, passing this
>EXEC SelectContactListTEST '0','1','0','lastname','ASC'
>I'm getting the following error:
>erver: Msg 245, Level 16, State 1, Procedure SelectContactListTEST, Line 9
>[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
>nvarchar value 'TEST' to a column of data type bit.
>I really don't see were this 'TEST' column comes from...
Hi lp_rochon,
I guess it's in your data, as one of the last names.
Look at this part:

> case @.orderingWay
> when 'ASC' then
> CASE @.orderingBy
> WHEN 'company' then company
> WHEN 'lastname' then u_last_name
> WHEN 'country' then u_country_code
> WHEN 'region' then u_region_code
> WHEN 'city' then u_city
> WHEN 'email' then u_email_address
> WHEN 'activated' then Activated
> end
> END
I assume that all columns here are char, varchar nchar or nvarchar, except
for the Activated column, which I guess to be bit. The datatype of the
CASE is determined by looking at all datatypes used in the WHEN
expressions; if they are different (and no explicit conversion is used),
then datatype precedence decides which is converted to what. In this case,
SQL Server will attempt to convert all varchar columnsto bit, since bit
has a higher precedence than varchar.
Try changing your ORDER BY to either
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then CAST(Activated as char(1))
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then CAST(Activated as char(1))
end
END
or
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
end
END
DESC,
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'activated' then Activated
end
END
DESC,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
end
END,
case @.orderingWay
when 'ASC' then
CASE @.orderingBy
WHEN 'activated' then Activated
end
END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Dynamic Parameter List

Is it possible to have a dynamic number of parameters? Basically I have a
vertical table that will contain a number of records. As records are added
to this table, they will need to be passed to a stored procedure. So say I
have this structure:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
@.dynamic_param2, etc. etc.
Tbl_Dynamic_Params
ID
1 Nationality
2 Net Worth
3 Hair Color
4 Shoe Size
So I want to pass:
SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
@.Net_Worth, @.HairColor, @.ShoeSize.
Is this possible without using dynamic SQL?"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>
You can have *optional* parameters in a proc, yes. Just specify a default
value for those parameters. For example:
CREATE PROC usp_proc
@.p1 INT = NULL,
@.p2 INT = NULL
AS ...
Both @.p1 and @.p2 are optional and will default to null if they are not
specified. Obviously your parameter *names* must be known at runtime
otherwise you couldn't write any code that used those parameters could you?
Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
used for system procs and has a special meaning that will cause unwanted
side-effects to your code.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I don't actually use sp as a prefix, it was merely a visual cue. I
understand optional parameters, but I want the list of parameters to be
dynamic, not just the arguments...that's the crux of the problem. I don't
think there's a really good solution to this.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uX5l3nC5HHA.5724@.TK2MSFTNGP05.phx.gbl...
> "James" <neg@.tory.com> wrote in message
> news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
>> Is it possible to have a dynamic number of parameters? Basically I have
>> a vertical table that will contain a number of records. As records are
>> added to this table, they will need to be passed to a stored procedure.
>> So say I have this structure:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state,
>> @.dynamic_param1, @.dynamic_param2, etc. etc.
>> Tbl_Dynamic_Params
>> ID
>> 1 Nationality
>> 2 Net Worth
>> 3 Hair Color
>> 4 Shoe Size
>> So I want to pass:
>> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
>> @.Net_Worth, @.HairColor, @.ShoeSize.
>> Is this possible without using dynamic SQL?
> You can have *optional* parameters in a proc, yes. Just specify a default
> value for those parameters. For example:
>
> CREATE PROC usp_proc
> @.p1 INT = NULL,
> @.p2 INT = NULL
> AS ...
> Both @.p1 and @.p2 are optional and will default to null if they are not
> specified. Obviously your parameter *names* must be known at runtime
> otherwise you couldn't write any code that used those parameters could
> you?
> Important: Do not use "sp_" as a prefix for your procs. "sp_" is the name
> used for system procs and has a special meaning that will cause unwanted
> side-effects to your code.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>|||"James" <neg@.tory.com> wrote in message
news:eK3xtqC5HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I don't actually use sp as a prefix, it was merely a visual cue. I
>understand optional parameters, but I want the list of parameters to be
>dynamic, not just the arguments...that's the crux of the problem. I don't
>think there's a really good solution to this.
>
I don't understand what you mean when you say you want the "list of
parameters to be dynamic". Parameters have names and types. If you don't
know the names and types in advance then how could you possibly write a proc
that made use of them - even assuming it was possible to pass them as
parameters?
Maybe what you want is an array. SQL Server has a data structure that is
much more powerful than an array: a table! :-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
The crux of the problem has to do with the poor design scheme you use to
represent various attributes as values. If you have a static set of
properties that you have to manage, treat them as columns rather than values
in a column. The right answer is that you should reconsider the logical
design. Mixing up data with metadata is always a recipe for messy and
complex solutions.
--
Anith|||You're preaching to the choir. This isn't my design. Let me rewind. A
colleague approach me with this problem.
Contacts Table:
ID First_Name Last_Name ... ...
Pretty basic. Now there is additional data that needs to be stored. I.E.
Shoe Size, Hat Size, and various other data. However, not every record will
have all of these fields. Also, fields need to be added on the fly with the
lowest amount of maintenance possible.
Performance is a huge consideration. He has approximately 2 million records
in the Contacts table and say, 10 fields +/-. He believes that performance
will be better if he has a flat table that is a 1-to-1 with this table that
stores the extra columns...as opposed to a more normalized alternative that
they have in place now which CRAWLS.
Should I talk him out of this redesign, encourage it, or suggest an
alternative? If so, that begs the question...what alternative?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23rOPW9C5HHA.4964@.TK2MSFTNGP06.phx.gbl...
>> ... but I want the list of parameters to be dynamic, not just the
>> arguments...that's the crux of the problem.
> The crux of the problem has to do with the poor design scheme you use to
> represent various attributes as values. If you have a static set of
> properties that you have to manage, treat them as columns rather than
> values in a column. The right answer is that you should reconsider the
> logical design. Mixing up data with metadata is always a recipe for messy
> and complex solutions.
> --
> Anith
>|||"James" <neg@.tory.com> wrote in message
news:eLGgUPD5HHA.5316@.TK2MSFTNGP04.phx.gbl...
> You're preaching to the choir. This isn't my design. Let me rewind. A
> colleague approach me with this problem.
> Contacts Table:
> ID First_Name Last_Name ... ...
> Pretty basic. Now there is additional data that needs to be stored. I.E.
> Shoe Size, Hat Size, and various other data. However, not every record
> will have all of these fields. Also, fields need to be added on the fly
> with the lowest amount of maintenance possible.
> Performance is a huge consideration. He has approximately 2 million
> records in the Contacts table and say, 10 fields +/-. He believes that
> performance will be better if he has a flat table that is a 1-to-1 with
> this table that stores the extra columns...as opposed to a more normalized
> alternative that they have in place now which CRAWLS.
> Should I talk him out of this redesign, encourage it, or suggest an
> alternative? If so, that begs the question...what alternative?
>
Two million rows is not large and should be well within the capabilities of
even an entry level server. If performance is inadequate then start looking
at indexing, query plans, statistics and procedure design.
As for the logical model, a normalized design is the right place to start.
Nothing you have said suggests any need for a change from that.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:elfSovC5HHA.3400@.TK2MSFTNGP03.phx.gbl...
>.
> Maybe what you want is an array. SQL Server has a data structure that is
> much more powerful than an array: a table! :-)
>.
This is a disturbing statement from someone who purports to
understand a strong typed system. I assume its rationale
is not intellectual. Shall I pull out the sql couch and
start the examination? :-)
www.beyondsql.blogspot.com|||You mean like printf. I seriously doubt it considering the hoops C has to go
through to make it work.
How about name/value pairs passed into one large varchar?
set @.paramerters = 'first_name='+@.first_name+';last_name='+@.last_name
create procedure sp_update_contact @.parameters
as
declare @.parameters varchar(max)
loop through the string looking for the ; delimiter, then split the
name/value pair and put it in a table variable:
declare @.MyArray TABLE (
name varchar(20)
value varchar(20)
)
Then do as you please.
Since the name portion had to corospond to a column in the table, you better
have some decent error checking an support for an error return.
"James" <neg@.tory.com> wrote in message
news:Ol%23pUYC5HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Is it possible to have a dynamic number of parameters? Basically I have a
> vertical table that will contain a number of records. As records are
> added to this table, they will need to be passed to a stored procedure.
> So say I have this structure:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.dynamic_param1,
> @.dynamic_param2, etc. etc.
> Tbl_Dynamic_Params
> ID
> 1 Nationality
> 2 Net Worth
> 3 Hair Color
> 4 Shoe Size
> So I want to pass:
> SP_Update_Contact @.first_name, @.last_name, @.city, @.state, @.Nationaltiy,
> @.Net_Worth, @.HairColor, @.ShoeSize.
> Is this possible without using dynamic SQL?
>sql

Dynamic Page Breaks

How can I create dynamic page breaks based on a number of records I only want
to be displayed per page?To create a page break after a specified number of rows:
1. Select the table properties
2. Add a grouping
3. In the Expression field add the following:
=Ceiling(RowNumber(Nothing)/30)
4. Check the â'Page break at endâ' check box.
reeves
"Terry" wrote:
> How can I create dynamic page breaks based on a number of records I only want
> to be displayed per page?|||Thank you so kindly.
By the way, should the new page break group be added to the already existing
groups?
"Reeves Smith" wrote:
> To create a page break after a specified number of rows:
> 1. Select the table properties
> 2. Add a grouping
> 3. In the Expression field add the following:
> =Ceiling(RowNumber(Nothing)/30)
> 4. Check the â'Page break at endâ' check box.
> reeves
> "Terry" wrote:
> > How can I create dynamic page breaks based on a number of records I only want
> > to be displayed per page?|||Terry,
Yes you need to add a group even if groups already exist. Where it is placed
might get a little tricky and need to be played with.
I got this info from a technical article: Report Design: Best Practices and
Guidelines
but it looks like this article got updated to: Report Design Tips and Tricks
(http://msdn2.microsoft.com/en-us/library/bb395166(sql.90).aspx) and there
looks to be another way in the new article to do what your after.
Here is the site for some great articles:
http://msdn2.microsoft.com/en-us/library/aa496080.aspx
The Chart one is VERY good.
Reeves
"Terry" wrote:
> Thank you so kindly.
> By the way, should the new page break group be added to the already existing
> groups?
>
> "Reeves Smith" wrote:
> > To create a page break after a specified number of rows:
> >
> > 1. Select the table properties
> > 2. Add a grouping
> > 3. In the Expression field add the following:
> >
> > =Ceiling(RowNumber(Nothing)/30)
> >
> > 4. Check the â'Page break at endâ' check box.
> >
> > reeves
> >
> > "Terry" wrote:
> >
> > > How can I create dynamic page breaks based on a number of records I only want
> > > to be displayed per page?|||I have tried this on multiple occassions and I always get this error message...
[rsInvalidGroupExpressionScope] A group expression for the table â'table1â'
uses the RowNumber function with a scope parameter that is not valid. When
used in a group expression, the value of the scope parameter of RowNumber
must equal the name of the group directly containing the current group.
Build complete -- 1 errors, 0 warnings
I'm sure there is something else that i am missing...
Any suggestions will be greatly appreciated.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
"Reeves Smith" wrote:
> To create a page break after a specified number of rows:
> 1. Select the table properties
> 2. Add a grouping
> 3. In the Expression field add the following:
> =Ceiling(RowNumber(Nothing)/30)
> 4. Check the â'Page break at endâ' check box.
> reeves
> "Terry" wrote:
> > How can I create dynamic page breaks based on a number of records I only want
> > to be displayed per page?

Dynamic (Parameterized) Page-Breaks

Hi,

I have a report, where depending on a parameter-value (e.g. Change in day: Yes/No), I need to display the records in one page or multiple pages (i.e. If Change in Day = Yes, display the records for each day in different pages. If No, display all the records in one page). In-short page break for each day, if there is a change in day, else no page break.

Can anyone suggest, is this possible to do with SSRS 2005 RTM ? I had read somewhere that conditional page-breaks is not supported. But, is there any other way I can do this ?

Thanks in Advance.

Isnt there a way to achieve this ?

Its URGENT. Any help is highly appreciated.

|||

you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value

like if user has selected "Yes" then set group expression

=iif(parametrvalue=yes,<fieldname-day>,1)

try this, it should work.

|||

Thanks a lot. It worked.

Thanks again.

|||but be careful it will work only if you have only one group in report. If you add more then it won't work.|||How to implement Page break if we are having more than one group

Dynamic (Parameterized) Page-Breaks

Hi,

I have a report, where depending on a parameter-value (e.g. Change in day: Yes/No), I need to display the records in one page or multiple pages (i.e. If Change in Day = Yes, display the records for each day in different pages. If No, display all the records in one page). In-short page break for each day, if there is a change in day, else no page break.

Can anyone suggest, is this possible to do with SSRS 2005 RTM ? I had read somewhere that conditional page-breaks is not supported. But, is there any other way I can do this ?

Thanks in Advance.

Isnt there a way to achieve this ?

Its URGENT. Any help is highly appreciated.

|||

you need to create a dummy group with "page break at end" true for this and the expression for group will be set according to parameter value

like if user has selected "Yes" then set group expression

=iif(parametrvalue=yes,<fieldname-day>,1)

try this, it should work.

|||

Thanks a lot. It worked.

Thanks again.

|||but be careful it will work only if you have only one group in report. If you add more then it won't work.|||How to implement Page break if we are having more than one group

Dy

I have to get a count of records using dynamic sql.
I have the following and I'm getting errors (Syntax error converting the
varchar value 'SELECT ' to a column of data type int.):
[Code]
DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject varchar(10
)
DECLARE @.SQL varchar(8000)
DECLARE @.RC int
SELECT @.sCalldate ='20050712'
SELECT @.sAgentid ='0'
SELECT @.sProject = 'A'
SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = ' + @.sCallDate +
' AND ISNULL(sRawLogout, ''x'') = ''x'''
IF @.sAgentId <> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
END
IF @.sProject<> '0'
BEGIN
SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
END
EXEC (@.SQL)
If I put the @.RC inside the quotes I get error: Must declare the variable
'@.RC'.
I tested by printing the statement and it prints properly just errors when
executing.
How can I make this work?
Thanks,
Ninel
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200507/1EXEC() has its own scope, so has no idea what @.RC is (nor does it "see"
sAgentID or @.sProject).
Try getting rid of the dynamic SQL altogether:
SELECT @.RC = Count(*)
FROM dbo.tmPunchTime
WHERE scalldate = @.sCallDate
AND COALESCE(sRawLogout, 'x') = 'x'
AND sAgentID = CASE @.sAgentID WHEN 'O' THEN sAgentID ELSE @.sAgentID
END
AND sProject = CASE @.sProject WHEN 'O' THEN sProject ELSE @.sProject
END
Or see http://www.aspfaq.com/2492
"ninel gorbunov via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:51A8B62C943CC@.webservertalk.com...
> I have to get a count of records using dynamic sql.
> I have the following and I'm getting errors (Syntax error converting the
> varchar value 'SELECT ' to a column of data type int.):
> [Code]
> DECLARE @.sCalldate varchar(10) , @.sAgentId varchar(10), @.sProject
> varchar(10)
> DECLARE @.SQL varchar(8000)
> DECLARE @.RC int
> SELECT @.sCalldate ='20050712'
> SELECT @.sAgentid ='0'
> SELECT @.sProject = 'A'
> SELECT @.SQL = 'SELECT ' + @.RC + ' = Count(*)
> FROM dbo.tmPunchTime
> WHERE scalldate = ' + @.sCallDate +
> ' AND ISNULL(sRawLogout, ''x'') = ''x'''
> IF @.sAgentId <> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sAgentId = @.sAgentId '
> END
> IF @.sProject<> '0'
> BEGIN
> SELECT @.SQL = @.SQL + ' AND sProject= @.sProject'
> END
> EXEC (@.SQL)
> If I put the @.RC inside the quotes I get error: Must declare the variable
> '@.RC'.
> I tested by printing the statement and it prints properly just errors when
> executing.
> How can I make this work?
> Thanks,
> Ninel
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200507/1|||I have a similar problem.
USE MyDataBase
GO
if exists
(select * from sysobjects where id =
object_id(N'[dbo].[sp_MTV_Diskalert]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_MTV_Diskalert]
GO
create procedure sp_MTV_diskalert (
@.LowerLimit INT
, @.LinkedServerName SYSNAME
, @.NumberOfEmailsToSend INT
, @.EmailAddress1 NVARCHAR(30)
, @.EmailAddress2 NVARCHAR(30))
AS
--
BEGIN
--
SET XACT_ABORT ON
SET NOCOUNT ON
--
DECLARE @.RCPT VARCHAR(500)
, @.LIMIT INT
, @.SQLSTRING NVARCHAR(4000)
, @.STRING2 NVARCHAR(4000)
, @.EMAIL NVARCHAR(4000)
, @.ParmDefinition NVARCHAR(4000)
, @.ParmIn NVARCHAR(4000)
, @.ParmRet NVARCHAR(4000)
, @.MyCounter int
, @.TempEmailAddress NVARCHAR(30)
--
set @.MyCounter = 0
--
--The following stmt works
--
exec (select @.EmailAddress1 as name)
--
while ( @.MyCounter <> @.NumberOfEmailsToSend )
Begin
SET @.MyCounter = @.MyCounter + 1
SET @.TempEmailAddress = '@.EmailAddress'+ltrim(str(@.MyCounter))
SET @.SQLString = N'Select ' +@.TempEmailAddress+ ' as name'
-- print @.SQLstring
EXECUTE sp_executesql
@.SQLString
,@.params = N'@.email nvarchar(30) output'
,@.email=@.email output
END
END
go
EXEC Sealedair.dbo.sp_SAC_diskalert
250,DUNSQL02,1,'Michael.varriale@.sealedair.com','2','3','4','5','6','7','8',
'9','10'
Must declare the variable '@.EmailAddress1'.

DWH problem: updating a table with every 1000 records a checkpoint

Hi,

Currently we're a building a metadatadriven datawarehouse in SQL
Server 2000. We're investigating the possibility of the updating
tables with enormeous number of updates and insert and the use of
checkpoints (for simple recovery and Backup Log for full recovery).

On several website people speak about full transaction log and the
pace of growing can't keep up with the update. Therefore we want to
create a script which flushes the dirty pages to the disk. It's not
quite clear to me how it works. Questions we have is:
* How does the process of updating, insert and deleting works with SQL
Server 2000 with respect to log cache, log file, buffer cache, commit,
checkpoint, etc?
What happens when?
* As far as i can see now: i'm thinking of creating chunks of data of
1000 records with a checkpoint after the Query. SQL server has the
default of implicit transactions and so it will not need a commit.
Something like this?
* How do i create chunks of 1000 records automatically without
creating a identity field or something. Is there something like SELECT
NEXT 1000?

Greetz,

Hennie> * How does the process of updating, insert and deleting works with SQL
> Server 2000 with respect to log cache, log file, buffer cache, commit,
> checkpoint, etc?
> What happens when?

There's a discussion of this in the Books Online
<architec.chm::/8_ar_sa_8unn.htm>. In summary, all data modifications are
written to both the buffer cache and transaction log buffer cache. The log
buffer is written first. Modified buffer cache pages are written to disk by
either worker threads, the lazy writer or the checkpoint process. Worker
threads write data when they need to wait for a read i/o anyway. The lazy
writer process wakes up periodically and writes data in order to maintain a
reasonable number of free cache buffers. The checkpoint process writes all
dirty pages to disk periodically (about a minute by default) in order to
reduce startup recovery time.

Log buffers are written as they become full, when you issue a COMMIT and
during a checkpoint.

> * As far as i can see now: i'm thinking of creating chunks of data of
> 1000 records with a checkpoint after the Query. SQL server has the
> default of implicit transactions and so it will not need a commit.
> Something like this?
> * How do i create chunks of 1000 records automatically without
> creating a identity field or something. Is there something like SELECT
> NEXT 1000?

I'm not sure I understand what you are asking here. In the default
autocommit mode, each statement is a separate transaction.

It seems you are mostly concerned with transaction log space management.
The main consideration is to keep your transactions reasonably sized since
only committed data can be removed from the log during a log backup (FULL or
BULK_LOGGED recovery model) or checkpoint (SIMPLE model).

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0402090341.23275aae@.posting.google.c om...
> Hi,
> Currently we're a building a metadatadriven datawarehouse in SQL
> Server 2000. We're investigating the possibility of the updating
> tables with enormeous number of updates and insert and the use of
> checkpoints (for simple recovery and Backup Log for full recovery).
> On several website people speak about full transaction log and the
> pace of growing can't keep up with the update. Therefore we want to
> create a script which flushes the dirty pages to the disk. It's not
> quite clear to me how it works. Questions we have is:
> * How does the process of updating, insert and deleting works with SQL
> Server 2000 with respect to log cache, log file, buffer cache, commit,
> checkpoint, etc?
> What happens when?
> * As far as i can see now: i'm thinking of creating chunks of data of
> 1000 records with a checkpoint after the Query. SQL server has the
> default of implicit transactions and so it will not need a commit.
> Something like this?
> * How do i create chunks of 1000 records automatically without
> creating a identity field or something. Is there something like SELECT
> NEXT 1000?
> Greetz,
> Hennie|||Hennie de Nooijer (hdenooijer@.hotmail.com) writes:
> * How does the process of updating, insert and deleting works with SQL
> Server 2000 with respect to log cache, log file, buffer cache, commit,
> checkpoint, etc?
> What happens when?
> * As far as i can see now: i'm thinking of creating chunks of data of
> 1000 records with a checkpoint after the Query. SQL server has the
> default of implicit transactions and so it will not need a commit.
> Something like this?
> * How do i create chunks of 1000 records automatically without
> creating a identity field or something. Is there something like SELECT
> NEXT 1000?

I hope Dan's reply shed some light on how the log works.

As for breaking up in chucks there is nothing built-in like:

UPDATE tbl
SET yadayada
...
WITH COMMIT EACH 1000 ROW

For DML statements you will have to find out how to do the batching best
from your data. Note there is no reason to keep a strict batch size, but
if you have some column that divides the data nicely that can be used.
For instance, if you are handling order data, you can take one day or
month at a time.

The one case where you can tell SQL Server to commit by each n row is
when you bulk load with BCP or BULK INSERT.

I also like to point out that selecting the batch size, is a trade-off
between log size and speed. Particularly if the search condition to
identify a batch takes time to execute, too small batch sizes can be
costly. A little depending on row size, but 100000 rows at time is not
an unreasonable number. 1000 is by far to small.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > * How does the process of updating, insert and deleting works with SQL
> > Server 2000 with respect to log cache, log file, buffer cache, commit,
> > checkpoint, etc?
> > What happens when?
> There's a discussion of this in the Books Online
> <architec.chm::/8_ar_sa_8unn.htm>.
How does this works?

> In summary, all data modifications are
> written to both the buffer cache and transaction log buffer cache. The log
> buffer is written first. Modified buffer cache pages are written to disk by
> either worker threads, the lazy writer or the checkpoint process. Worker
> threads write data when they need to wait for a read i/o anyway. The lazy
> writer process wakes up periodically and writes data in order to maintain a
> reasonable number of free cache buffers. The checkpoint process writes all
> dirty pages to disk periodically (about a minute by default) in order to
> reduce startup recovery time.
Ok clear..

> Log buffers are written as they become full, when you issue a COMMIT and
> during a checkpoint.
It thought that a commit is not neccessary a write to the disk. A
commit is just a end mark of a transaction as far i can see in the
documentation. A checkpoint will write the transactions to the
database.

> > * As far as i can see now: i'm thinking of creating chunks of data of
> > 1000 records with a checkpoint after the Query. SQL server has the
> > default of implicit transactions and so it will not need a commit.
> > Something like this?
> > * How do i create chunks of 1000 records automatically without
> > creating a identity field or something. Is there something like SELECT
> > NEXT 1000?
> I'm not sure I understand what you are asking here. In the default
> autocommit mode, each statement is a separate transaction.
> It seems you are mostly concerned with transaction log space management.
> The main consideration is to keep your transactions reasonably sized since
> only committed data can be removed from the log during a log backup (FULL or
> BULK_LOGGED recovery model) or checkpoint (SIMPLE model).
Yup this is what i meant. Only the question is what is a reasonable
chunk? Perhaps i will create parameter for this so i can tune this.|||> I hope Dan's reply shed some light on how the log works.
Yup

> As for breaking up in chucks there is nothing built-in like:
> UPDATE tbl
> SET yadayada
> ...
> WITH COMMIT EACH 1000 ROW
> For DML statements you will have to find out how to do the batching best
> from your data. Note there is no reason to keep a strict batch size, but
> if you have some column that divides the data nicely that can be used.
> For instance, if you are handling order data, you can take one day or
> month at a time.
Yeah well i have identity column which i could use for chunking the
records. Disadvantage is that it is an implementation depending on the
functionality of a column in the table. It's not what i want but i'll
have to deal with it.

> The one case where you can tell SQL Server to commit by each n row is
> when you bulk load with BCP or BULK INSERT.
Well this is not possible. I'm reading and insert/updating from other
tables in SQL server and BCP reads only from files.

> I also like to point out that selecting the batch size, is a trade-off
> between log size and speed. Particularly if the search condition to
> identify a batch takes time to execute, too small batch sizes can be
> costly. A little depending on row size, but 100000 rows at time is not
> an unreasonable number. 1000 is by far to small.
Well 1000 was just a hunch (is this rightly written). I thinking of
creating a parameter from which we can tune the chunks..

Thx you all for your answers

Greetz
Hennie|||> > There's a discussion of this in the Books Online
> > <architec.chm::/8_ar_sa_8unn.htm>.
> How does this works?

From the Books Online, you can go directly to the referenced topic by
clicking Go-->URL and pasting in the URL 'architec.chm::/8_ar_sa_8unn.htm'.

> > Log buffers are written as they become full, when you issue a COMMIT and
> > during a checkpoint.
> It thought that a commit is not neccessary a write to the disk. A
> commit is just a end mark of a transaction as far i can see in the
> documentation. A checkpoint will write the transactions to the
> database.

A COMMIT ensures all committed data are written to the log file so that
committed data are permanently persisted to the database. Modified data may
or may not have been written to data files because dirty data pages are
written separately by worker threads, the lazy writer or checkpoint process.
This is transparent to your application because data are always retrieved
from buffer cache.

Only committed data before the oldest uncommitted transaction can be removed
from the log. This occurs during a log backup in the FULL or BULK_LOGGED
recovery model or during a checkpoint in the SIMPLE model. Rather than
trying to take control of the checkpoint frequency, all you really need to
do is ensure your transactions are reasonably short and small.

> Only the question is what is a reasonable
> chunk? Perhaps i will create parameter for this so i can tune this.

If you are running in the SIMPLE recovery model and have many small
insert/update/delete transactions, I suggest you simply let SQL Server
truncate the log automatically. However, if you modify a lot of data (e.g.
millions of rows) in a single statement or transaction, you can use the
techniques Erland mentioned to keep the log size reasonable. In my
experience, the optimal amount is somewhat less that the amount of memory
allocated to SQL Server.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hennie de Nooijer" <hdenooijer@.hotmail.com> wrote in message
news:191115aa.0402110050.7797d139@.posting.google.c om...
> > > * How does the process of updating, insert and deleting works with SQL
> > > Server 2000 with respect to log cache, log file, buffer cache, commit,
> > > checkpoint, etc?
> > > What happens when?
> > There's a discussion of this in the Books Online
> > <architec.chm::/8_ar_sa_8unn.htm>.
> How does this works?
> > In summary, all data modifications are
> > written to both the buffer cache and transaction log buffer cache. The
log
> > buffer is written first. Modified buffer cache pages are written to
disk by
> > either worker threads, the lazy writer or the checkpoint process.
Worker
> > threads write data when they need to wait for a read i/o anyway. The
lazy
> > writer process wakes up periodically and writes data in order to
maintain a
> > reasonable number of free cache buffers. The checkpoint process writes
all
> > dirty pages to disk periodically (about a minute by default) in order to
> > reduce startup recovery time.
> Ok clear..
> > Log buffers are written as they become full, when you issue a COMMIT and
> > during a checkpoint.
> It thought that a commit is not neccessary a write to the disk. A
> commit is just a end mark of a transaction as far i can see in the
> documentation. A checkpoint will write the transactions to the
> database.
> > > * As far as i can see now: i'm thinking of creating chunks of data of
> > > 1000 records with a checkpoint after the Query. SQL server has the
> > > default of implicit transactions and so it will not need a commit.
> > > Something like this?
> > > * How do i create chunks of 1000 records automatically without
> > > creating a identity field or something. Is there something like SELECT
> > > NEXT 1000?
> > I'm not sure I understand what you are asking here. In the default
> > autocommit mode, each statement is a separate transaction.
> > It seems you are mostly concerned with transaction log space management.
> > The main consideration is to keep your transactions reasonably sized
since
> > only committed data can be removed from the log during a log backup
(FULL or
> > BULK_LOGGED recovery model) or checkpoint (SIMPLE model).
> Yup this is what i meant. Only the question is what is a reasonable
> chunk? Perhaps i will create parameter for this so i can tune this.|||[posted and mailed, please reply in news]

Hennie de Nooijer (hdenooijer@.hotmail.com) writes:
> Yeah well i have identity column which i could use for chunking the
> records. Disadvantage is that it is an implementation depending on the
> functionality of a column in the table. It's not what i want but i'll
> have to deal with it.

One thing that I should have added is that you should see if the
clustered index is good for chunking. That can speed up the time
it takes for SQL Server to locate the rows quite a bit.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp