|
I would read that as ch has a value of the empty set; it is a zero length string.
|
|
|
|
|
In youer example @ch would be an empty (zero length) string. If you want it to be null then
SET @ch = NULL
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
a string of length zero
Niladri Biswas
|
|
|
|
|
Hi I need to create a stored procedure that changes the column name of a column in a datatable. Can someone help please? Thanks
|
|
|
|
|
You might want to give more information. As it is, changing the name of a column as you have it stated would be a one time operation.
Alter Column [column_name]
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
There is a stored procedure available in the System Stored Procedures in
Sql Server. It is called sp_rename
It accepts 3 parameters viz.
@objname nvarchar(1035), -- up to 4-part "old" name
@newname sysname, -- one-part new name
@objtype varchar(13) = null -- identifying the name
For renaming a column, execute the following
EXEC sp_rename
@objname= 'TABLE_NAME.OLD_COLUMN_NAME',
@newname = 'NEW_COLUMN_NAME',
@objtype = 'COLUMN'
N.B.~ For your reference, the @objecttype correspond to
system tables which track of the folowing type:
'column' 'database' 'index' 'object' 'userdatatype'
Hope this helps
Vote me
Niladri Biswas
|
|
|
|
|
Hi,
How to return value in the following format
Name Name Name Name
N1...N2....N3....N4
1....2 .... 4 ....7
3....1 .... 8 ....7
4....8..... 5.... 7
7....5......3.....8
the numbers can be consider as the list of tokens for the N1,N2..etc
The table structure is
N1.1
N1.3
N1.4
N1.7
N2.2
N2.1
N2.8
N2.5
N3.4
N3.8
N3.5
N3.3
.
.
.
Thankyou
YPKI
|
|
|
|
|
It's called a pivot table and SQL 2005+ supports it here is an article [^]about it
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
PIVOT expects aggregate function
Niladri Biswas
modified on Tuesday, June 30, 2009 12:01 AM
|
|
|
|
|
Use MAX() or MIN() as the aggregate
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
Create a table
CREATE TABLE mytable (
Name1 VARCHAR(10)
,Name2 VARCHAR(10)
,Name3 VARCHAR(10)
,Name4 VARCHAR(10)
)
Next insert some records
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( 'N1', 'N2', 'N3', 'N4' )
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '1', '2', '4', '7' )
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '3', '1', '8', '7' )
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '4', '8', '5', '7' )
INSERT INTO mytable (Name1, Name2, Name3, Name4) VALUES ( '7', '5', '3', '8' )
Fire the below query
SELECT *
FROM mytable
;
WITH unpvt
AS ( SELECT col1,
col2
FROM mytable UNPIVOT ( col2 FOR col1 IN ( [Name1], [Name2], [Name3], [Name4] ) ) AS unpvt
) ,
Result1
AS ( SELECT col1 AS NameList_pre,
col2 AS NameList
FROM unpvt
WHERE col2 LIKE 'N%'
) ,
Result2
AS ( SELECT col1 AS NameList_pre,
col2 AS Tokens
FROM unpvt
WHERE col2 NOT LIKE 'N%'
)
SELECT Result1.NameList,
Result2.Tokens
FROM Result2
INNER JOIN Result1 ON Result1.NameList_pre = Result2.NameList_pre
ORDER BY Result1.NameList,
Result2.Tokens
Output:
Name1 Name2 Name3 Name4
N1 N2 N3 N4
1 2 4 7
3 1 8 7
4 8 5 7
7 5 3 8
Niladri Biswas
modified on Monday, June 29, 2009 11:56 PM
|
|
|
|
|
HI,
I am a newbie to programming,I am developing a webapplication in order to learn about sql and asp.net/C#.In my application there are five users which can access the application my application will be running on IIS on lan, The application is simply inserting,deleting,modifying record in a table.Now let us suppose:
1)three users all of them on different systems are inserting different records in the table simultaneously.
2)fourth and fifth are viewing the same record simultaneously(easily identified by a unique database id)but fourth is modifying the record whereas fifth is simply deleting the same record at the same time,what will happen?will an error occur?how to handle that error?
also please tell what are stored procedures used for(in this context)?
thanks
with regards
shankbond
|
|
|
|
|
|
use Transaction in your Program. use either SQL Transaction or Front End C#.Net Transaction
|
|
|
|
|
I didn't understand
|
|
|
|
|
in Front End Like C#
Sqlcommand cmd=new Sqlcommand()
SqlTransaction myTrans;
myTrans = myConnection.BeginTransaction();
cmd.Transaction = myTrans;
try
{
myTrans.Commit();
}
catch(Exeception ex)
{
myTrans.Rollback();
}
|
|
|
|
|
I am having the below secnario.
Table 1
Col1 Col2 Col3
10 20 30
Table 2
Code Name
1 Col1
2 Col2
3 Col3
Expected Output:
1 Col1 10
2 Col2 20
3 Col3 30
How to write the SQL script (SQL server 2005) for this? Thanks in advance.
Jey
|
|
|
|
|
Are you sure about the values in Table 1 ?
From your description, there is one row with three columns where col1 contains a value of 10, col2 contains a value of 20 and col3 contains a value of 30.
This is not a typical relational database method for storing data. You may want to re-check your homework assignment.
BTW: Most people won't help you with your homework.
|
|
|
|
|
Hi,
Thanks for your reply.
I do aware of it. But my requirement is similar like this.
I needs to get all the column names from the Table 1 & lookup on Table 2 and get the Code of that respective Column name.
Let me explain: In table 1 there is a column called 'LoadFactor' & its value is 80
LoadFactor
80
Table 2
Code Name
1 LoadFactor
Table 3
Code LSL USL
1 50 100
My Required Output:
Code Name 'Actual Value' LSL USL
1 LoadFactor 80 50 100
Now. have u got it? This is a simple example that explains my requirement. I do have 500 similar columns with few input tables.
Jey
|
|
|
|
|
I think you want to access system tables that will allow you to get the names of columns for a given table.
For example, the following will give you a listing of all of the columns for "myTable1". You could then expand this query to join to your other tables.
Use myDatabase
GO
select so.name,sc.name
from sysobjects so, syscolumns sc
where so.xtype = 'U'
and so.name = 'myTable1'
and so.id = sc.id
Tell me if this helps you.
|
|
|
|
|
Thank you Mujica. Definately It helps me... Thanks a lot.
Please find my last comment for the complete solution.
Jey
|
|
|
|
|
Your table data and structure doesn't seems to me good enought logical.
What if Table1 have different values? What if Table1 contain these values :
Col1 Col2 Col3
10 20 30
100 200 300
1000 2000 3000
10000 20000 30000
and so on.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi, Thanks for your valuable reply.
Please assume that I am always select one row from Table 1.
Please refer my previous thread for the detailed requirement.
Jey
|
|
|
|
|
SQL Server 2005 introduced an UNPIVOT operator that will do what you're looking for.
SELECT T2.CODE, T1.COLS,T1.INDICOLVALS
FROM
(
SELECT INDICOLVALS,COLS
FROM
(SELECT Col1, Col2, Col3 FROM Table1) P
UNPIVOT
(INDICOLVALS FOR COLS IN (Col1, Col2, Col3)) AS U
) AS T1
INNER JOIN TABLE2 T2
ON T1.INDICOLVALS = 10*T2.CODE
Vote me please
Niladri Biswas
|
|
|
|
|
Great Work I am really thankful for your effort spent on this & guidance
I have used this logic for my case. BUT, the JOIN is the highlighting one Sorry, this is for fun.
ON T1.INDICOLVALS = 10*T2.CODE
declare @cols nvarchar(2000)
declare @sql nvarchar(4000)
set @cols='Col1, Col2, Col3'
set @query='SELECT T2.CODE,
T1.COLS,
T1.INDICOLVALS
FROM
(
SELECT INDICOLVALS,COLS FROM
(SELECT Col1, Col2, Col3 FROM Table1) P
UNPIVOT
(INDICOLVALS FOR COLS IN ('+ @cols +')
) AS U
) AS T1
INNER JOIN TABLE2 T2
ON T1.COLS = T2.[Name]'
--print @query
execute(@query)
As the list of columns may change in future, we have used the below script to get the list of columns and using the CURSOR, it is stored in the @cols variable with comma seperated format.
Select name from syscolumns where id=object_id('Table 1')
Once Again, Thank You very much.
Jey
modified on Wednesday, June 17, 2009 11:03 AM
|
|
|
|
|