Inserting a Decimal - HELP

Im trying to insert a decimal into SQL 2000 using a stored procedure. I can successfully insert it using a direct T-SQL statement inside the code, but I cant insert it correctly when calling for a stored procedure.

For instance 12.5 will insert as 12.

Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@.Num", SqlDbType.Decimal))
MyCommand.Parameters("@.Num").Precision = 5
MyCommand.Parameters("@.Num").Scale = 2
MyCommand.Parameters("@.Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]

I also declared @.Num to be a decimal in my Stored Procedure.

As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.

Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.Try running Profiler and tracing the transaction. Then you can determine if the precision is being lost when .NET sends the command to SQL Server, or when SQL Server calls the stored procedure. Have you tried not being so specific? Can you post the source code for your stored procedure?

MyCommand.Parameters.Add("@.Num", Convert.ToDecimal(TextBox1.Text))

inserting <NULL>

how do you write an insert into statement and keep <NULL> in the null cells?
i was trying something like this BUT when you try to write an IS NULL statement it doesnt work.
ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO D1, NULL) AS Mod1,ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO , NULL) AS Mod1,|||how is this any different?

ISNULL(dbo.qry_TRUNK02_LastVersion_PayableClaims01 .MOD1, NULL) AS Mod1

after i truncate and insert into my table i need to be able to query with an IS NULL statement.

select mod1
from table
where mod1 is null|||Books online: ISNULL Replaces NULL with the specified replacement value.
So what is the point of ISNULL([A], Null)?

Please explain more clearly what you are trying to do, and give some sample data.|||Yeah, what The Blind One said...

are you just meaning to insert a NULL into a column of a table?

as in:
INSERT INTO dbo.TRUNK02_LastVersion_PayableClaims_01 (Mod1, ...)
VALUES (NULL, ...)???

Insert/Update sql commands not saving to DB

Here is my problem :
I issue an insert statement to the db. While I am getting a return value of 1 (1 row was affected) the values never show up into the db when I open the DB in access. However, I can see the data when it does an SQL select inside the program. So for instance, I do an insert into ORDER values (1, 12, 5.99). (1 = item ID, 12 = quantity, 5.99 = price). I then do a select * from Order, and I get those values back. When I open the DB in access, in between doing the insert and the select, I dont see the values there either. It is like it is making a temporary copy of the DB in memory during the execution only. When I close the program and re-F5, the data is no longer there. Maybe we need some kind of commit transaction? What am I doing wrong? I am using VB.Net 2005/MS Access 2003. Here is the relevant code :
Private m_Connection As OleDbConnection
''' <summary>
''' Defines the path to the database.
''' </summary>
''' <remarks></remarks>
#If CONFIG = "Debug" Then
Public Const DB_PATH As String = "DBs\DB_Test.mdb"
#ElseIf CONFIG = "Release" Then
Public Const DB_PATH As String = "DBs\DB_Production.mdb"
#End If
Sub connect(ByVal p_path As String) Implements IPartyDBase.connect
Dim connect_string As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & p_path
m_Connection = New OleDbConnection(connect_string)
m_Connection.Open()
End Sub
Sub someSub(ByVal stock As StockClass)
Dim tempString
Dim command As OleDbCommand
command = m_Connection.CreateCommand
command.CommandType = CommandType.Text
tempString = "Insert into Stock VALUES (" & stock.ID & ", "
tempString = tempString & stock.Quantity & ", "
tempString = tempString & stock.Price & ")"
Dim tempInt as Integer
command.CommandText = tempString
tempInt = command.ExecuteNonQuery
If Not tempInt = 1 Then
Throw New Exception("Bad addStockToDB into Stock " & tempInt)
End If
End Sub
Sub anotherSub
p_dbase.connect(PartyDBaseAccess.DB_PATH)
p_dbase.someSub()
p_dbase.close()
End Sub
Edit : During execution, looking under bin/debug/DBs, there is a copy of the database that has all the transactions I did during execution... but the actual DB isnt being updated/copied over.
Ok, the problem was that the path was not implicit, and it was overwriting the DB in /bin/debug/DBs... so changing the DB attributes to never copy worked, and opening the file in /bin/debug/DBs instead of the place where it was copying from.

insert/select

I am trying to find an easier way to handle my insert/select statement. If
I have the following tables and query - Is there a way to do this in one
statement?
create table Master(
MasterKey int,
)
Create table SubTable1(
MasterKey int,
PK int,
Data varChar(1000)
)
Create table SubTable2(
MasterKey int,
PK int,
Data varChar(1000
)
Master Table
MasterKey Priority
1 0
2 0
3 1
4 0
5 0
6 0
SubTable1
Empty
SubTable2
MasterKey PK
1 1
1 1
1 2
2 3
2 3
3 4
3 4
3 4
4 4
4 4
4 5
4 5
What I want to be able to do is move data from SubTable2 to SubTable1
I tried to do something like:
Select @.MasterKey from Master where Priority = 1 (this would give me
a MasterKey of 3)
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK = 4
This would move/create 5 records with a @.MasterKey of 3 into the SubTable1.
This works as long as there is only
one MasterKey. But what if I want to create a 5 records for all (or a
potion) of the MasterKeys.
I could reexecute the command multiple times from a loop to get the results
I want, but I was curious if there was an easier way, using one SQL
Statement.
Thanks,
Tomtshad, it is not clear what you are trying to do. In the example you give
you would end up with 5 records in SubTable1 that all had MasterKey = 3 and
PK = 4, that doesn't seem to make much sense.
If you explain it better I can probably help. I think you may want to use an
IN list in the SELECT, so something like:
insert (MasterKey,PK,Data)
Select @.MasterKey,PK,Data
From SubTable2
Where PK IN (4, 5, 6)
or otherwise you may need to use a subquery or a join, but I just can't tell
what you're trying to do.
Sean
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uIw5J5tjFHA.3756@.TK2MSFTNGP15.phx.gbl...
>I am trying to find an easier way to handle my insert/select statement. If
>I have the following tables and query - Is there a way to do this in one
>statement?
> create table Master(
> MasterKey int,
> )
> Create table SubTable1(
> MasterKey int,
> PK int,
> Data varChar(1000)
> )
>
> Create table SubTable2(
> MasterKey int,
> PK int,
> Data varChar(1000
> )
> Master Table
> MasterKey Priority
> 1 0
> 2 0
> 3 1
> 4 0
> 5 0
> 6 0
> SubTable1
> Empty
> SubTable2
> MasterKey PK
> 1 1
> 1 1
> 1 2
> 2 3
> 2 3
> 3 4
> 3 4
> 3 4
> 4 4
> 4 4
> 4 5
> 4 5
> What I want to be able to do is move data from SubTable2 to SubTable1
> I tried to do something like:
> Select @.MasterKey from Master where Priority = 1 (this would give
> me a MasterKey of 3)
> insert (MasterKey,PK,Data)
> Select @.MasterKey,PK,Data
> From SubTable2
> Where PK = 4
> This would move/create 5 records with a @.MasterKey of 3 into the
> SubTable1. This works as long as there is only
> one MasterKey. But what if I want to create a 5 records for all (or a
> potion) of the MasterKeys.
> I could reexecute the command multiple times from a loop to get the
> results I want, but I was curious if there was an easier way, using one
> SQL Statement.
> Thanks,
> Tom
>
>

insert..select

I have an insert into select from statement that sometimes performs HORRIBLY
on SQL Server. I did some tests. This is what I found.
INSERT INTO target
SELECT top 500 *
FROM source
executes in less than 1 second.
INSERT INTO target
SELECT top 501 *
FROM source
executes in 140 seconds!
What is up? How can I get respectable performance from this database.
I am COMPLETELY at a loss why the number 500 is magic.
--
Keith WilliamsHi,
This will not happen. I feel that first one is read from memory. Try
executing the below:-
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
INSERT INTO target
SELECT top 500 *
FROM source
-- Now
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
INSERT INTO target
SELECT top 501 *
FROM source
Thanks
Hari
MCDBA
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> I have an insert into select from statement that sometimes performs
HORRIBLY on SQL Server. I did some tests. This is what I found.
> INSERT INTO target
> SELECT top 500 *
> FROM source
> executes in less than 1 second.
> INSERT INTO target
> SELECT top 501 *
> FROM source
> executes in 140 seconds!
> What is up? How can I get respectable performance from this database.
> I am COMPLETELY at a loss why the number 500 is magic.
> --
> Keith Williams
>|||Perhaps this is the line where the optimizer decides to not use an index vs.
using the index. Check
the execution plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> I have an insert into select from statement that sometimes performs HORRIBLY on SQ
L Server. I did
some tests. This is what I found.
> INSERT INTO target
> SELECT top 500 *
> FROM source
> executes in less than 1 second.
> INSERT INTO target
> SELECT top 501 *
> FROM source
> executes in 140 seconds!
> What is up? How can I get respectable performance from this database.
> I am COMPLETELY at a loss why the number 500 is magic.
> --
> Keith Williams
>|||The execution plan is vastly different. I had looked at it already and assum
ed that was obvious. My questions are
1) Why does the excution plan change at such a low threshold? The plan for 5
01 rows to insert would be fantastic if I was inserting at least 5000 rows,
maybe more like 50,000 rows.
2) How can I influence the plan? I have tried all the hints, even those I kn
ew wouldn't work. I am dissapointed that the FIRSTROWS hint doesn't have an
affect.
Since I posted this I have gotten much better performance by replacing the b
ind variables in the actual SQL statement with hard coded parameters. SQLSer
ver now makes a better estimate of the number of rows being inserted.
The actual SQL is much more complicated than the trivial example because it
is joining several tables, the target is clustered in a non-optimal fashion
for the insert, and there is an after trigger on the target table.
Real world, real issues. This same query runs in sub-second on Oracle and DB
2.
--
Keith Williams
"Tibor Karaszi" wrote:

> Perhaps this is the line where the optimizer decides to not use an index v
s. using the index. Check
> the execution plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in messag
e
> news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> some tests. This is what I found.
>
>|||I can think of two things off hand:
Statistics. Did you try to update for involved tables? With fullscan?
Parameter sniffing. Doesn't sound like it, but if you aren't familiar with t
he concept, search the
archives and you should find good explanations on the topic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:6B6AF096-A16A-4BC7-9E78-A993F8E59BCB@.microsoft.com...
> The execution plan is vastly different. I had looked at it already and assumed tha
t was obvious.
My questions are
> 1) Why does the excution plan change at such a low threshold? The plan for 501 row
s to insert
would be fantastic if I was inserting at least 5000 rows, maybe more like 50,000 rows.[vbcol
=seagreen]
> 2) How can I influence the plan? I have tried all the hints, even those I knew wou
ldn't work. I am[/vbcol]
dissapointed that the FIRSTROWS hint doesn't have an affect.
> Since I posted this I have gotten much better performance by replacing the bind va
riables in the
actual SQL statement with hard coded parameters. SQLServer now makes a bette
r estimate of the number
of rows being inserted.
> The actual SQL is much more complicated than the trivial example because it is joi
ning several
tables, the target is clustered in a non-optimal fashion for the insert, and
there is an after
trigger on the target table.[vbcol=seagreen]
> Real world, real issues. This same query runs in sub-second on Oracle and
DB2.
> --
> Keith Williams
>
> "Tibor Karaszi" wrote:
>
Check[vbcol=seagreen]
did[vbcol=seagreen]

insert..select

I have an insert into select from statement that sometimes performs HORRIBLY on SQL Server. I did some tests. This is what I found.
INSERT INTO target
SELECT top 500 *
FROM source
executes in less than 1 second.
INSERT INTO target
SELECT top 501 *
FROM source
executes in 140 seconds!
What is up? How can I get respectable performance from this database.
I am COMPLETELY at a loss why the number 500 is magic.
Keith Williams
Hi,
This will not happen. I feel that first one is read from memory. Try
executing the below:-
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
INSERT INTO target
SELECT top 500 *
FROM source
-- Now
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
INSERT INTO target
SELECT top 501 *
FROM source
Thanks
Hari
MCDBA
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> I have an insert into select from statement that sometimes performs
HORRIBLY on SQL Server. I did some tests. This is what I found.
> INSERT INTO target
> SELECT top 500 *
> FROM source
> executes in less than 1 second.
> INSERT INTO target
> SELECT top 501 *
> FROM source
> executes in 140 seconds!
> What is up? How can I get respectable performance from this database.
> I am COMPLETELY at a loss why the number 500 is magic.
> --
> Keith Williams
>
|||Perhaps this is the line where the optimizer decides to not use an index vs. using the index. Check
the execution plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> I have an insert into select from statement that sometimes performs HORRIBLY on SQL Server. I did
some tests. This is what I found.
> INSERT INTO target
> SELECT top 500 *
> FROM source
> executes in less than 1 second.
> INSERT INTO target
> SELECT top 501 *
> FROM source
> executes in 140 seconds!
> What is up? How can I get respectable performance from this database.
> I am COMPLETELY at a loss why the number 500 is magic.
> --
> Keith Williams
>
|||The execution plan is vastly different. I had looked at it already and assumed that was obvious. My questions are
1) Why does the excution plan change at such a low threshold? The plan for 501 rows to insert would be fantastic if I was inserting at least 5000 rows, maybe more like 50,000 rows.
2) How can I influence the plan? I have tried all the hints, even those I knew wouldn't work. I am dissapointed that the FIRSTROWS hint doesn't have an affect.
Since I posted this I have gotten much better performance by replacing the bind variables in the actual SQL statement with hard coded parameters. SQLServer now makes a better estimate of the number of rows being inserted.
The actual SQL is much more complicated than the trivial example because it is joining several tables, the target is clustered in a non-optimal fashion for the insert, and there is an after trigger on the target table.
Real world, real issues. This same query runs in sub-second on Oracle and DB2.
Keith Williams
"Tibor Karaszi" wrote:

> Perhaps this is the line where the optimizer decides to not use an index vs. using the index. Check
> the execution plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
> news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> some tests. This is what I found.
>
>
|||I can think of two things off hand:
Statistics. Did you try to update for involved tables? With fullscan?
Parameter sniffing. Doesn't sound like it, but if you aren't familiar with the concept, search the
archives and you should find good explanations on the topic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:6B6AF096-A16A-4BC7-9E78-A993F8E59BCB@.microsoft.com...
> The execution plan is vastly different. I had looked at it already and assumed that was obvious.
My questions are
> 1) Why does the excution plan change at such a low threshold? The plan for 501 rows to insert
would be fantastic if I was inserting at least 5000 rows, maybe more like 50,000 rows.
> 2) How can I influence the plan? I have tried all the hints, even those I knew wouldn't work. I am
dissapointed that the FIRSTROWS hint doesn't have an affect.
> Since I posted this I have gotten much better performance by replacing the bind variables in the
actual SQL statement with hard coded parameters. SQLServer now makes a better estimate of the number
of rows being inserted.
> The actual SQL is much more complicated than the trivial example because it is joining several
tables, the target is clustered in a non-optimal fashion for the insert, and there is an after
trigger on the target table.[vbcol=seagreen]
> Real world, real issues. This same query runs in sub-second on Oracle and DB2.
> --
> Keith Williams
>
> "Tibor Karaszi" wrote:
Check[vbcol=seagreen]
did[vbcol=seagreen]
sql

insert..select

I have an insert into select from statement that sometimes performs HORRIBLY on SQL Server. I did some tests. This is what I found.
INSERT INTO target
SELECT top 500 *
FROM source
executes in less than 1 second.
INSERT INTO target
SELECT top 501 *
FROM source
executes in 140 seconds!
What is up? How can I get respectable performance from this database.
I am COMPLETELY at a loss why the number 500 is magic.
--
Keith WilliamsHi,
This will not happen. I feel that first one is read from memory. Try
executing the below:-
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
INSERT INTO target
SELECT top 500 *
FROM source
-- Now
DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
INSERT INTO target
SELECT top 501 *
FROM source
Thanks
Hari
MCDBA
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> I have an insert into select from statement that sometimes performs
HORRIBLY on SQL Server. I did some tests. This is what I found.
> INSERT INTO target
> SELECT top 500 *
> FROM source
> executes in less than 1 second.
> INSERT INTO target
> SELECT top 501 *
> FROM source
> executes in 140 seconds!
> What is up? How can I get respectable performance from this database.
> I am COMPLETELY at a loss why the number 500 is magic.
> --
> Keith Williams
>|||Perhaps this is the line where the optimizer decides to not use an index vs. using the index. Check
the execution plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> I have an insert into select from statement that sometimes performs HORRIBLY on SQL Server. I did
some tests. This is what I found.
> INSERT INTO target
> SELECT top 500 *
> FROM source
> executes in less than 1 second.
> INSERT INTO target
> SELECT top 501 *
> FROM source
> executes in 140 seconds!
> What is up? How can I get respectable performance from this database.
> I am COMPLETELY at a loss why the number 500 is magic.
> --
> Keith Williams
>|||I can think of two things off hand:
Statistics. Did you try to update for involved tables? With fullscan?
Parameter sniffing. Doesn't sound like it, but if you aren't familiar with the concept, search the
archives and you should find good explanations on the topic.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
news:6B6AF096-A16A-4BC7-9E78-A993F8E59BCB@.microsoft.com...
> The execution plan is vastly different. I had looked at it already and assumed that was obvious.
My questions are
> 1) Why does the excution plan change at such a low threshold? The plan for 501 rows to insert
would be fantastic if I was inserting at least 5000 rows, maybe more like 50,000 rows.
> 2) How can I influence the plan? I have tried all the hints, even those I knew wouldn't work. I am
dissapointed that the FIRSTROWS hint doesn't have an affect.
> Since I posted this I have gotten much better performance by replacing the bind variables in the
actual SQL statement with hard coded parameters. SQLServer now makes a better estimate of the number
of rows being inserted.
> The actual SQL is much more complicated than the trivial example because it is joining several
tables, the target is clustered in a non-optimal fashion for the insert, and there is an after
trigger on the target table.
> Real world, real issues. This same query runs in sub-second on Oracle and DB2.
> --
> Keith Williams
>
> "Tibor Karaszi" wrote:
> > Perhaps this is the line where the optimizer decides to not use an index vs. using the index.
Check
> > the execution plan.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Keith Williams" <KeithWilliams@.discussions.microsoft.com> wrote in message
> > news:032A9291-2E2C-4EE6-845E-A9E75305FA61@.microsoft.com...
> > > I have an insert into select from statement that sometimes performs HORRIBLY on SQL Server. I
did
> > some tests. This is what I found.
> > >
> > > INSERT INTO target
> > > SELECT top 500 *
> > > FROM source
> > >
> > > executes in less than 1 second.
> > >
> > > INSERT INTO target
> > > SELECT top 501 *
> > > FROM source
> > >
> > > executes in 140 seconds!
> > >
> > > What is up? How can I get respectable performance from this database.
> > >
> > > I am COMPLETELY at a loss why the number 500 is magic.
> > > --
> > > Keith Williams
> > >
> >
> >
> >

insert, select, update and delete

I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc.

How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4.

Maybe I didn't explain it good enough for you, please tell me then!!

Thanks!!

I think I got a solution for it. On every top of the page the user can select a value in a dropdownlist. The selected value calls the database and selects the appropriate row. Now I can update and delete the row I want. To update the values of the database I got for each value a textboxt. On selectedindexchanged the textbox.text will filled with the values of the appropriate row and now I can adjust the textboxes and update the values. Or I can delete the row with another button. This is in theory but practical it's hard. Does someone has suggestions or hints??|||Which version of ASP.NET and which version of SQL Server are you using? Your questions are a bit too vague (and possibly in the incorrect forum) to be able to be of much help to you.

If you are using ASP.NET 2.0 you might try theTutorials on this site.|||I was affraid of that, second try:
I have a dropdownlist with values as NEW and BonsaiName1, BonsaiName2. When the NEW is selected all textboxes are empty and can be filled with data that can be inserted into the database and BonsaiName3 is created. When BonsaiName1 or BonsaiName2 etc is selected the textboxes should be filled with the correspondending data out of the database with a SELECT procedure. They can now be edited by a UPDATE or DELETE statement.
My question is: When I got a dropdownlist1.selectedvalue how do I get this in the SELECTstatement in the WHEREpart. I think the best way is with a parameter but how?
I'm running ASP.NET 2.0 and SQL Server 2005.
Thanks!!

INSERT using text from different textboxes and trying to get it into SQL database (NOT WOR

If anyone has examples of pulling the text out of textboxes and passing it to a INSERT statement which then puts the data into a SQL database table please if you could pass this on that would be great.

Regards and thanks in advance.

Ryan J. Boylecheck this article

hth

Insert using multiple field terminators

Hi all,
In Oracle, SQL Loader allows you to use the statement below:
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
Is there a way to do this with BULK INSERT or some other import function?
The data files that I am attempting to import has fields terminated by commas
but when some of those text fields may contain a comma, they are also
enclosed by pipes. The above statement in Oracle deals with this issue
effectively... hoping I can find something for MS SQL Server. Thanks!
PatrickPatrick
CREATE TABLE TmpStList
(
stFName varchar (10) NOT NULL,
stLName varchar (10) NOT NULL,
stEmail varchar (30) NOT NULL
)
go
The data file (hawk.dat):
"Kelly","Reynold","kelly@.reynold.com"
"John","Smith","bill@.smith.com"
"Sara","Parker","sara@.parker.com"
The format file (hawk.bcp):
8.0
4
1 SQLCHAR 0 1 "\"" 0 first_quote ""
2 SQLCHAR 0 10 "\",\"" 1 stFName ""
3 SQLCHAR 0 10 "\",\"" 2 stLName ""
4 SQLCHAR 0 30 "\"\r\n" 3 stEmail ""
bulk insert TmpStList from 'C:\Staging\hawk.dat'
with (formatfile = 'C:\Staging\hawk.bcp')
select * from TmpStList
stFName stLName stEmail
-- -- --
Kelly Reynold kelly@.reynold.com
John Smith bill@.smith.com
Sara Parker sara@.parker.com
drop table TmpStList
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:CA167741-DE9E-4752-AEE1-97D3A67C3837@.microsoft.com...
> Hi all,
> In Oracle, SQL Loader allows you to use the statement below:
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
> Is there a way to do this with BULK INSERT or some other import function?
> The data files that I am attempting to import has fields terminated by
> commas
> but when some of those text fields may contain a comma, they are also
> enclosed by pipes. The above statement in Oracle deals with this issue
> effectively... hoping I can find something for MS SQL Server. Thanks!
> Patrick
>

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.
1;Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
EndCould you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:

> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.
[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi
Some ideas
http://dimantdatabasesolutions.blog...e
s.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.&
#91;Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>Some ideas
>http://dimantdatabasesolutions.blog...
es.html
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1|||Hi Linchi, thank you for responding back and the guidance. I'll try rewritin
g
it again.
Linchi Shea wrote:[vbcol=seagreen]
>Could you shed some light on what you are trying to accomplish with the CAS
E
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>
>[quoted text clipped - 20 lines]
Message posted via http://www.droptable.comsql

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
End
Could you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:

> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>
|||Hi
Some ideas
http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>
|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:[vbcol=seagreen]
>Hi
>Some ideas
>http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
|||Hi Linchi, thank you for responding back and the guidance. I'll try rewriting
it again.
Linchi Shea wrote:[vbcol=seagreen]
>Could you shed some light on what you are trying to accomplish with the CASE
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>[quoted text clipped - 20 lines]
Message posted via http://www.droptable.com

Insert using a case condition

Hello, SQL Gurus, I am trying to use the case statement in the following
logic and just can't seem to get it to work. Any help would be greatly
appreciated. Thanks a bunch.
INSERT INTO dbo.match_table
SELECT
a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
New_entry, a.PRODTYPE
from BAL_table a JOIN Tier2_table b
ON
b.Custtype = a.Custtype
and b. ICPROFCT = a.ICPROFCT
and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
set TIER2 = 'C092'
Else
set TIER2 = 'C094'
EndCould you shed some light on what you are trying to accomplish with the CASE
expression? I can't tell from your post code. Is it supposed to be part of
the SELECT statement (perhaps in its where clause) or part of the join
condition?
Note that CASE ... END is an expression in T-SQL, not a statement. As an
expression, you can't enclose a T-SQL statement in it.
Linchi
"mbouck" wrote:
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi
Some ideas
http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
"mbouck" <u32265@.uwe> wrote in message news:6ec0823937b99@.uwe...
> Hello, SQL Gurus, I am trying to use the case statement in the following
> logic and just can't seem to get it to work. Any help would be greatly
> appreciated. Thanks a bunch.
>
> INSERT INTO dbo.match_table
> SELECT
> a.[Glsubact], a.[Dcoscat], [a.Revreduc],a.[Custtype], b.[Tier2] , b.
> New_entry, a.PRODTYPE
> from BAL_table a JOIN Tier2_table b
> ON
> b.Custtype = a.Custtype
> and b. ICPROFCT = a.ICPROFCT
> and ( substring(b.Profctr,1,2)+ '00' ) = a.Profctr
> CASE WHEN(PRODTYPE not in ('01','04') and New_entry = 'N') Then
> set TIER2 = 'C092'
> Else
> set TIER2 = 'C094'
> End
>|||Hi Uri, the case examples that you have posted are very helpful, I will try
them. thanks for the help.
Uri Dimant wrote:
>Hi
>Some ideas
>http://dimantdatabasesolutions.blogspot.com/2007/02/some-case-expression-techniques.html
>> Hello, SQL Gurus, I am trying to use the case statement in the following
>> logic and just can't seem to get it to work. Any help would be greatly
>[quoted text clipped - 20 lines]
>> End
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||Hi Linchi, thank you for responding back and the guidance. I'll try rewriting
it again.
Linchi Shea wrote:
>Could you shed some light on what you are trying to accomplish with the CASE
>expression? I can't tell from your post code. Is it supposed to be part of
>the SELECT statement (perhaps in its where clause) or part of the join
>condition?
>Note that CASE ... END is an expression in T-SQL, not a statement. As an
>expression, you can't enclose a T-SQL statement in it.
>Linchi
>> Hello, SQL Gurus, I am trying to use the case statement in the following
>> logic and just can't seem to get it to work. Any help would be greatly
>[quoted text clipped - 20 lines]
>> End
--
Message posted via http://www.sqlmonster.com

Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA PaulTry something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
..
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
David Portas
SQL Server MVP
--

Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA Paul
Try something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>
|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
...
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
David Portas
SQL Server MVP

Insert Trigger with If Statement

I want to check if a field has a 0 in it, if it does it should run some
code, if not do nothing
If field1 = 0 then
some code
end if
Please advise of the syntax for this
TIA PaulTry something on these lines:
If Exists (Select 1 from <Table> where field = 0)
Begin
-- the activity you wanted to do
End
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Paul Goldney" <paulg@.wizardit.co.uk> wrote in message
news:ca97dk$pm3$1$8302bc10@.news.demon.co.uk...
> I want to check if a field has a 0 in it, if it does it should run some
> code, if not do nothing
> If field1 = 0 then
> some code
> end if
> Please advise of the syntax for this
> TIA Paul
>|||Remember that more than one row may be inserted at once. Maybe one row = 0
and another row is <> 0. Probably you just need a WHERE clause on whatever
statement(s) are in "some code" instead of an IF statement:
?
...
FROM Inserted WHERE col = 0
You can test for col = 0 in an IF statement using EXISTS but crucially this
tests for the presence of *any* row where col = 0, which may or may not be
what you want in your trigger.
IF EXISTS
(SELECT *
FROM Inserted
WHERE col = 0)
--
David Portas
SQL Server MVP
--sql

Insert Trigger

Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
VensiaTry:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia

Insert Trigger

Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
Try:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia

Insert Trigger

Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
VensiaTry:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia

INSERT the OUTPUT of update statement - a neat trick that doesn't work?

Here's the code
ALTER procedure [dbo].[BalanceUpdate]
As
declare @.DateX datetime
set @.DateX = CONVERT(varchar(10),dateadd(hour,6,getda
te()),120)
--INSERT INTO Payment (Summ, UserID, Reason, RelatedOrderID, DT)
UPDATE [User] SET Balance = Balance - Cost, PaidThru = DATEADD(month,
1, ISNULL(PaidThru, @.DateX))
OUTPUT PayPlan.Cost, inserted.ID, 'Monthly payment blah blah blah',
null, @.DateX
FROM [User]
INNER JOIN PayPlan ON Payplan.ID = PayplanID
WHERE (PaidThru <= @.DateX or PaidThru is null) AND (Cost = 0 OR
(Balance >= Cost)) and Confirmed = 1
You see the commented insert statement - in theory it should work, in
practice it says syntac error. I tried surrounding the update with
SELECT * FROM (...) tmp but it's still syntax error.
Is there any way to make this trick work? I don't want to write an
ugly cursor! Woops. Problem solved, nm|||Could you elaborate? Where was the syntax error?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1182716922.462969.87000@.k79g2000hse.googlegroups.com...
> Woops. Problem solved, nm
>