Combining Data from Variable number of tables

I have a requirment to take data from a large set of tables where the
total number of these tables may change on a regular baisis and
output into another table. All the tables willl have the same
columns. Frequency is being debated but it maybe as much as once per
hour.

Example
1) I need to choose all the following tables
select * from dbo.sysobjects where name like '_CPY%.

2) then I need the following
for each of the tables found above, I need the outfrom from each of
those tables to be inputted into another table. basically, I would
want the following output from each of the tables found in step 1

select machineid,name from _cpy_offermanager_678

3) In the end I would have something like dbo.ALLCPY with records
combined from all other _CPY tables

Ron SorrellTry something like:

SET NOCOUNT ON
CREATE TABLE ALLCPY
(
machineid int NOT NULL,
name varchar(30) NOT NULL

)
DECLARE @.InsertStatement nvarchar(4000)

DECLARE InsertStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'INSERT INTO ALLCPY
SELECT machineid,name FROM ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
TABLE_NAME LIKE '[_]CPY%'

OPEN InsertStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM InsertStatements INTO @.InsertStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.InsertStatement)
END
CLOSE InsertStatements
DEALLOCATE InsertStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Ron Sorrell" <ron.sorrell@.quaysys.com> wrote in message
news:4cgrmv0tnu355pk28jb7si938uq9pe6d85@.4ax.com...
> I have a requirment to take data from a large set of tables where the
> total number of these tables may change on a regular baisis and
> output into another table. All the tables willl have the same
> columns. Frequency is being debated but it maybe as much as once per
> hour.
> Example
> 1) I need to choose all the following tables
> select * from dbo.sysobjects where name like '_CPY%.
> 2) then I need the following
> for each of the tables found above, I need the outfrom from each of
> those tables to be inputted into another table. basically, I would
> want the following output from each of the tables found in step 1
> select machineid,name from _cpy_offermanager_678
> 3) In the end I would have something like dbo.ALLCPY with records
> combined from all other _CPY tables
> Ron Sorrell|||Perfect
Thank you very much

Ron Sorrell

On Sun, 21 Sep 2003 20:04:31 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:

>Try something like:
>
>SET NOCOUNT ON
>CREATE TABLE ALLCPY
> (
> machineid int NOT NULL,
> name varchar(30) NOT NULL
>)
>DECLARE @.InsertStatement nvarchar(4000)
>DECLARE InsertStatements CURSOR
>LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT
> N'INSERT INTO ALLCPY
> SELECT machineid,name FROM ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> FROM INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> TABLE_NAME LIKE '[_]CPY%'
>OPEN InsertStatements
>WHILE 1 = 1
>BEGIN
> FETCH NEXT FROM InsertStatements INTO @.InsertStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.InsertStatement)
>END
>CLOSE InsertStatements
>DEALLOCATE InsertStatementssqlsql

Column, parameter, or variable #1: Cannot find data type SqlDatareader

Hello Everyone,

A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:

public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index)
{
if(dr.GetSqlValue(index) == null)
return SqlInt32.Null;
else
return dr.GetSqlInt32(index)
}

I than allways get the following ErrorMessage:

Column, parameter, or variable #1: Cannot find data type SqlDatareader.

Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.

My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.

Would appreciate any kind of help! Thanks

You may need to refer to the SqlDataReader with full qualified name if you haven't using the proper namespace:

System.Data.SqlClient.SqlDataReader

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/de...es_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/d...>
_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Query with a Variable

I have 30 fields in a table called ADP_Pay_Detail that I need to query with
the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
DED_CD_30. I have coded a counter with a WHILE statement to increment the
last number of this string, and assign the entire string to a varchar
variable named @.column_amt. All of the type conversions I need to do are
correct. I then try to write a query using this variable as a field name.
An example would be
SELECT *
FROM ADP_Pay_Detail
where @.column_amt = 30
This isn't working at all. Can anyone give me a suggestion about how to
accomplish what I want to do? The query I have to write is much more
complicated than my example, and I'd hate to have to write it 30 times to
query each column!
Thanks!
Keith
Keith>I have 30 fields in a table called ADP_Pay_Detail that I need to query with
> the exact same query. Each field is named DED_CD_1, DED_CD_2, etc. up to
> DED_CD_30.
Just curious if you have considered a more sensible and relational design?
Are you going to add DED_CD_31 -> DED_CD_n at some point?

> SELECT *
> FROM ADP_Pay_Detail
> where @.column_amt = 30
> This isn't working at all.
That is right, because T-SQL is not a language that allows you to construct
queries like this, you must tell it about the metadata and not let it decide
on its own. My first suggestion would be to pass all 30 columns back, and
let the app display only the column(s) it needs. This will be more flexible
as you can later decide to show 2 or 3 or all columns and you won't have
much change to do...
My more intuitive response would be to store the data and its value as data,
instead of mixing data and metadata. An example might be:
CREATE TABLE dbo.ADP_Pay_Detail
(
pdID INT PRIMARY KEY
-- , other columns
)
CREATE TABLE dbo.ADP_Pay_Detail_DED_CD
(
pdID INT FOREIGN KEY REFERENCES dbo.ADP_Pay_Detail(pdID),
column_amt TINYINT, -- CHECK CONSTRAINT perhaps?
value INT
)
Now you can say
SELECT * FROM ADP_Pay_Detail d
INNER JOIN ADP_Pay_Detail_DED_CD c
ON d.pdID = c.pdID
WHERE c.column_amt = 30
And you don't have any silly column names with data embedded in them. (Next
you can work on manageable table names.)

> Can anyone give me a suggestion about how to
> accomplish what I want to do?
As a last resort, dynamic SQL. PLEASE READ THE FOLLOWING and heed the
warnings seriously:
http://www.sommarskog.se/dynamic_sql.html|||>> I have 30 fields [sic] in a table called ADP_Pay_Detail that I need to query w
ith
the exact same query. Each field [sic] is named DED_CD_1, DED_CD_2,
etc. up to DED_CD_30. <<
Rows are not records; fields are not columns; tables are not files.
You need to get a book on RDBMS basics and read the chapter on Normal
Forms. What you have here is a 1950's COBOL file layout with a fake
OCCURS clause.
Apparently SQL is the first compiled language you have ever used. It
is also a declarative language, so writing loops in a proprietary 3GL
is a sign of poor coding.
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.

Column or Field variable for an Excel query.

Hello all.
I have an excel query that pulls data from a SQL database. I have 2 parameters that represent the [start] and [end] of a between criteria. Here is the query.

SELECT CltDue.CDClientName
, CltDue.CDEventDesc
, CltDue.CDDescription
, (empfname)+' '+(emplname)
, CltDue.CDTargetAmount
, CltDue.CDTargetHours
FROM VPM.dbo.CltDue CltDue, VPM.dbo.Employee Employee
WHERE CltDue.CDInCharge = Employee.ID
AND ((CltDue.CDStart Between ? And ?))

This works fine, but what i wish to do is create a 3rd parameter for CltDue.CDStart
There are 4 fields in the CltDue table that i need.CDstart,CDdateComplete, CDTarget, and CDDateDelivered.
I want the user to be able to choose one of these 4 from a list box and then enter the beginning and end date.
Is excel capable of this?
Thanks for any help.

Quote:

Originally Posted by silversubey

Hello all.
I have an excel query that pulls data from a SQL database. I have 2 parameters that represent the [start] and [end] of a between criteria. Here is the query.

SELECT CltDue.CDClientName
, CltDue.CDEventDesc
, CltDue.CDDescription
, (empfname)+' '+(emplname)
, CltDue.CDTargetAmount
, CltDue.CDTargetHours
FROM VPM.dbo.CltDue CltDue, VPM.dbo.Employee Employee
WHERE CltDue.CDInCharge = Employee.ID
AND ((CltDue.CDStart Between ? And ?))

This works fine, but what i wish to do is create a 3rd parameter for CltDue.CDStart
There are 4 fields in the CltDue table that i need.CDstart,CDdateComplete, CDTarget, and CDDateDelivered.
I want the user to be able to choose one of these 4 from a list box and then enter the beginning and end date.
Is excel capable of this?
Thanks for any help.

As with any of the VBA applications excel included you should be able to create either an dynamic SQL statement including your column values from a selectable list and then fire the SQL to the SQL server to retrieve your dataset or even better reference an stored procedure either way....you are in the SQL Server forum so maybe you are better served in an Excel forum to follow through on the VBA application side of it?

Regards

Jim :)|||Thanks, I'll give it a try

Column name as variable

Does anyone know if I can use a variable for a column name in a query?
I'm wanting to reuse some code that updates a table, but, depending on
conditions, I want it to update a different column.
Something like:
declare @.col_name as ?
Select @.col_name = "last_week"
update tblTest set @.col_name = blah blah blah
Perspiring minds want to know.
DS
Hello,
You may need to use dynamic sql for this. Take a look into EXEC and
SP_EXECUTESQL in books online
Thanks
Hari
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178038066.444666.41510@.y5g2000hsa.googlegrou ps.com...
> Does anyone know if I can use a variable for a column name in a query?
> I'm wanting to reuse some code that updates a table, but, depending on
> conditions, I want it to update a different column.
> Something like:
> declare @.col_name as ?
> Select @.col_name = "last_week"
> update tblTest set @.col_name = blah blah blah
> Perspiring minds want to know.
> DS
>
|||On May 1, 9:54 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> You may need to use dynamic sql for this. Take a look into EXEC and
> SP_EXECUTESQL in books online
> Thanks
> Hari
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178038066.444666.41510@.y5g2000hsa.googlegrou ps.com...
>
>
>
>
>
> - Show quoted text -
Gracias. That looks promising.

Column name as variable

Does anyone know if I can use a variable for a column name in a query?
I'm wanting to reuse some code that updates a table, but, depending on
conditions, I want it to update a different column.
Something like:
declare @.col_name as '
Select @.col_name = "last_week"
update tblTest set @.col_name = blah blah blah
Perspiring minds want to know.
DSHello,
You may need to use dynamic sql for this. Take a look into EXEC and
SP_EXECUTESQL in books online
Thanks
Hari
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
> Does anyone know if I can use a variable for a column name in a query?
> I'm wanting to reuse some code that updates a table, but, depending on
> conditions, I want it to update a different column.
> Something like:
> declare @.col_name as '
> Select @.col_name = "last_week"
> update tblTest set @.col_name = blah blah blah
> Perspiring minds want to know.
> DS
>|||On May 1, 9:54 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> You may need to use dynamic sql for this. Take a look into EXEC and
> SP_EXECUTESQL in books online
> Thanks
> Hari
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Gracias. That looks promising.

Column Name as Variable

Greetings,

I have a table that contains various columns in it totalling 12,000 rows of data. For example;

site_ref, account_title, gl_code, period1, period2, period3 etc through to period12

I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?

Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.

Any pointers would be appreciated

Regards

Hello Toni,

You can use dynamic code, look at the exec statement at BOL. Basically, you form the code based on user choices and then pass it to exec statement.
exec 'select * from ATable where site_ref='tb' and period3=3 '. Surf this forum for more info about using "exec", including security issues. I believe this has been discussed a lot.

Let me suggest you another solution. Maybe you should reorganize your table schema like this: remove period columns from 1-12, make one "period" column and add another one, PeriodID. For example, let you have a row with these values:

site_ref, account_title, gl_code, period1, period2, period3 ...
'tb' , 'gross rent' , 1234 , 111 , 222 , 333 ...

After reorganization, you will have 12 rows instead of this one:

site_ref, account_title, gl_code, period, PeriodID
'tb' , 'gross rent' , 1234 , 111 ,1
'tb' , 'gross rent' , 1234 , 222 ,2
'tb' , 'gross rent' , 1234 , 333 ,3
...

This schema is a little bit redundant, but it is indeed much easier for use. Good luck!

|||

Hello and thanks for the response. May I be cheeky and ask how I could achieve your idea. I too was thinking of re-organising the data into the format you suggested, only because historically I have always found it easier to deal with data in this way. The problem I have is that every month this data will change. the figures for the next period will be added to the source table and then this is to be over written into the existing table.

If I were to say have a table as it stands now with site_ref, account_title, gl_code, period1, period2, period3 etc etc which is updated monthly by my accounts team. How can I create a T_SQL statement that would look at the source data as is exampled in the beginning of this paragraph, manipulate the data to the way that you are suggesting. If I understand you right you are looking to take my current 12,000 rows and duplicate them, once for every month and add the figures for the next period into the next set of 12,000 records and then add in the new column period2, 3 etc etc.

Normally I would do this manually in Excel but seen as we are dealing with multiples of 12,000 I would reach the 65K limit in Excel pretty quickly, Plus I would rather the routine had some for of error checking. For example, it would look at the value in the row for three columns, which would always be unique to that row and then place the relevant value for the next period in the value column, and then add the periodID too

Example

Owner,Site_Ref,GL_Code,Account_Type,Period1, Period2, Period3

EXON,TB,1234,Gross Rent,79354,80587,0

EXON,TB,1236,Gross Rent,37000,39000,0

to

Owner,Site_Ref,GL_Code,Account_Type,Value, Period

EXON,TB,1234,Gross Rent,79354,1

EXON,TB,1234,Gross Rent,80587,2

EXON,TB,1234,Gross Rent,0,3

and then the next month the last line of the example would be updated with the value relevant to that period and so on

Thanks for your help so far

Regards

|||

Toni,

Sorry I couldn't answer you earlier.
When answering your question I assumed you can change the schema whatever you want, but it seems that other team is using the table too.
Ask them if you can change the tabse schema. If you can, then just rewrite the existing queries and, then write a "change" script that reads the old table data and inserts all rows from it to the newer one. If you need some help in this, then we could try it together.
If they cannot, then keeping 2 tables, reading the data from one into another might prove as painful as running quieries against the old table "as it is".
I have an impression that I might have understood the case not correctly-if this is true,feel free to reformulate the question and we will try to find an answer.

Column Name as Variable

Greetings,

I have a table that contains various columns in it totalling 12,000 rows of data. For example;

site_ref, account_title, gl_code, period1, period2, period3 etc through to period12

I wish to write a query that will allow me to search for specific site_ref, acount_title etc and then only one of the period columns. This period column will be specified by the user at the time of submitting the query through reporting services. How do I assign a column to a variable so that the user can set it in the report parameters and then the code will run against that specific column for the period?

Example would be to see everything for site_ref = 'tb', account_title = 'gross rent' and the financial figures within the column titled 'period10' or the next time they run the report they may wish to run it against the values in period7.

Any pointers would be appreciated

Regards

Hello Toni,

You can use dynamic code, look at the exec statement at BOL. Basically, you form the code based on user choices and then pass it to exec statement.
exec 'select * from ATable where site_ref='tb' and period3=3 '. Surf this forum for more info about using "exec", including security issues. I believe this has been discussed a lot.

Let me suggest you another solution. Maybe you should reorganize your table schema like this: remove period columns from 1-12, make one "period" column and add another one, PeriodID. For example, let you have a row with these values:

site_ref, account_title, gl_code, period1, period2, period3 ...
'tb' , 'gross rent' , 1234 , 111 , 222 , 333 ...

After reorganization, you will have 12 rows instead of this one:

site_ref, account_title, gl_code, period, PeriodID
'tb' , 'gross rent' , 1234 , 111 ,1
'tb' , 'gross rent' , 1234 , 222 ,2
'tb' , 'gross rent' , 1234 , 333 ,3
...

This schema is a little bit redundant, but it is indeed much easier for use. Good luck!

|||

Hello and thanks for the response. May I be cheeky and ask how I could achieve your idea. I too was thinking of re-organising the data into the format you suggested, only because historically I have always found it easier to deal with data in this way. The problem I have is that every month this data will change. the figures for the next period will be added to the source table and then this is to be over written into the existing table.

If I were to say have a table as it stands now with site_ref, account_title, gl_code, period1, period2, period3 etc etc which is updated monthly by my accounts team. How can I create a T_SQL statement that would look at the source data as is exampled in the beginning of this paragraph, manipulate the data to the way that you are suggesting. If I understand you right you are looking to take my current 12,000 rows and duplicate them, once for every month and add the figures for the next period into the next set of 12,000 records and then add in the new column period2, 3 etc etc.

Normally I would do this manually in Excel but seen as we are dealing with multiples of 12,000 I would reach the 65K limit in Excel pretty quickly, Plus I would rather the routine had some for of error checking. For example, it would look at the value in the row for three columns, which would always be unique to that row and then place the relevant value for the next period in the value column, and then add the periodID too

Example

Owner,Site_Ref,GL_Code,Account_Type,Period1, Period2, Period3

EXON,TB,1234,Gross Rent,79354,80587,0

EXON,TB,1236,Gross Rent,37000,39000,0

to

Owner,Site_Ref,GL_Code,Account_Type,Value, Period

EXON,TB,1234,Gross Rent,79354,1

EXON,TB,1234,Gross Rent,80587,2

EXON,TB,1234,Gross Rent,0,3

and then the next month the last line of the example would be updated with the value relevant to that period and so on

Thanks for your help so far

Regards

|||

Toni,

Sorry I couldn't answer you earlier.
When answering your question I assumed you can change the schema whatever you want, but it seems that other team is using the table too.
Ask them if you can change the tabse schema. If you can, then just rewrite the existing queries and, then write a "change" script that reads the old table data and inserts all rows from it to the newer one. If you need some help in this, then we could try it together.
If they cannot, then keeping 2 tables, reading the data from one into another might prove as painful as running quieries against the old table "as it is".
I have an impression that I might have understood the case not correctly-if this is true,feel free to reformulate the question and we will try to find an answer.

Column name as variable

Does anyone know if I can use a variable for a column name in a query?
I'm wanting to reuse some code that updates a table, but, depending on
conditions, I want it to update a different column.
Something like:
declare @.col_name as '
Select @.col_name = "last_week"
update tblTest set @.col_name = blah blah blah
Perspiring minds want to know.
DSHello,
You may need to use dynamic sql for this. Take a look into EXEC and
SP_EXECUTESQL in books online
Thanks
Hari
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
> Does anyone know if I can use a variable for a column name in a query?
> I'm wanting to reuse some code that updates a table, but, depending on
> conditions, I want it to update a different column.
> Something like:
> declare @.col_name as '
> Select @.col_name = "last_week"
> update tblTest set @.col_name = blah blah blah
> Perspiring minds want to know.
> DS
>|||On May 1, 9:54 am, "Hari Prasad" <hari_prasa...@.hotmail.com> wrote:
> Hello,
> You may need to use dynamic sql for this. Take a look into EXEC and
> SP_EXECUTESQL in books online
> Thanks
> Hari
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178038066.444666.41510@.y5g2000hsa.googlegroups.com...
>
> > Does anyone know if I can use a variable for a column name in a query?
> > I'm wanting to reuse some code that updates a table, but, depending on
> > conditions, I want it to update a different column.
> > Something like:
> > declare @.col_name as '
> > Select @.col_name = "last_week"
> > update tblTest set @.col_name = blah blah blah
> > Perspiring minds want to know.
> > DS- Hide quoted text -
> - Show quoted text -
Gracias. That looks promising.

column as variable

I have a problem that I'm sure is very simple to answer for anyone that knows a bit of T-SQL. In a stored procedure, I simply want to concatenate a string variable containing a column name into a Select statement.

For example:
I want to execute the following statement but using a variable for the column name:

Select * from tblmet1araw where JulianDay = 1

JulianDay is an integer
This is how I have my code set up:

declare @.xxx as varchar(20)
set @.theday = 'JulianDay'

select * from tblmet1araw where @.theday = 1

I get the following error:
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value 'JulianDay' to a column of data type int.declare @.col varchar(10)
set @.col='id'
exec('select * from sysobjects where '+@.col+'=1')|||Thanks. That works.

column alias as variable

Is there a way to select a column as an alias using a variable for the alias? something like this:

SELECT Column1 as @.myVariable FROM Table1

Not directly in a SELECT statement. You can use dynamic SQL like:

set @.sql = N'SELECT Column1 as ' + quotename(@.myVariable) + N' FROM Table1'

exec sp_executesql @.sql

But it is not going to be pretty if you want to do this for multiple columns and dynamic SQL has security implications/management issues. Why do you want to do this? How will the client handle this if the column names can be modified arbitrarily? One way to do this is to fix the column names as c1, c2, c3 etc and have a separate result set or metadata that contains the user friendly names for c1, c2, c3 respectively.

|||

The user enters a period length such as 30 days and the report displays the period date ranges as the coulmn name of a pivot.

like this:

Location 1-30 31-60 61-90

Texas 10 3 2

Florida 5 8 7

collecting values into a variable in a loop

Dear All,

Im trying to collect values from a query into a single variable within a loop, like so:

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = @.myString + , + (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop

END

For some reason though the @.myString does not collect up the values, but will equal NULL at the end of the loop.

If however I simple do

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop
END

Then I get the last value from the query as expected.

Can anyone explain why this might be?

Thanks in advance!Hi

Your variable is null initially -
NULL +'Something' = NULL

HTH|||Also - you don't need a loop:

DECLARE @.myString AS VarChar(1000)

SELECT @.myString = ''

SELECT @.myString = @.myString + ', ' + companyName
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 3, LEN(@.myString)-2)

SELECT @.myString|||This code will handle if any of the companyName is null.

DECLARE @.myString AS VarChar(1000)
SELECT @.myString = ''

SELECT @.myString = @.myString + coalesce(companyName+',','')
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 1, LEN(@.myString)-1)

SELECT @.myString|||Nice one thanks a lot! A final obsticle and I'm there!

As I need to send a list of action items to my users, I need to add a carriage return at the end of each line, any idea how I would do that, the mail is sent as a text file so I assume I'll need the CTRL + M combination or in ASCII:

Char: CR, Oct:15, Dec:13, Hex:d

Any ideas anyone?|||Hmm wasnt that tricky I needed CHAR(13) + CHAR(10) cool, thanks for all the help again!|||add char(13) with ur string.
eg:

PRINT 'First line.' + CHAR(13) + CHAR(10) + 'Second line.'|||Thanks yes I noticed I had to include a string to add CHAR(13) + CHAR(10) to. Cheers!