Is the table structure to store addresses somewhat standardized?
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
--
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country.
> State is specific to US, in Canada they have provinces, other contries have
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> > Is the table structure to store addresses somewhat standardized?
> >
> > Address1
> > Address2
> > City
> > State
> > ZipCode
> > Country
> >
> > I ask because I am working with international addresses for the first time
> > and I don't know what to expect in terms of what my users will want to
> > enter.
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>> Not really, it varies from database to database. The best thing you can
>> do
>> is make it somewhat generic and not make any assumptions about any
>> country.
>> State is specific to US, in Canada they have provinces, other contries
>> have
>> regions, districts, you name it. I think this structure might be more
>> generic and applicable to many countries:
>> Address1
>> Address2
>> Address3
>> City
>> PostalCode
>> Region
>> Country
>> Of course, address formatting varies from country to country.
>> Roman
>> --
>> Roman Rehak
>> http://sqlblog.com/blogs/roman_rehak
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>> > Is the table structure to store addresses somewhat standardized?
>> >
>> > Address1
>> > Address2
>> > City
>> > State
>> > ZipCode
>> > Country
>> >
>> > I ask because I am working with international addresses for the first
>> > time
>> > and I don't know what to expect in terms of what my users will want to
>> > enter.
>>sql
Showing posts with label store. Show all posts
Showing posts with label store. Show all posts
Tuesday, March 27, 2012
Address table structure
Is the table structure to store addresses somewhat standardized?
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter
.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country
.
> State is specific to US, in Canada they have provinces, other contries hav
e
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...[vbcol=seagreen]
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter
.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country
.
> State is specific to US, in Canada they have provinces, other contries hav
e
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...[vbcol=seagreen]
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>
Thursday, March 22, 2012
Adding to outputs together to retrieve the top(10) - Is it possible?
Hi There,
I have been struggling day and night with the creation of a store procedure due to not being able to retieve the rows I need for SUM and AVG functions.
I have two tables ('actions' & 'incident_types') which both have a score value for each record. In my database, I have reports that contain both action codes and incident_type codes based on a personnel_code.
In simple terms I'm trying to do the following:-
For all reports, find each personnel member and thier attached incident_types and action codes and then, for each score from the actions and incident_types tables, create the SUM of the 'combined' scores.
I have successfully retrieved the output for the scores individually but I need the TOP(10) of the combined output.....
I'm currently using to seperate queries as follows :--
--This gives me the incident type output...... Creating the 'Volume' column which is the total Score for incidetn_types
SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(incident_types.type_score) as 'Volume', avg(incident_types.type_score) as 'Average' INTO 'Incident_Scores' from Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
-- I then use the following to get the total of all action scores, again in the 'Volume' column
SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(actions.action_score) as 'Volume', AVG(actions.action_score) as 'Average' from profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname
So my question is - How can I get the sum(incident_types.type_score) + sum(actions.action_score) and then get the TOP(10) rows?
Is it possible to output these 2 result to a new table and then join the new tables?
Thanks for any assistance, this is really draining me at the moment..... :-/
here it is,
Select TOP (@.Number)
incident_Data.personnel_code
, incident_Data.personnel_forename
, incident_Data.personnel_Surname
, incident_Data.Volume
, incident_Data.Average
, actions_data.Volume
, actions_data.Average
, incident_Data.Volume + actions_data.Volume as Total_Volume
, incident_Data.Average + actions_data.Average as Total_Average
from
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(incident_types.type_score) as 'Volume'
, avg(incident_types.type_score) as 'Average'
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as incident_Data
Inner Join
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(actions.action_score) as 'Volume'
, AVG(actions.action_score) as 'Average'
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as actions_data
On actions_data.personnel_code = incident_Data.personnel_code
|||
Thank you so much for your reply this has really made my day!
I forgot to mention that the action record is not always present but the incident record is. Therefore I made the actions_data join = 'Left Outer Join' (see code below)... This gives me a problem in that when the action_data record contains 'NULL' the Volume is also 'NULL' - is it possible to make NULL = 0 [zero] when there is no action record?
Thanks again (see code and output).....
[code I've removed some of the selection criteria from previous post for testing purposes]
Select TOP (1000)
incident_Data.personnel_code
, incident_Data.personnel_forename
, incident_Data.personnel_Surname
, incident_Data.Volume
, incident_Data.Average
, actions_data.Volume
, actions_data.Average
, incident_Data.Volume + actions_data.Volume as Total_Volume
, incident_Data.Average + actions_data.Average as Total_Average
from
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(incident_types.type_score) as 'Volume'
, avg(incident_types.type_score) as 'Average'
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
report_basedon.personnel_code <> 0
AND report_header.record_status='1'
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as incident_Data
Left Outer Join
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, sum(actions.action_score) as 'Volume'
, AVG(actions.action_score) as 'Average'
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
personnel_details.personnel_code <> 0
AND report_header.record_status='1'
AND profile.record_status='1'
group by
Personnel_details.personnel_code,
Personnel_details.personnel_forename,
Personnel_details.personnel_Surname
) as actions_data
On actions_data.personnel_code = incident_Data.personnel_code
Order by incident_data.personnel_code
[example of results]
15 Oscar Freeman 0 0 NULL NULL NULL NULL16 Sofia Daniels 0 0 NULL NULL NULL NULL
19 Megan Hardy 25 12 120 40 145 52
20 Finley Randall -25 -25 NULL NULL NULL NULL|||
observations
1. since actions_data subquery may be missing [i.e. OUTER JOIN] you should use this line instead
, incident_Data.Volume + ISNULL(actions_data.Volume,0) as Total_Volume
, incident_Data.Average + ISNULL(actions_data.Average,0) as Total_Average
2. the Total_Average calculation is flawed, e.g.
4 rows of incident_Data with average of 50 and 1 row of actions_data with average of 20 would yield a Total_Average of 70
when what you probably wanted was 44 (or 35 for simplicity if you don't care about weighting)
Dick
|||Thank you Dick, for both points, your absolutly correct. I was so concerned with not being ale to extract the data that I hadn't thought this though. You've propably saved me a lot more pain!
Just for completeness, I had to go on and resolve a divide by zero error when calculating the final averages etc etc and I found a very useful link http://www.sql-server-helper.com/error-messages/msg-8134.aspx which shows 3 ways of dealing with this error.
Thanks again for everyones help - I can now get on with developing the program!!!!
|||
Yes. ISNULL will fix the issue... If the average is your issue the following query might be a best one..
Not sure why we need a TOP here.. It wont give any performance gain.
Select TOP (@.Number)
personnel_code
, personnel_forename
, personnel_Surname
, sum(case when type='incident' then Score end) as incident_volume
, avg(case when type='incident' then Score end) as incident_average
, sum(case when type='actions' then Score end) as actions_volume
, avg(case when type='actions' then Score end) as actions_average
, sum(Score) as Total_Volume
, avg(Score) as Total_Average
From
(
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, (incident_types.type_score) as 'Score'
, 'incident' Type
from
Report_header
JOIN incident_types on incident_types.type_code = report_header.report_incident_Code
JOIN report_basedon on report_basedon.report_code = report_header.report_code
JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND report_basedon.personnel_code <> 0
AND report_header.record_status=@.recordStatus
UNION ALL
SELECT
Personnel_details.personnel_code
, Personnel_details.personnel_forename
, Personnel_details.personnel_Surname
, (actions.action_score) as 'Score'
, 'actions' Type
from
profile
JOIN actions on actions.action_code = profile.action_code
JOIN report_header on report_header.report_code = profile.incident_ID
JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code
WHERE
(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)
AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)
AND personnel_details.personnel_code <> 0
AND report_header.record_status=@.recordStatus
AND profile.record_status=@.recordStatus
) as data
Group By
personnel_code
, personnel_forename
, personnel_Surname
Sunday, March 11, 2012
Adding pictures to my Database
Hello everyone!,
I'm a new user of SQL 2000 and I would like to build a database that contains pictures. This database will be used to store information regarding my products and of course I would like to show the user what I'm referring to. Any and
replies are welcomed!
Santiago Ortiz
Santiago,
many people just hold the image paths in varchar fields and the actual files
on the OS, but if you do ant to do this programatically, these articles will
help:
HOWTO: Retrieve and Update a SQL Server Text Field Using ADO
http://support.microsoft.com/?kbid=180368
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?kbid=258038
Using Textcopy.exe
http://www.databasejournal.com/featu...le.php/1443521
HTH,
PAul Ibison
|||Santiago,
many people just hold the image paths in varchar fields and the actual files
on the OS, but if you do ant to do this programatically, these articles will
help:
HOWTO: Retrieve and Update a SQL Server Text Field Using ADO
http://support.microsoft.com/?kbid=180368
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?kbid=258038
Using Textcopy.exe
http://www.databasejournal.com/featu...le.php/1443521
HTH,
PAul Ibison
I'm a new user of SQL 2000 and I would like to build a database that contains pictures. This database will be used to store information regarding my products and of course I would like to show the user what I'm referring to. Any and
replies are welcomed!
Santiago Ortiz
Santiago,
many people just hold the image paths in varchar fields and the actual files
on the OS, but if you do ant to do this programatically, these articles will
help:
HOWTO: Retrieve and Update a SQL Server Text Field Using ADO
http://support.microsoft.com/?kbid=180368
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?kbid=258038
Using Textcopy.exe
http://www.databasejournal.com/featu...le.php/1443521
HTH,
PAul Ibison
|||Santiago,
many people just hold the image paths in varchar fields and the actual files
on the OS, but if you do ant to do this programatically, these articles will
help:
HOWTO: Retrieve and Update a SQL Server Text Field Using ADO
http://support.microsoft.com/?kbid=180368
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?kbid=258038
Using Textcopy.exe
http://www.databasejournal.com/featu...le.php/1443521
HTH,
PAul Ibison
Adding pictures to my Database
Hello everyone!,
I'm a new user of SQL 2000 and I would like to build a database that contain
s pictures. This database will be used to store information regarding my pro
ducts and of course I would like to show the user what I'm referring to. Any
and
replies are welcomed!
Santiago OrtizSantiago,
many people just hold the image paths in varchar fields and the actual files
on the OS, but if you do ant to do this programatically, these articles will
help:
HOWTO: Retrieve and Update a SQL Server Text Field Using ADO
http://support.microsoft.com/?kbid=180368
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?kbid=258038
Using Textcopy.exe
http://www.databasejournal.com/feat...cle.php/1443521
HTH,
PAul Ibison
I'm a new user of SQL 2000 and I would like to build a database that contain
s pictures. This database will be used to store information regarding my pro
ducts and of course I would like to show the user what I'm referring to. Any
and
replies are welcomed!
Santiago OrtizSantiago,
many people just hold the image paths in varchar fields and the actual files
on the OS, but if you do ant to do this programatically, these articles will
help:
HOWTO: Retrieve and Update a SQL Server Text Field Using ADO
http://support.microsoft.com/?kbid=180368
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?kbid=258038
Using Textcopy.exe
http://www.databasejournal.com/feat...cle.php/1443521
HTH,
PAul Ibison
Adding pictures to my Database
Hello everyone!,
I'm a new user of SQL 2000 and I would like to build a database that contains pictures. This database will be used to store information regarding my products and of course I would like to show the user what I'm referring to. Any and replies are welcomed!
Santiago OrtizSantiago,
many people just hold the image paths in varchar fields and the actual files
on the OS, but if you do ant to do this programatically, these articles will
help:
HOWTO: Retrieve and Update a SQL Server Text Field Using ADO
http://support.microsoft.com/?kbid=180368
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?kbid=258038
Using Textcopy.exe
http://www.databasejournal.com/features/mssql/article.php/1443521
HTH,
PAul Ibison
I'm a new user of SQL 2000 and I would like to build a database that contains pictures. This database will be used to store information regarding my products and of course I would like to show the user what I'm referring to. Any and replies are welcomed!
Santiago OrtizSantiago,
many people just hold the image paths in varchar fields and the actual files
on the OS, but if you do ant to do this programatically, these articles will
help:
HOWTO: Retrieve and Update a SQL Server Text Field Using ADO
http://support.microsoft.com/?kbid=180368
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/?kbid=258038
Using Textcopy.exe
http://www.databasejournal.com/features/mssql/article.php/1443521
HTH,
PAul Ibison
Tuesday, March 6, 2012
Adding new column from inside a store procedure....
My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULL
The only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
.... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>
|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULL
The only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
.... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>
|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)
Adding new column from inside a store procedure....
My question is...
Is there any way to add a new column dinamically inside a store procedure wh
it the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I wil
l need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach t
o
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure
whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I w
ill need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)
Is there any way to add a new column dinamically inside a store procedure wh
it the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I wil
l need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach t
o
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure
whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I w
ill need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)
Adding new column from inside a store procedure....
My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
--
http://www.aspfaq.com/
(Reverse address to reply.)
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
--
http://www.aspfaq.com/
(Reverse address to reply.)
Sunday, February 12, 2012
Adding an incremental number to select result
Hi,
I will like to have, along with my quesry result, another
column that will store the number of the row, for example:
1 query results...
2 query results...
3 query results...
how can i do this?
Thanks!Refer to following url
HOW TO: Dynamically Number Rows in a Select Statement
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
if you have any unique field in the table then you can try a query something
like this:
Ex:
use northwind
go
select (select count(customerid) from customers where customerid <=a.customerid) rank,
*
from customers a
go
--
-Vishal
"Juan Carlos" <jcarlos_mn@.hotmail.com> wrote in message
news:0bf201c34249$de23a670$a501280a@.phx.gbl...
> Hi,
> I will like to have, along with my quesry result, another
> column that will store the number of the row, for example:
> 1 query results...
> 2 query results...
> 3 query results...
> how can i do this?
> Thanks!|||http://www.aspfaq.com/2427
--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/
Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.
"Juan Carlos" <jcarlos_mn@.hotmail.com> wrote in message
news:0bf201c34249$de23a670$a501280a@.phx.gbl...
> Hi,
> I will like to have, along with my quesry result, another
> column that will store the number of the row, for example:
> 1 query results...
> 2 query results...
> 3 query results...
> how can i do this?
> Thanks!
I will like to have, along with my quesry result, another
column that will store the number of the row, for example:
1 query results...
2 query results...
3 query results...
how can i do this?
Thanks!Refer to following url
HOW TO: Dynamically Number Rows in a Select Statement
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q186133
if you have any unique field in the table then you can try a query something
like this:
Ex:
use northwind
go
select (select count(customerid) from customers where customerid <=a.customerid) rank,
*
from customers a
go
--
-Vishal
"Juan Carlos" <jcarlos_mn@.hotmail.com> wrote in message
news:0bf201c34249$de23a670$a501280a@.phx.gbl...
> Hi,
> I will like to have, along with my quesry result, another
> column that will store the number of the row, for example:
> 1 query results...
> 2 query results...
> 3 query results...
> how can i do this?
> Thanks!|||http://www.aspfaq.com/2427
--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/
Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.
"Juan Carlos" <jcarlos_mn@.hotmail.com> wrote in message
news:0bf201c34249$de23a670$a501280a@.phx.gbl...
> Hi,
> I will like to have, along with my quesry result, another
> column that will store the number of the row, for example:
> 1 query results...
> 2 query results...
> 3 query results...
> how can i do this?
> Thanks!
Subscribe to:
Posts (Atom)