how to asssign string variable to SqlDbType

i have string variable as,

String str="Int";

now, while assigning sql parameters, i want

param.SqlDBType=SqlDBType.Int;

but, value of Int is dynamic. it may be string or double so,

i want it to be as,

param.SqlDBType=(SqlDBType)str;

but its not acceptable(its invalid cast).

in any way can i do it and how?

regards--

The SqlDBType is not the value you are passing to the database, rather it is the data type. Therefore this must match that of your table column data type.

If your column is a varchar you would assign SqlDBType.Varchar or of it is an Int you would assign SqlDBType.Int.

To assign the actual value to the parameter you use the Value property.

e.g. param.Value = <the value you want to assign to the parameter>

How to alter(add) a Table with a default value and by allowing Nulls?

Hi

I am using this query to alter a table

ALTER TABLE myTable ADD age int NULL DEFAULT(0)

But above query is adding age field by storing Nulls but not with default values

So I need to add age field to the table by storing default value as 0 and by allowing Nulls

Please advice

Thanks

Use the below query,

Code Snippet

Create table #Mytable

(

Id int,

Name varchar(100)

)

Insert Into #Mytable values(1,'One');

Insert Into #Mytable values(2,'Two');

Insert Into #Mytable values(3,'Three');

Alter table #MyTable Add Age int NOT NULL Default(0)

Alter table #MyTable Alter Column Age int NULL;

select * from #MyTable

Insert Into #Mytable(Id,Name) values(4,'Four');

select * from #MyTable

|||

Thanks Mani

I got my answer

and

Can't we do it in a single step in Sql Server?

|||

Yes, we have it,

Code Snippet

Alter table #MyTable Add Age int NULL Default(0) WITH VALUES

|||

Thanks a lot Mani, That's what I'm talking about.

With Regards

Vijay

how to ALTER COLUMN ?

Hi,
i do a create:
CREATE TABLE myTable ( id int IDENTITY(1,1) NOT NULL)
and now i want to remove the IDENTITY definition from colum id
using ALTER myTable ALTER COLUMN id ... '
so that column id has definition as when i do a:
CREATE TABLE myTable ( id int NULL)
How can i do this?
thanks, HelmutHelmut
CREATE TABLE Test (col INT NOT NULL IDENTITY(1,1))
GO
insert into Test default values
ALTER TABLE Test ADD col1 INT NULL
GO
ALTER TABLE Test DROP COLUMN col
GO
EXEC sp_rename 'TEST.COL1', 'COL', 'COLUMN'
Note: If you had some data inserted into the table before addin a new column
and need to move it ,so use UPDATE command as
UPDATE Test SET col1=col
"Helmut Woess" <user22@.inode.at> wrote in message
news:dzg357eusfly$.19hvp25nnxfh1.dlg@.40tude.net...
> Hi,
> i do a create:
> CREATE TABLE myTable ( id int IDENTITY(1,1) NOT NULL)
> and now i want to remove the IDENTITY definition from colum id
> using ALTER myTable ALTER COLUMN id ... '
> so that column id has definition as when i do a:
> CREATE TABLE myTable ( id int NULL)
> How can i do this?
> thanks, Helmut|||I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
to loose an entire table because of that command. The table in question had
17million rows. So be carful with it.
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eIFAYrEbGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Helmut
> CREATE TABLE Test (col INT NOT NULL IDENTITY(1,1))
> GO
> insert into Test default values
> ALTER TABLE Test ADD col1 INT NULL
> GO
> ALTER TABLE Test DROP COLUMN col
> GO
> EXEC sp_rename 'TEST.COL1', 'COL', 'COLUMN'
> Note: If you had some data inserted into the table before addin a new
> column and need to move it ,so use UPDATE command as
> UPDATE Test SET col1=col
>
> "Helmut Woess" <user22@.inode.at> wrote in message
> news:dzg357eusfly$.19hvp25nnxfh1.dlg@.40tude.net...
>|||Colin
This stored procedure is documented and supported officialy by MS .

>I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
>to loose an entire table because of that command. The table in question
>had 17million rows. So be carful with it.
I just did some testing on the table that has 50 million rows and it worked
nice. Can you provide a repro where you are loosing an entire of table?
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:vJ15g.61831$wl.42277@.text.news.blueyonder.co.uk...
>I hate the sp_rename command. Using SQL2000 and SQL2005 Beta 2, I managed
>to loose an entire table because of that command. The table in question
>had 17million rows. So be carful with it.
> Regards
> Colin Dawson
> www.cjdawson.com
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eIFAYrEbGHA.4060@.TK2MSFTNGP02.phx.gbl...
>|||Yes, I know. I was at the MS Labs when the table got lost. Fortunately I
had a backup of the table, so I retried and lost the table again, and a
third time. Bottom line is, that if you try to rename a large table there
is a chance that you will loose it completely.
If you want to take your chances and risk loosing data on a production
system - or more to the point, creating unneeded downtime if you've been
cautious, and sensible enough to backup before hand. Then go ahead.
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uCabo8EbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Colin
> This stored procedure is documented and supported officialy by MS .
>
> I just did some testing on the table that has 50 million rows and it
> worked nice. Can you provide a repro where you are loosing an entire of
> table?
>
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:vJ15g.61831$wl.42277@.text.news.blueyonder.co.uk...
>

how to add primary key in existing table

i have table fff .it has two fields one is fno int , another is fname
varchar(20)
fff
fno fname
--- ----
100 suresh
102 ramesh
here there is no not null constraint and identity column then
i am add primary key constraint fno column pls help mesurya (suryaitha@.gmail.com) writes:

Quote:

Originally Posted by

i have table fff .it has two fields one is fno int , another is fname
varchar(20)
fff
fno fname
--- ----
100 suresh
102 ramesh
here there is no not null constraint and identity column then
i am add primary key constraint fno column pls help me

ALTER TABLE fff ADD CONSTRAINT pk_fff PRIMARY KEY (fno)

You find the syntax for ALTER TABLE in Books Online. Yes, it is quite
complex. Then again, there are quite a few examples in that topic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

how to add autonumber (increment) unique ID to sqlserver

Hi,

I am a newbie learning sql server. I used to use Access table where I set the primary ID (int) as autonumber. Every insert will generate a new ID, which is incremented by 1.

How do I add an INSERT statement to add the incrementing primary ID in sql server. I noticed that you can't set the ID to autonumber as you do in Access. I read that you can set the ID as identity, which can auto increment.

How do I handle this in the INSERT cmd. The only thought is to call a sql cmd with a max on the current id and increment by 1 in vbscript. Afterwards, add the increment ID into the INSERT call.

Is there an easier way to do this?

Thanks,

JohnHi John,

Correct. You can't write to an identity field (and I didn't know you could in Access). You don't write to it, so don't include that field in your insert statement.

If you need the new ID number, return the value from the SQL Server SCOPE_IDENTITY function after you insert the row, either by returning it from your stored procedure or immediately after doing the insert. And you can batch them together so that you have only one round trip to the SQL Server server.

And there are lots of ways to do this, depending on the needs of your application.

Is that enough information? If not, just ask. It's pretty straightforward once you get to know SQL Server a bit.

Don

how to add a where clause by parameter in a stored procedure

What i want is to add by parameter a Where clause and i can not find how to do it!
CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 Where fieldId = @.id /*and @.WhereClause */
GO

thx
What error did you get? That sproc defintion looks fine to me apart from the fact that you've missed the FROM clause from the parameterised SQL statement.

-Jamie|||thx for the reply.

You are right. I forgot the from clause.

CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 FROM Table1 Where fieldId = @.id /*and @.WhereClause */
GO
I get only a syntax error when checking the procedure syntax|||

SQL Server does not support "parameterizing" the WHERE clause or any syntactic construct.

You have to construct the SQL String using string concatenation operations and then execute the constructed string using the dynamic EXEC statement.

CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
BEGIN
DECLARE @.mdstring nvarchar(300);
set @.cmdstring = "SELECT field1, field2 FROM T WHERE fieldID = " + @.id +
" AND " + @.whereClause;
EXEC(@.cmdstring);
END
GO

|||Personally, I dislike the above mentioned method. Besides the fact that it opens you to all sorts of bad stuff.

What you can do is make the parameters nullable, and do a case statement on the where part of the clause:

create procedure pSomethingOrAnother
(
@.p_lID integer,
@.p_lWhereItem1 integer,
@.p_sWhereItem2 varchar(50)
)
as

set nocount on

select
t.Field1,
t.Field2
from
TableName t
where
t.FieldID = @.p_lID
and (case when ISNULL(@.p_lWhereItem1, 0) = 0 then 0 else t.FieldToCheck1 end) = ISNULL(@.p_lWhereItem1, 0)
and (case when ISNULL(@.p_lWhereItem2, '') = '' then '' else t.FieldToCheck2 end) = ISNULL(@.p_sWhereItem2, '')

set nocount off

No magic values, no string concatenation, no million if statements. The only time this won't work so well is when you are looking for the value NULL in a field, but you can separate those instances out with an IF statement for that field.

how to add a where clause by parameter in a stored procedure

What i want is to add by parameter a Where clause and i can not find how to do it!
CREATE PROCEDURE [ProcNavigate]
(
@.id as int,
@.whereClause as char(100)
)
AS
Select field1, field2 from table1 Where fieldId = @.id /*and @.WhereClause */
GO
any suggestion?What you're trying to do can only be done using dynamic SQL. Dynamic SQL is a pretty large topic, so here are a few links to get youstarted:
http://www.databasejournal.com/features/mssql/article.php/1438931
http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sommarskog.se/dynamic_sql.html