I'm using a stored procedure that receives one parameter namely @.EmployeeID INT
but when I want to give this parameter a default value, my SP fails.
I did it like with a varchar where it works.
--
@.Employee INT = '%'
--
Is this correct or did I use a wrong syntac/wildcard?
Greetings,
GeoffINT is a numeric datatype only you should use digits to assign defaults.
If not use VARCHAR to assign such special chars.|||Isn't there a numeric default that I can use?
actually i use the SP to do the next thing.
If I don't give a parameter I want all recoreds to be returned. When passing through the ID (as parameter tot the SP) I want only that record to be returned. So if I understand correctly you're saying there is no sucth thing as setting a default for an int-type?
Greetings,
Godofredo|||Only numerics are allowed as default for int.|||Can i define a range then?
like @.EmployeeID INT = [0-9]
so that all numbers can be received? or how precisely do I do this?
If no parameter is given I want al records returned.
Greetings,
Geoff|||CREATE PROCEDURE sp_myproc
@.emp_id int = 0
as
select * from my_table
where emp_id =
case @.emp_id when 0 then emp_id else @.emp_id end
when you call sp_myproc without specifying any parameters then all employees are retrieved.
Is that what you were asking?|||Yes indeed
Showing posts with label int. Show all posts
Showing posts with label int. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Default Constraints (call: User-Defined Function or proc)
Hi
Have any way to call User-Defined Function or proc of a Default Constraint'
INT CONSTRAINT test_df DEFAULT dbo.myFunc()
ThaksAccording to the SQL BOL, a user-defined function can be used in a DEFAULT
clause. A stored procedure - not that I am aware of.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>|||Hi
Here is an example of a udf:
CREATE FUNCTION SetDate ()
RETURNS Datetime
AS
BEGIN
DECLARE @.Startdate Datetime
SET @.Startdate = '20050101'
RETURN @.Startdate
END
CREATE TABLE MyTest ( id int not null identity(1,1) Primary key,
name varchar(10) NOT NULL,
StartDate datetime not null default dbo.SetDate(),
EndDate datetime not null default getdate()+1
)
INSERT INTO MyTest ( name ) VALUES ( 'John')
SELECT * FROM MyTest
/*
id name StartDate
EndDate
-- -- ----
-
--1 John 2005-01-01 00:00:00.000
2005-09-25 15:38:48.220
(1 row(s) affected)
*/
John
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>
Have any way to call User-Defined Function or proc of a Default Constraint'
INT CONSTRAINT test_df DEFAULT dbo.myFunc()
ThaksAccording to the SQL BOL, a user-defined function can be used in a DEFAULT
clause. A stored procedure - not that I am aware of.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>|||Hi
Here is an example of a udf:
CREATE FUNCTION SetDate ()
RETURNS Datetime
AS
BEGIN
DECLARE @.Startdate Datetime
SET @.Startdate = '20050101'
RETURN @.Startdate
END
CREATE TABLE MyTest ( id int not null identity(1,1) Primary key,
name varchar(10) NOT NULL,
StartDate datetime not null default dbo.SetDate(),
EndDate datetime not null default getdate()+1
)
INSERT INTO MyTest ( name ) VALUES ( 'John')
SELECT * FROM MyTest
/*
id name StartDate
EndDate
-- -- ----
-
--1 John 2005-01-01 00:00:00.000
2005-09-25 15:38:48.220
(1 row(s) affected)
*/
John
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>
Default constraints
Hello
I have about 40 tables where I need to increase the size of the admission number field from smallint to Int . The field used the tables' primary key or part of the key. I am a programmer, but fairly new to SQL Server. I have written some scripts to remove the defaults and primary key constraints off of this field in each table, do the field resize and then put the constraints back on. The scripts get the names and settings of the constraints from system tables before the constraints are dropped, so that they can be reapplied after the field size change.
Is this the best way to do it? Or should I be looking at a DTS package?
I would be grateful for your advice
Shirley
To me (unless you have a database design tool), this sounds like a sufficient way to do it. It is never perfectly easy or anything, but you can get most everything you need to generate a script using the system tables, so I would do that. Sounds messy of course, since you also have to deal with foreign key constraints, but if it is just 40 tables, that probably isn't too bad. (I assume you will make ths size plenty big for the next sixty years this time :)
|||Thanks very much for that. Yes, Integer will definitely be a big enough field size for the forseeable future for our admission number!
Shirley
Wednesday, March 21, 2012
Default additional field to identity value?
I've got a record that has an identity column and secondary identifier that I need to have default to the same value as the identity column:
id int identity(1,1) not null ,
name varchar(20) not null default CAST($IDENTITY AS VARCHAR(20))
Problem is, using @.@.identity or scope_identity() as the default for name gives me the prior insert's identity value, not the current record's value. Using an AFTER trigger doesn't work because the initial insert fails due to the not null constraint, and using an INSTEAD OF trigger does not work because the identity value is not set on the inserted row.
Is there any way to set a not-null field on a record equal to the identity value assigned to the record?
There is no way to do this declaratively. You can use a computed column instead of a persisted column if the name column is just string representation of the identity value. Do you allow the name value to be modified later? If so then you will have to use a trigger to update the value and set default to 0 or -1.sqlSunday, March 11, 2012
declare variables
New to stored procedures. Is it necessary to place a default value into a
variable at the time you declare it?
SAMPLE: “ @.Sec int = 100”
Can @.Sec just be declared?It can just be declared. It will default to NULL:
DECLARE @.SEC INT
SELECT @.SEC
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:10D3C4D8-9FD7-47E0-B77E-DD1C1A4EE8CA@.microsoft.com...
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: " @.Sec int = 100"
> Can @.Sec just be declared?
>|||No, it is not necessary, but until you put a value into it, it's value will
be Null. This can bite you if it's a char() or varChar() because by defaul
t
(there's a setting to change this, but don't use it) the nulls propagate whe
n
you concatenate them... i.e.,
null + 'dsasdasd' is null
"Rich" wrote:
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:548AE03F-A9DD-441E-98AD-5F7416492EB5@.microsoft.com...
> No, it is not necessary, but until you put a value into it, it's value
will
> be Null. This can bite you if it's a char() or varChar() because by
default
> (there's a setting to change this, but don't use it) the nulls propagate
when
> you concatenate them... i.e.,
> null + 'dsasdasd' is null
It's no different with numeric types:
SELECT CONVERT(INT, NULL) + 1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ok, that is good but here is a second part to the question. If I place a
default value in the variable but then at run time, I have a agent that call
s
the stored procedure and pushes a value to it, will the value pushed in
always take priority over the default value?
"Rich" wrote:
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||Are you talking about variables within the procedure, or paramaterss to the
procedure?
Variables within the proc, at any given point in code, will have whatever
value was last assigned (if any). Just like any other language. E.g:
DECLARE @.variable INT
-- @.variable is NULL
SET @.variable = 1
-- @.variable = 1
SET @.variable = 2
-- @.variable = 2
Parameters with a default value are different - if you pass the param to the
proc it will have the value you passed (including NULL); if you do not pass
the param it will have the default value. E.g:
CREATE PROC foo (@.i INT = 0) AS
BEGIN
SELECT @.i AS i
END
EXEC foo -- returns 0
EXEC foo @.i = 1 -- returns 1
EXEC foo @.i = NULL -- returns NULL
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Yes, If the parameter declaration in the Stored Proc has a default value,
and you nevertheless pass in a value, the passed in value will always take
pre3cedence over the default value.
This is true even When the Passed in value is Null, and teh default value
is somethiong other than Null...
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Hello CB,
perfect, I kind of tested that and found it to be true, I just wanted to
hear it from another programmer!
:)
"CBretana" wrote:
> Yes, If the parameter declaration in the Stored Proc has a default value,
> and you nevertheless pass in a value, the passed in value will always take
> pre3cedence over the default value.
> This is true even When the Passed in value is Null, and teh default value
> is somethiong other than Null...
> "Rich" wrote:
>
variable at the time you declare it?
SAMPLE: “ @.Sec int = 100”
Can @.Sec just be declared?It can just be declared. It will default to NULL:
DECLARE @.SEC INT
SELECT @.SEC
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:10D3C4D8-9FD7-47E0-B77E-DD1C1A4EE8CA@.microsoft.com...
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: " @.Sec int = 100"
> Can @.Sec just be declared?
>|||No, it is not necessary, but until you put a value into it, it's value will
be Null. This can bite you if it's a char() or varChar() because by defaul
t
(there's a setting to change this, but don't use it) the nulls propagate whe
n
you concatenate them... i.e.,
null + 'dsasdasd' is null
"Rich" wrote:
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:548AE03F-A9DD-441E-98AD-5F7416492EB5@.microsoft.com...
> No, it is not necessary, but until you put a value into it, it's value
will
> be Null. This can bite you if it's a char() or varChar() because by
default
> (there's a setting to change this, but don't use it) the nulls propagate
when
> you concatenate them... i.e.,
> null + 'dsasdasd' is null
It's no different with numeric types:
SELECT CONVERT(INT, NULL) + 1
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ok, that is good but here is a second part to the question. If I place a
default value in the variable but then at run time, I have a agent that call
s
the stored procedure and pushes a value to it, will the value pushed in
always take priority over the default value?
"Rich" wrote:
> New to stored procedures. Is it necessary to place a default value into a
> variable at the time you declare it?
> SAMPLE: “ @.Sec int = 100”
> Can @.Sec just be declared?
>|||Are you talking about variables within the procedure, or paramaterss to the
procedure?
Variables within the proc, at any given point in code, will have whatever
value was last assigned (if any). Just like any other language. E.g:
DECLARE @.variable INT
-- @.variable is NULL
SET @.variable = 1
-- @.variable = 1
SET @.variable = 2
-- @.variable = 2
Parameters with a default value are different - if you pass the param to the
proc it will have the value you passed (including NULL); if you do not pass
the param it will have the default value. E.g:
CREATE PROC foo (@.i INT = 0) AS
BEGIN
SELECT @.i AS i
END
EXEC foo -- returns 0
EXEC foo @.i = 1 -- returns 1
EXEC foo @.i = NULL -- returns NULL
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Yes, If the parameter declaration in the Stored Proc has a default value,
and you nevertheless pass in a value, the passed in value will always take
pre3cedence over the default value.
This is true even When the Passed in value is Null, and teh default value
is somethiong other than Null...
"Rich" wrote:
> Ok, that is good but here is a second part to the question. If I place a
> default value in the variable but then at run time, I have a agent that ca
lls
> the stored procedure and pushes a value to it, will the value pushed in
> always take priority over the default value?
> "Rich" wrote:
>|||Hello CB,
perfect, I kind of tested that and found it to be true, I just wanted to
hear it from another programmer!
:)
"CBretana" wrote:
> Yes, If the parameter declaration in the Stored Proc has a default value,
> and you nevertheless pass in a value, the passed in value will always take
> pre3cedence over the default value.
> This is true even When the Passed in value is Null, and teh default value
> is somethiong other than Null...
> "Rich" wrote:
>
DECLARE SYNTAX
I am trying to convert a declare syntax from SQL to MySQL
the syntax is as follows:
declare @.x int ;
set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
UPDATE bugevent
SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
ixBug = (SELECT max(ixBug) FROM bug)
WHERE ixBugEvent = @.x;
but it is giving me an error. can anyone help.
thanks for your time in advance...Since people here generally speak SQL, not MySQL, you might
have better luck asking your question in a MySQL newsgroup.
(If you want to know how to same something in French, you'd ask
someone who speaks French, right?)
Steve Kass
Drew University
harpalshergill@.gmail.com wrote:
>I am trying to convert a declare syntax from SQL to MySQL
>the syntax is as follows:
>
>declare @.x int ;
>set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
>UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
>
>but it is giving me an error. can anyone help.
>thanks for your time in advance...
>
>|||(harpalshergill@.gmail.com) writes:
> I am trying to convert a declare syntax from SQL to MySQL
> the syntax is as follows:
>
> declare @.x int ;
> set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
> UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
> but it is giving me an error. can anyone help.
> thanks for your time in advance...
All I can say is hat I don't think that @.x variables are available in
MySQL. I would only expect those to work with SQL Server and Sybase.
There is a comp.databases.mysql. You should have better luck there.
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|||Not only are variables available but they can used along with
columns in a query! :)
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97EFB333256EYazorman@.127.0.0.1...
> (harpalshergill@.gmail.com) writes:
> All I can say is hat I don't think that @.x variables are available in
> MySQL. I would only expect those to work with SQL Server and Sybase.
> There is a comp.databases.mysql. You should have better luck there.
>
> --
> 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|||A more precise analogy might be to ask: if you want to know how to name
something in Creole French, you'd ask some one who speaks it, non?
Speak de patetois, non?
Steve Kass wrote:
> Since people here generally speak SQL, not MySQL, you might
> have better luck asking your question in a MySQL newsgroup.
> (If you want to know how to same something in French, you'd ask
> someone who speaks French, right?)
> Steve Kass
> Drew University
> harpalshergill@.gmail.com wrote:
>|||harpalshergill@.gmail.com wrote:
> I am trying to convert a declare syntax from SQL to MySQL
> the syntax is as follows:
>
> declare @.x int ;
> set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
> UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
>
> but it is giving me an error. can anyone help.
> thanks for your time in advance...
>
Two things that might help you get a response:
1. Include the error message, don't make us guess what error you're
getting.
2. Post to the proper newsgroup
the syntax is as follows:
declare @.x int ;
set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
UPDATE bugevent
SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
ixBug = (SELECT max(ixBug) FROM bug)
WHERE ixBugEvent = @.x;
but it is giving me an error. can anyone help.
thanks for your time in advance...Since people here generally speak SQL, not MySQL, you might
have better luck asking your question in a MySQL newsgroup.
(If you want to know how to same something in French, you'd ask
someone who speaks French, right?)
Steve Kass
Drew University
harpalshergill@.gmail.com wrote:
>I am trying to convert a declare syntax from SQL to MySQL
>the syntax is as follows:
>
>declare @.x int ;
>set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
>UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
>
>but it is giving me an error. can anyone help.
>thanks for your time in advance...
>
>|||(harpalshergill@.gmail.com) writes:
> I am trying to convert a declare syntax from SQL to MySQL
> the syntax is as follows:
>
> declare @.x int ;
> set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
> UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
> but it is giving me an error. can anyone help.
> thanks for your time in advance...
All I can say is hat I don't think that @.x variables are available in
MySQL. I would only expect those to work with SQL Server and Sybase.
There is a comp.databases.mysql. You should have better luck there.
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|||Not only are variables available but they can used along with
columns in a query! :)
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97EFB333256EYazorman@.127.0.0.1...
> (harpalshergill@.gmail.com) writes:
> All I can say is hat I don't think that @.x variables are available in
> MySQL. I would only expect those to work with SQL Server and Sybase.
> There is a comp.databases.mysql. You should have better luck there.
>
> --
> 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|||A more precise analogy might be to ask: if you want to know how to name
something in Creole French, you'd ask some one who speaks it, non?
Speak de patetois, non?
Steve Kass wrote:
> Since people here generally speak SQL, not MySQL, you might
> have better luck asking your question in a MySQL newsgroup.
> (If you want to know how to same something in French, you'd ask
> someone who speaks French, right?)
> Steve Kass
> Drew University
> harpalshergill@.gmail.com wrote:
>|||harpalshergill@.gmail.com wrote:
> I am trying to convert a declare syntax from SQL to MySQL
> the syntax is as follows:
>
> declare @.x int ;
> set @.x = (SELECT max(ixBugEvent) FROM bugevent) ;
> UPDATE bugevent
> SET ixAttachment = (SELECT max(ixAttachment) FROM attachment),
> ixBug = (SELECT max(ixBug) FROM bug)
> WHERE ixBugEvent = @.x;
>
> but it is giving me an error. can anyone help.
> thanks for your time in advance...
>
Two things that might help you get a response:
1. Include the error message, don't make us guess what error you're
getting.
2. Post to the proper newsgroup
Friday, March 9, 2012
Declare an array of int in SQL Server
Hi All,
I want declare an array of int in SQL Server
please help me to convert the following code from VB to TSQL
----
Dim md_mon(12) As Integer
md_mon(1) = 31
----
ThanksThere are no arrays in SQLServer. Maybe if you explain your requirements
someone can suggest another alternative.
I want declare an array of int in SQL Server
please help me to convert the following code from VB to TSQL
----
Dim md_mon(12) As Integer
md_mon(1) = 31
----
ThanksThere are no arrays in SQLServer. Maybe if you explain your requirements
someone can suggest another alternative.
--
David Portas
SQL Server MVP
--|||Majid Mohammadian (Mohammadian59@.yahoo.com) writes:
> I want declare an array of int in SQL Server
> please help me to convert the following code from VB to TSQL
> ----
> Dim md_mon(12) As Integer
> md_mon(1) = 31
> ----
SQL and Visual Basic are languages that work from very different mindsets,
and for a successful rewrite from VB to SQL it is often best to start from
the beginning. In VB you would typically to things in loops, to iterate
over items in a set of data, but in SQL you should always strive to use
commands that operate on the entire set at the same time. That is when
you actually win performance.
SQL does not have arrays, but there are tables, and tables are a lot broader
concept than array. So in this case:
CREATE TABLE md_mon (monthno int NOT NULL PRIMARY KEY,
noofdays tinyint NOT NULL)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If the purpose of your array was to store the number of days in each month
then as an alternative in TSQL you could calculate the number of days for a
given month like this:
DECLARE @.dt DATETIME
SET @.dt = '20040101' -- January 2004
SELECT DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@.dt),@.dt))))
--
David Portas
SQL Server MVP
--|||Erland Sommarskog <sommar@.algonet.se> wrote in message
> SQL does not have arrays, but there are tables, and tables are a lot broader
> concept than array. So in this case:
> CREATE TABLE md_mon (monthno int NOT NULL PRIMARY KEY,
> noofdays tinyint NOT NULL)
FYI: You can also create a table variable, like:
DECLARE @.tblMonth TABLE (
MonthNo int,
NumDays int
)
... and then use it like any real table:
INSERT INTO @.tblMonth...
SELECT * FROM @.tblMonth...
Declaration of record variable
Can someone say how to declare a record like variable in MSSQL-2000 Like:
mr_rec record of variables a int, b char(20), c datetime? I could not find any examples on BOL. I want to use this in a stored procedure create script.
Thanks, Vinniethere is no such thing as a "record" variable in mssql.
You can declare a table variable however, this might meet your need. A table variable is basically a temp table. See BOL for more info on table variables.
mr_rec record of variables a int, b char(20), c datetime? I could not find any examples on BOL. I want to use this in a stored procedure create script.
Thanks, Vinniethere is no such thing as a "record" variable in mssql.
You can declare a table variable however, this might meet your need. A table variable is basically a temp table. See BOL for more info on table variables.
Decimal value rounded to ,00 , why?
Hello!
I′m having some trouble with a database field that I had to redefine. At first, it was defined as an int, but I had to change it to decimal (5,2).
I have an application which is accessing my database. Problem now is that when if I enter a decimal value, say 3,12 into my field and then press save on my BindingNavigator, this value is instantly rounded to 3,00. I tried entering the value directly into my database, and that′s no problem. The rounding seems to happen on the update statement. I tried stepping through the code, and after this particular line, the value is rounded... Any help?
Me.OrdersTableAdapter.Update(Me.OrdersDataSet.Orders)
Regards
Daniel
This is totally an application issue -not a SQL Server issue.
The problem is that your application STILL thinks that the datatype is int. It needs to be provided the 'new' information that the datatype is decimal.
In your application code, Remove the binding and linkages between the datagrid and the database, and then re-create them.
|||Thanks alot,
I found that I could edit the datatypes defined in my TableAdapter′s insert and update statements,
now everything work fine!
Thanks again!
Daniel
Saturday, February 25, 2012
decimal
Dear guys,
I have a data like 28.56 I want only 56, how can I get..
I can get 28 after converting in to Int datatype , what about 56, how can I
get , any buildin function available.
Thanks
NOOR
On Mon, 2 Aug 2004 23:41:31 -0700, Noor wrote:
>Dear guys,
>I have a data like 28.56 I want only 56, how can I get..
>I can get 28 after converting in to Int datatype , what about 56, how can I
>get , any buildin function available.
>Thanks
>NOOR
Hi Noor,
Do you want 0.56 or 56?
Several options are in the script below.
declare @.a decimal(4,2)
set @.a = 28.56
select @.a
select cast(@.a as int)
select @.a - cast(@.a as int)
select (@.a - cast(@.a as int)) * 100
select substring(cast(@.a - cast(@.a as int) as varchar),3,2)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo
Noor
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:6jpug0h5eoa24b5urkg8sm8hdgnmtm6v5r@.4ax.com... [vbcol=seagreen]
> On Mon, 2 Aug 2004 23:41:31 -0700, Noor wrote:
I
> Hi Noor,
> Do you want 0.56 or 56?
> Several options are in the script below.
> declare @.a decimal(4,2)
> set @.a = 28.56
> select @.a
> select cast(@.a as int)
> select @.a - cast(@.a as int)
> select (@.a - cast(@.a as int)) * 100
> select substring(cast(@.a - cast(@.a as int) as varchar),3,2)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
I have a data like 28.56 I want only 56, how can I get..
I can get 28 after converting in to Int datatype , what about 56, how can I
get , any buildin function available.
Thanks
NOOR
On Mon, 2 Aug 2004 23:41:31 -0700, Noor wrote:
>Dear guys,
>I have a data like 28.56 I want only 56, how can I get..
>I can get 28 after converting in to Int datatype , what about 56, how can I
>get , any buildin function available.
>Thanks
>NOOR
Hi Noor,
Do you want 0.56 or 56?
Several options are in the script below.
declare @.a decimal(4,2)
set @.a = 28.56
select @.a
select cast(@.a as int)
select @.a - cast(@.a as int)
select (@.a - cast(@.a as int)) * 100
select substring(cast(@.a - cast(@.a as int) as varchar),3,2)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo
Noor
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:6jpug0h5eoa24b5urkg8sm8hdgnmtm6v5r@.4ax.com... [vbcol=seagreen]
> On Mon, 2 Aug 2004 23:41:31 -0700, Noor wrote:
I
> Hi Noor,
> Do you want 0.56 or 56?
> Several options are in the script below.
> declare @.a decimal(4,2)
> set @.a = 28.56
> select @.a
> select cast(@.a as int)
> select @.a - cast(@.a as int)
> select (@.a - cast(@.a as int)) * 100
> select substring(cast(@.a - cast(@.a as int) as varchar),3,2)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
deceptively simple join / select question
Ok, I have two tables with a child/parent or one -> many relationship:
parent_table:
pid int primary key
pname varchar
child_table:
cid int primary key
pid int
cname varchar
Say the contents of these two tables are:
parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael
child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3
Now what I would like to be able to do is:
select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?
Originally posted by blm14_cu
Ok, I have two tables with a child/parent or one -> many relationship:
parent_table:
pid int primary key
pname varchar
child_table:
cid int primary key
pid int
cname varchar
Say the contents of these two tables are:
parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael
child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3
Now what I would like to be able to do is:
select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?|||Originally posted by JODonnell
Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?
No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:
pname1 cname1
pname1 cname2
I want:
pname1 cname1 cname2|||Originally posted by blm14_cu
No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:
pname1 cname1
pname1 cname2
I want:
pname1 cname1 cname2
What about GROUP BY:
Select p.*,c.* From Ptable P Join Ctable C ON P.pid = C.pid Where [P.pid = C.pid] GROUP BY P.pid
Sorry for the mess but it's almost 5.
John|||Still no good. The group by wont help because I'm not doing any sums or avgs or counts or anything. Adding the group by wont change the results at all actually, from what I know.|||I was bored.
I think this is what you're looking for
Rgds,
Jim.
declare @.d_id int;
declare @.c_name varchar(100);
declare @.c_arr varchar(2000);
declare @.tmp varchar(100);
declare @.x table([id] int, [name] varchar(2000))
DECLARE d cursor for
select depid
from dept;
OPEN d
FETCH NEXT FROM d INTO @.d_id
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.tmp='';
set @.c_arr='';
DECLARE c CURSOR FOR
SELECT name
FROM emp
where deptid = @.d_id
OPEN c
FETCH next from c into @.c_name
while @.@.fetch_status = 0
BEGIN
print @.d_id
print @.c_name
set @.tmp = @.c_arr
set @.c_arr = @.c_name+','+@.tmp
fetch next from c into @.c_name
END
CLOSE c
DEALLOCATE c
if (len(@.c_arr)>1)
begin Insert @.x values(@.d_id, substring(@.c_arr,1,len(@.c_arr)-1))end
FETCH NEXT FROM d INTO @.d_id
END
CLOSE d
DEALLOCATE d
select id, name as name from @.x
GO|||You might check yesterday's thread (http://www.dbforums.com/t989683.html) on this topic.
-PatP
parent_table:
pid int primary key
pname varchar
child_table:
cid int primary key
pid int
cname varchar
Say the contents of these two tables are:
parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael
child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3
Now what I would like to be able to do is:
select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?
Originally posted by blm14_cu
Ok, I have two tables with a child/parent or one -> many relationship:
parent_table:
pid int primary key
pname varchar
child_table:
cid int primary key
pid int
cname varchar
Say the contents of these two tables are:
parent_table:
pid pname:
1 Ben
2 Jesse
3 Michael
child_table
pid cid cname
1 1 ben_Child1
1 2 ben_Child2
1 3 ben_Child3
2 4 jesse_Child1
2 5 jesse_Child2
2 6 jesse_Child3
3 7 michael_Child1
3 8 michael_Child2
3 9 michael_Child3
Now what I would like to be able to do is:
select pname, cname
from
parent table a,
child_table b
where a.pid = b.pid
Except! Instead of getting the results in the form of:
Ben ben_Child1
Ben ben_Child2
Ben ben_Child3
...
I would like them in
Ben ben_Child1 ben_Child2
Now normally this would be impossible (I think) since the query would return an unknown number of columns. But in this case I only care about the FIRST TWO children for each parent. So I'm sure there's some way to do this with a simple select, but I don't know how. Anyone?|||Originally posted by JODonnell
Are you trying to get everything from both tables in the form pname cname? If so what about a Cross Join?
No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:
pname1 cname1
pname1 cname2
I want:
pname1 cname1 cname2|||Originally posted by blm14_cu
No, a cross join would give me EVERYTHING. I am trying to get a subset of the results but in addition I am trying to map two rows to two columns eg instead of:
pname1 cname1
pname1 cname2
I want:
pname1 cname1 cname2
What about GROUP BY:
Select p.*,c.* From Ptable P Join Ctable C ON P.pid = C.pid Where [P.pid = C.pid] GROUP BY P.pid
Sorry for the mess but it's almost 5.
John|||Still no good. The group by wont help because I'm not doing any sums or avgs or counts or anything. Adding the group by wont change the results at all actually, from what I know.|||I was bored.
I think this is what you're looking for
Rgds,
Jim.
declare @.d_id int;
declare @.c_name varchar(100);
declare @.c_arr varchar(2000);
declare @.tmp varchar(100);
declare @.x table([id] int, [name] varchar(2000))
DECLARE d cursor for
select depid
from dept;
OPEN d
FETCH NEXT FROM d INTO @.d_id
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.tmp='';
set @.c_arr='';
DECLARE c CURSOR FOR
SELECT name
FROM emp
where deptid = @.d_id
OPEN c
FETCH next from c into @.c_name
while @.@.fetch_status = 0
BEGIN
print @.d_id
print @.c_name
set @.tmp = @.c_arr
set @.c_arr = @.c_name+','+@.tmp
fetch next from c into @.c_name
END
CLOSE c
DEALLOCATE c
if (len(@.c_arr)>1)
begin Insert @.x values(@.d_id, substring(@.c_arr,1,len(@.c_arr)-1))end
FETCH NEXT FROM d INTO @.d_id
END
CLOSE d
DEALLOCATE d
select id, name as name from @.x
GO|||You might check yesterday's thread (http://www.dbforums.com/t989683.html) on this topic.
-PatP
Subscribe to:
Posts (Atom)