Help optimising a stored proc

Hi I have the following procedure that accepts two CSV lists of values,
the first list contains primary key numbers, and the second values to
update.
When the list gets over about 200 items, I am getting intermittent
Timout errors.
Currently just over 500,000 records in the table.
Is there a way to optimise the performace of the update? Or is theer an
easier way if the input can be provided in a single list
e.g 2245=1,2257=2,3367=2 instead of
2245,2257,3367 and 1,2,2
Thanks!
CREATE Procedure dbo.UpdateResults
@.RegistrationIDs Varchar(8000),
@.Results Varchar(4000)
AS
UPDATE tblRegistrations
SET Result
= (SELECT A.Value FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value )
WHERE EXISTS (SELECT *
FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
WHERE RegistrationID=B.Value)
CREATE FUNCTION dbo.Split
(
@.List varchar(8000),
@.SplitOn nvarchar(5)
)
RETURNS @.RtnValue table
(
Id int identity(1,1),
Value nvarchar(150)
)
AS
BEGIN
While (Charindex(@.SplitOn,@.List)>0)
Begin
Insert Into @.RtnValue (value)
Select
Value =
ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
Set @.List =
Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
End
Insert Into @.RtnValue (Value)
Select Value = ltrim(rtrim(@.List))
Return
ENDI would try using temporary tables instead of table variables. I have seen
some strange stuff happen when trying to join two table variables together,
or joining a table-valued function together with a real base table.
Move the logic of the dbo.split funtion inside the stored procedure, take
the input csv strings and write them into a temporary table, then process
from that temp table.
"hals_left" wrote:

> Hi I have the following procedure that accepts two CSV lists of values,
> the first list contains primary key numbers, and the second values to
> update.
> When the list gets over about 200 items, I am getting intermittent
> Timout errors.
> Currently just over 500,000 records in the table.
> Is there a way to optimise the performace of the update? Or is theer an
> easier way if the input can be provided in a single list
> e.g 2245=1,2257=2,3367=2 instead of
> 2245,2257,3367 and 1,2,2
> Thanks!
>
> CREATE Procedure dbo.UpdateResults
> @.RegistrationIDs Varchar(8000),
> @.Results Varchar(4000)
> AS
> UPDATE tblRegistrations
> SET Result
> = (SELECT A.Value FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value )
> WHERE EXISTS (SELECT *
> FROM dbo.Split(@.Results,',') A
> JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
> WHERE RegistrationID=B.Value)
>
> CREATE FUNCTION dbo.Split
> (
> @.List varchar(8000),
> @.SplitOn nvarchar(5)
> )
> RETURNS @.RtnValue table
> (
> Id int identity(1,1),
> Value nvarchar(150)
> )
> AS
> BEGIN
> While (Charindex(@.SplitOn,@.List)>0)
> Begin
> Insert Into @.RtnValue (value)
> Select
> Value =
> ltrim(rtrim(Substring(@.List,1,Charindex(
@.SplitOn,@.List)-1)))
> Set @.List =
> Substring(@.List,Charindex(@.SplitOn,@.List
)+len(@.SplitOn),len(@.List))
> End
> Insert Into @.RtnValue (Value)
> Select Value = ltrim(rtrim(@.List))
> Return
> END
>|||Hi There,
What Mark suggested is right but you may like to try this .
UPDATE T1
SET Result = A.Value
>From FROM dbo.Split(@.Results,',') A
JOIN dbo.Split(@.RegistrationIDs,',') B ON A.id = B.id
Join tblRegistrations T1 On T1.RegistrationID=B.Value
and remove the split function If possible .
With Warm regards
Jatinder Singh
Mark Williams wrote:
> I would try using temporary tables instead of table variables. I have seen
> some strange stuff happen when trying to join two table variables together
,
> or joining a table-valued function together with a real base table.
> Move the logic of the dbo.split funtion inside the stored procedure, take
> the input csv strings and write them into a temporary table, then process
> from that temp table.
> --
> "hals_left" wrote:
>|||Thanks Jatinder, that works nice on a small test and is much simpler
SQL.
I'l test in on the real database and see how it compares to the
original
I dont really see how can I remove the split function, unless I pass
the data 1 record at a time in a loop ...

help on insert a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS
"Hongyu Sun" wrote:

> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violated
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good luck

help on insert a record on sql server with identity column as key

Hi, All:
Please help. I use sql server as back end and Access 2003 as front end
(everything is DAO).
A table on SQL server has an identity column as the key.
We have trouble on adding records to this table using the following SQL.
strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
E FROM myTableonAccessLocal"
db.execute strSQL
The schema of the table "myTableOnSQLServer" and the schema of the table
"myTableonAccessLocal" are all the same except that the "myTableOnSQLServer"
has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" and
the table "myTableonAccessLocal" does not have a key.
When we try to run the query, it gives errors indicating the key is violated
or missing.
Should I figure out the autonumber for it first and then add to the SQL
server table?
Many thanks,
HS"Hongyu Sun" wrote:

> Hi, All:
> Please help. I use sql server as back end and Access 2003 as front end
> (everything is DAO).
> A table on SQL server has an identity column as the key.
> We have trouble on adding records to this table using the following SQL.
> strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D
,
> E FROM myTableonAccessLocal"
> db.execute strSQL
> The schema of the table "myTableOnSQLServer" and the schema of the table
> "myTableonAccessLocal" are all the same except that the "myTableOnSQLServe
r"
> has an identity column (ID). The key of the "myTableOnSQLServer" is "ID" a
nd
> the table "myTableonAccessLocal" does not have a key.
> When we try to run the query, it gives errors indicating the key is violat
ed
> or missing.
> Should I figure out the autonumber for it first and then add to the SQL
> server table?
> Many thanks,
> HS
As a common an identity column generates values by itself. If you need to
insert values into an identity column use this command:
SET IDENTITY_INSERT myTableOnSQLServer ON
After the insert has been completed issue the following statement:
SET IDENTITY_INSERT myTableOnSQLServer OFF
Good lucksql

Help needed with select query!

Hi All!

i have 2 tables

TABLE1
--
DESC
CODE
SEVERITY

CODE is the key here.
(SEVERITY ranges from 0 to 7)

TABLE2
--
CODE1
CODE2
CODE3
CODE4
CUST_ID
CALL_NBR

CUST_ID and CALL_NBR form the key.
CODE1 to CODE4 reference CODE in TABLE1

There can be at max 2 CALL_NBR per CUST_ID in TABLE2 i.e. at max 2
records per customer.

Now, what i need is

Extract 4 codes (CODE1, CODE2, CODE3, CODE4 frOM TABLE2...[remember,
there can be a max of 8 codes per customer]) into 4 variables per
customer based on priority defined in TABLE1This design definitely needs a rethink. The 4 Code columns are called a
repeating group and you should not have repeating groups in tables. There's
not really enough information to go on but I would expect a correctly
normalised design to look something like this:

CREATE TABLE CallCodes (descript VARCHAR(20) NOT NULL UNIQUE, code INTEGER
PRIMARY KEY, severity INTEGER NOT NULL CHECK (severity BETWEEN 0 AND 7))

CREATE TABLE CustomerCalls (cust_id INTEGER /* REFERENCES Customers
(cuist_id) */, call_nbr INTEGER, code INTEGER REFERENCES CallCodes (code),
PRIMARY KEY (cust_id, call_nbr, code) /* or (cust_id, call_nbr)? */)

Don't use Desc as a column name because it's a reserved word. When posting,
include DDL (CREATE TABLE statements, as above) with your posts so that it's
clear what the keys, constraints and data types are.

To find the 5 highest priority codes for each customer, assuming 7 is the
highest priority:

SELECT cust_id, code
FROM CustomerCalls AS A
WHERE code IN
(SELECT TOP 5 WITH TIES U.code
FROM CustomerCalls AS U
JOIN CallCodes AS O
ON U.code = O.code
WHERE U.cust_id = A.cust_id
ORDER BY O.severity DESC)

Note that if you had six codes for a customer with priorities 7,6,5,4,3,3
then this query will return all six codes, not five. You didn't specify what
you wanted to do if there were tied priorities so I've left them all in.

> Extract 4 codes (CODE1, CODE2, CODE3, CODE4 frOM TABLE2...[remember,
> there can be a max of 8 codes per customer]) into 4 variables per
> customer based on priority defined in TABLE1

Four variables per *customer*? You shouldn't need to do that in a relational
database. Please explain what you want to do and someone should be able to
suggest an alternative method.

--
David Portas
SQL Server MVP
--|||[Repost]
...

To find the 4 highest priority codes for each customer, assuming 7 is the
highest priority:

SELECT cust_id, code
FROM CustomerCalls AS A
WHERE code IN
(SELECT TOP 4 WITH TIES U.code
FROM CustomerCalls AS U
JOIN CallCodes AS O
ON U.code = O.code
WHERE U.cust_id = A.cust_id
ORDER BY O.severity DESC)

Note that if you had five codes for a customer with priorities 7,6,5,4,4
then this query will return all five codes, not four. You didn't specify
what you wanted to do if there were tied priorities so I've left them all
in.

> Extract 4 codes (CODE1, CODE2, CODE3, CODE4 frOM TABLE2...[remember,
> there can be a max of 8 codes per customer]) into 4 variables per
> customer based on priority defined in TABLE1

Four variables per *customer*? You shouldn't need to do that in a relational
database. Please explain what you want to do and someone should be able to
suggest an alternative method.

--
David Portas
SQL Server MVP
--|||Thanks for the response.

Well! I do understand that it is not a proper relational system but,
redesigning the table schemas is ruled out since it is a existing system
with lots of applications using it. The reason we have the 4 codes
stored per customer as different columns is coz we get these from an
external system based on many crietria,.

Here the DDLs of my 2 existing tables
CREATE TABLE [dbo].[MIX310] (
[REASON_CODE] [varchar] (2) NULL ,
[REASON_CODE_DESCRIPTION] [varchar] (80) NULL ,
[SEVERITY_CODE] [numeric](1, 0) NULL ,
[REASON_CODE_DESCRIPTION_SL] [varchar] (80) NULL
) ON [PRIMARY]
GO
note: SEVERITY ranges from 0 to 7

CREATE TABLE [dbo].[CUSTSWRESP] (
[CUST_ALT_ID] [varchar] (15) NOT NULL ,
[CALL_NBR] [numeric](1, 0) NOT NULL ,
[INPUT_MSG] [ntext] NULL ,
[MESSAGE_1] [varchar] (2) NULL ,
[MESSAGE_2] [varchar] (2) NULL ,
[MESSAGE_3] [varchar] (2) NULL ,
[MESSAGE_4] [varchar] (2) NULL ,
[SCORE] [varchar] (5) NULL ,
[RECOMMENDATION] [varchar] (2) NULL
(there are many other cols here...)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CUSTSWRESP] ADD
CONSTRAINT [PK_CUSTSWRESP] PRIMARY KEY CLUSTERED
(
[CUST_ALT_ID],
[CALL_NBR]
) ON [PRIMARY]
GO

Note: The only things of revelance in this case in CUSTSWRESP table are
the [CUST_ALT_ID], [MESSAGE_1],[MESSAGE_2],[MESSAGE_3] and [MESSAGE_4]
columns in our case.
Also, the MESSAGE_1...4 (message codes) refer to the REASON_CODE in
MIX310.

As I had earlier said, there can be at max 2 CALL_NBR for CUST_ALT_ID
i.e. at max 2 records per customer in CUSTSWRESP which implies have at
max 8 message codes. Also, all or just some of these message codes may
have values.

Now, i need to extract upto 4 REASON_CODE_DESCRIPTION from MIX310 based
on SEVERITY_CODE per CUST_ALT_ID where the message codes[MESSAGE_1...4]
in CUSTSWRESP match the REASON_CODE in MIX310.

I need urgent help with this. Please , let me know if there is any other
details needed.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I'll assume that reason_code is unique in Mix310 even though that table
doesn't have a primary key (!).

CREATE VIEW CustomerMessages
(cust_alt_id, reason_code)
AS
SELECT cust_alt_id, message_1
FROM
(SELECT cust_alt_id, message_1
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_2
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_3
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_4
FROM Custswresp) AS M
WHERE message_1 IS NOT NULL

SELECT cust_alt_id, reason_code
FROM CustomerMessages AS T
WHERE reason_code IN
(SELECT TOP 4 WITH TIES C.reason_code
FROM CustomerMessages AS C
JOIN Mix310 AS M
ON C.reason_code = M.reason_code
WHERE C.cust_alt_id = T.cust_alt_id
ORDER BY M.severity_code DESC)

--
David Portas
SQL Server MVP
--|||Hi David!

Thanks for the quick response.

I could get it working and it gives the desired outcome.

I'll most probably twist it a little to use a #temp table instead of a
view since i m using it inside a stored procdure which recieves
cust_alt_id as input parameter.

I'll repost it when i m done for your comments since i m still a sql
novice.

Thanks!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you just wanted to return a result for a single customer at a time you
can do this:

SELECT TOP 4 WITH TIES M.reason_code, M.reason_code_description
FROM
(SELECT cust_alt_id, message_1
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_2
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_3
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_4
FROM Custswresp) AS
C (cust_alt_id,reason_code)
JOIN Mix310 AS M
ON C.reason_code = M.reason_code
WHERE C.cust_alt_id = @.cust_alt_id
ORDER BY M.severity_code DESC

--
David Portas
SQL Server MVP
--|||O! thatz a great help!!

I had atually started going the temp table way...

A lil more help...this returns me the top 4 reason codes descriptions.
Now, what i need (and what i apoligize for not having elaborated
earlier) is to assign these to 4 to local variables within the procedure
since i need them for some other processing and also return them to the
component which is invoking the stored procedure.

I need something like...
SELECT @.EmpiricaFirstFactor = ISNULL(REASON_CODE_DESCRIPTION,'')
SELECT @.EmpiricaSecondFactor = ISNULL(REASON_CODE_DESCRIPTION,'')...and
so on.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Rather than assign four variables and then manipulate them further, try to
extend my query to produce the end result you require and then return that
result set from the stored procedure. In general you should try to keep
step-by-step procedural processing to a minimum in SQL.

One problem with your specification is the handling of tied values. If you
have only four variables but you have five different reason_codes with
severities 7,6,5,4,4 , which values of reason_code_description do you want
to return? The two values with severity 4 might have different descriptions
but you only want to return one of them. At least add reason_code into the
ORDER BY clause so that you get a consistent (but not necessarily useful)
result in that case:
...
ORDER BY M.severity_code DESC, C.reason_code

If you must assign four variables in your SP then you can try the following.
However this is a rather questionable, undocumented and possibly not 100%
reliable method of doing this. I strongly recommend that you return the
values as a result set instead.

DECLARE @.empirica1 VARCHAR(80), @.empirica2 VARCHAR(80), @.empirica3
VARCHAR(80), @.empirica4 VARCHAR(80)

SELECT TOP 4
@.empirica1 = @.empirica2,
@.empirica2 = @.empirica3,
@.empirica3 = @.empirica4,
@.empirica4 = M.reason_code_description
FROM
...

--
David Portas
SQL Server MVP
--|||>> I do understand that it is not a proper relational system but,
redesigning the table schemas is ruled out since it is a existing
system with lots of applications using it. <<

LOL! Everytime I read "redesigning the schema is not allowed", I keep
thinking that: (1) This guy has decided that bankruptcy and total
failure ARE allowed! (2) I'm going to get a consulting job with a huge
daily rate in about a year

If this is true, then the application and the backend have been
coupled together much, much too tightly.

>> the reason we have the four codes stored per customer as different
columns is because we get these FROM an external system based on many
criteria. <<

Unh?? That has absolutely nothing to do with how the database stores
the facts. Quit mimicking a physical file layout.

>> here the DDLs of my two existing tables <<

MIX310 is not a table and it can never be a table. There are no keys
and with the NULL-able columns,there can never be a key. You failed
to put in a known constraint on the table. Your datatypes are wrong
-- VARCHAR(2) is a bitch for the front end guys who have to pad it out
to print it; use CHAR(n) for short codes.

You talk about joining on reason codes, but have no such column in
CUSTSWRESP. Did you actually give the same data element mulitple names
in the schema? Do you have not DRI between the tables?

This is such a mess you need to start over.

CREATE TABLE MIX310
(reason_code CHAR(2) NOT NULL PRIMARY KEY,
reason_description VARCHAR (80) NOT NULL, -- punch card width!
severity_code INTEGER NOT NULL -- code description in another table?
CHECK (severity BETWEEN 0 AND 7),
);

>> .. Which implies have at most 8 message codes. <<

So design a table something like this:

CREATE TABLE CustMessages
(cust_id VARCHAR (15) NOT NULL
REFERENCES Customers (cust_id)
ON UPDATE CASCADE,
message_nbr INTEGER DEFAULT (1) NOT NULL
CHECK (message_nbr BETWEEN 1 AND 8), -- enforce business
rule
message_txt TEXT NOT NULL,
reason_code CHAR(2) NOT NULL
REFERENCES MIX310 (reason_code)
ON UPDATE CASCADE,
...
PRIMARY KEY (cust_id, call_nbr));

Now use "message_nbr BETWEEN 1 and 4" to get that sample. Ordering the
messages by severity is a simple update.

Right now, you have no data integrity in the current schema. All your
queries will convoluted nightmares that produce erroneous results. If
you cannot fix it, you might want to update your resume and try to
find a company that will be in business.

Help needed with Primary Key and Identity

I have some code in my ASP.NET page which uses a SQL 2000 Database that was created before creating the ASP Page. The problem I'm having is using an insert statement such as the following example from the DATAGRID example on the Matrix Product. I want the option to create new rows but my Primary Key doesn't allow Nulls and when I hard code a number in the first field of my table for my ID...it's not automatically generated. I've looked through this forum but I'm having some problems understanding what others have done with Identity or GUID's...etc...:

Sub AddNew_Click(Sender As Object, E As EventArgs)

' add a new row to the end of the data, and set editing mode 'on'

CheckIsEditing("")

If Not isEditing = True Then

' set the flag so we know to do an insert at Update time
AddingNew = True

' add new row to the end of the dataset after binding

' first get the data
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds)

' add a new blank row to the end of the data
Dim rowValues As Object() = {"", "", ""}
ds.Tables(0).Rows.Add(rowValues)

' figure out the EditItemIndex, last record on last page
Dim recordCount As Integer = ds.Tables(0).Rows.Count

If recordCount > 1 Then

recordCount -= 1
DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize

End If

' databind
DataGrid1.DataSource = ds
DataGrid1.DataBind()

End If

End Subds.Tables(0).Columns("YourPrimaryKey").IncrementSeed = 1
ds.Tables(0).Columns("YourPrimaryKey")... other properties you need to set to make it an identity.|||Right now I'm using an "ID" field as the primary key and it is setup in SQL as an identity but when I try to add a row...it says..

System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'CustomerInfo' when IDENTITY_INSERT is set to OFF. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.OrderEdit_aspx.DataGrid_Update(Object Sender, DataGridCommandEventArgs E

Would I need to remove the identity setting in SQL and create the identity through my code or is there a way to specify that my "ID" field is a primary key and it needs to be incremented by 1 whenever a new row is added?

Thanks for the help!!

Help needed on select and insert query

a bit of a begginer's request here.
I have a table called t_user in which username is the primary key and one of
the fields is a decimal called bsl.
I'm trying to write a stored procedure that selects the right record based
on the username (input from a cookie) and then inserts the bsl value based o
n
the users input into a textbox on the asp.net page
the stored proc code is
CREATE PROCEDURE addbsl
@.bsl decimal,
@.username varchar(20)
AS
SELECT username FROM t_user
WHERE username = @.username
insert into t_user (bsl) values (@.bsl)
GO
and when i run it in the query analyser it tells me:
Server: Msg 515, Level 16, State 2, Procedure addbsl, Line 8
Cannot insert the value NULL into column 'username', table
'FYProj.dbo.t_user'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(1 row(s) affected)
Stored Procedure: FYProj.dbo.addbsl
Return Code = -6
I understand that it is trying to insert a username as well but cannot
because you cannot enter a null value but I don't want it to input a sername
i just want it to insert data into the record with the specified username
Is there a way to change the sproc to get it to work or am i just taking the
wrong approach?You will have to use an UPDATE statement to update an existing row. INSERT
always insert a new row.
Hello neil_pat,

> a bit of a begginer's request here.
<snip>
Lasse Vgsther Karlsen
http://www.vkarlsen.no/
mailto:lasse@.vkarlsen.no
PGP KeyID: 0x0270466B

Help needed in Table partition

Hi,
I'm doing horizontal partition with partition key as an
integer datatype.It's a part of primary key also.When i
search for a particular value, it's fetching correct
results ,but if we examine execution plan all the
partitions are searched for a particular search.
Please help me to fetch the correct result in execution
plan
Thanks in advance,
Sunish
is your primary key clustered? Are you saying the the execution plan does a
table scan?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Sunish" <sunish_007@.hotmail> wrote in message
news:732801c4761d$d3435170$a501280a@.phx.gbl...
> Hi,
> I'm doing horizontal partition with partition key as an
> integer datatype.It's a part of primary key also.When i
> search for a particular value, it's fetching correct
> results ,but if we examine execution plan all the
> partitions are searched for a particular search.
> Please help me to fetch the correct result in execution
> plan
> Thanks in advance,
> Sunish
|||hi,
yea, the primary key is a clustered one.When i checked
the execution p[lan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||hi,
yea, the primary key is a clustered one.When i check
the execution plan it shows Clustered Index Scan for
each partition ......also instead of compute saclar it's
showing Concatenation ,Cost=2%.
Thanks in advance
Sunish

>--Original Message--
>is your primary key clustered? Are you saying the the
execution plan does a
>table scan?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Sunish" <sunish_007@.hotmail> wrote in message
>news:732801c4761d$d3435170$a501280a@.phx.gbl...
>
>.
>
|||I think this is normal.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
<anonymous@.discussions.microsoft.com> wrote in message
news:873601c47855$f7adc3d0$a601280a@.phx.gbl...[vbcol=seagreen]
> hi,
> yea, the primary key is a clustered one.When i checked
> the execution p[lan it shows Clustered Index Scan for
> each partition ......also instead of compute saclar it's
> showing Concatenation ,Cost=2%.
> Thanks in advance
> Sunish
>
> execution plan does a
|||hi,
Since it has to search for the corresponding partition
only rather than searching the entire partition views ,i
think i am wrong some where .
also in the execution plan ,it has to show compute scalar
instead of concatenation, as i referred to articles which
i collected from the net.
please inform if there is any other way i need to perform
this .
my query is like this only
select * from detailstable where programcode =222
[vbcol=seagreen]
>--Original Message--
>I think this is normal.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:873601c47855$f7adc3d0$a601280a@.phx.gbl...
it's[vbcol=seagreen]
as an[vbcol=seagreen]
also.When i[vbcol=seagreen]
execution
>
>.
>

Help needed in sp_pkeys

Hai ,

I am in the situation to find the p.key field from a table.So i use

sp_pkeys 'table' .It works nice.

But i want to select the pkey field alone instead of the rest of the
informations supplied by the sp.

How to get the P.key field name alone from a table .Or If u tell the
way to store the results of sp_pkeys 'table' into a table also ok to
me.

With Regards
Raghu"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0402030621.2bb732b@.posting.google.co m...
> Hai ,
> I am in the situation to find the p.key field from a table.So i use
> sp_pkeys 'table' .It works nice.
> But i want to select the pkey field alone instead of the rest of the
> informations supplied by the sp.
> How to get the P.key field name alone from a table .Or If u tell the
> way to store the results of sp_pkeys 'table' into a table also ok to
> me.
>
> With Regards
> Raghu

You didn't mention which version of SQL Server you have, but in 7/2000, you
can use the INFORMATION_SCHEMA views:

select
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
kcu.COLUMN_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
where
tc.CONSTRAINT_TYPE = 'PRIMARY KEY' and
tc.TABLE_SCHEMA = 'dbo' and
tc.TABLE_NAME = 'MyTable'

Alternatively, you can put the output of sp_pkeys into a table:

create table #keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
go
insert into #keys
exec sp_pkeys 'MyTable'
go

Simon|||
Hai Simon

Thanks for your code.

I am in Sql server 7.0 and i make use of it

Raghu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help needed

Hi all

I m having three tables youth,clubs,ythclubs. youth may participate in any no of clubs.
in youth youth_id is the key attribute. In clubs club_id is the key attribute.Table ythclubs consists the columns youth_id,club_id. My requirement is
If user selects a club, it should give the list of youths in that club along with the some columns of youth(for ex lastname,firstname like this)

Sample data is
This is in Youths table This is in clubs table
Youthid club_id
10005 10003
10006 10004
10007 10005
10008 10000

In ythclub the data is
club_id youthid
10005 10005
10003 10005
10004 10005
10006 10006
10005 10012
10006 10012

Thank u

Baba

It sounds to me like you should be able to do this with a select that joins the Youth and ythclub tables:

Code Snippet

declare @.Youth table
( YouthId integer,
lastName varchar(12),
firstName varchar(12)
)
insert into @.Youth
select 10005, 'Rubble', 'Fred' union all
select 10006, 'Flintstone', 'George' union all
select 10007, 'Jetson', 'Barney' union all
select 10008, 'Douglas', 'Jed' union all
select 10012, 'Kiley', 'Oliver'
--select * from @.youth

declare @.ythClub table
( club_id integer,
youthId integer
)
insert into @.ythClub
select 10005, 10005 union all
select 10003, 10005 union all
select 10004, 10005 union all
select 10006, 10006 union all
select 10005, 10012 union all
select 10006, 10012
--select * from @.ythClub

declare @.targetClub integer
set @.targetClub = 10005

select a.club_id,
a.youthId,
b.lastName,
b.firstName
from @.ythClub a
join @.Youth b
on a.youthId = b.youthId
and a.club_id = @.targetClub
order by b.lastName, b.firstName

/*
club_id youthId lastName firstName
-- --
10005 10012 Kiley Oliver
10005 10005 Rubble Fred
*/

If you need anything from the CLUB table -- such as the name of the club -- then you will also need to join to the CLUB table.

HELP ME...........

It my ddl table:
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
and my data like it:
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 6:31:00 1
1 1/1/2005 1/1/2005 16:30:00 1
1 1/1/2005 1/1/2005 16:30:00 0
1 1/1/2005 1/1/2005 16:33:00 0
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 6:28:00 1
2 1/1/2005 1/1/2005 6:32:00 1
2 1/1/2005 1/1/2005 16:30:00 0
2 1/1/2005 1/1/2005 16:31:00 0
2 1/1/2005 1/1/2005 16:45:00 0
2 1/1/2005 1/1/2005 16:45:00 0
I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) of
group noid,fdate is not deleted. So its data will be;
noid fdate ftime fstatus
---
1 1/1/2005 1/1/2005 6:30:00 1
1 1/1/2005 1/1/2005 16:33:00 0
2 1/1/2005 1/1/2005 6:27:00 1
2 1/1/2005 1/1/2005 16:45:00 0
Can u help me? How sintax sql? Can it be solved with one statement?Hi
I think Steve Kass has already provided solution for you .
Would you mind to post a sample data when you ask for help or at least to
fix your current DDL
This is one of the many options that others provided
CREATE TABLE [dbo].[TTEMP_BC] (
[RecID] [int] IDENTITY (1, 1) Primary Key,
[NOID] [INT],
[FDATE] [smalldatetime] NULL ,
[FTIME] [smalldatetime] NULL ,
[FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 06:31:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (1,' 1/1/2005','1/1/2005 16:33:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:27:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:28:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 06:32:00',1)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:30:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:31:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
INSERT INTO [dbo].[TTEMP_BC] VALUES (2,' 1/1/2005','1/1/2005 16:45:00',0)
SELECT * FROM
(
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=1
GROUP BY noid
UNION ALL
SELECT noid,MIN(ftime)as ftime
FROM TTEMP_BC WHERE fstatus=0
GROUP BY noid
) AS Der
ORDER BY noid
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:uK2KgVQkFHA.3336@.tk2msftngp13.phx.gbl...
> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime))
of
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>|||Try,
delete t1
where
recid !=
case
when fstatus = 1 then (select min(a.recid) from t1 as a where a.fstatus = 1
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
min(b.ftime) from t1 as b where b.fstatus = 1 and b.noid = t1.noid and
b.fdate = t1.fdate))
when fstatus = 0 then (select max(a.recid) from t1 as a where a.fstatus = 0
and a.noid = t1.noid and a.fdate = t1.fdate and a.ftime = (select
max(b.ftime) from t1 as b where b.fstatus = 0 and b.noid = t1.noid and
b.fdate = t1.fdate))
end
go
AMB
"Bpk. Adi Wira Kusuma" wrote:

> It my ddl table:
> CREATE TABLE [dbo].[TTEMP_BC] (
> [RecID] [int] IDENTITY (1, 1) Primary Key,
> [FDATE] [smalldatetime] NULL ,
> [FTIME] [smalldatetime] NULL ,
> [NOID] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
> [FSTATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL
> )
> and my data like it:
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 6:31:00 1
> 1 1/1/2005 1/1/2005 16:30:00 1
> 1 1/1/2005 1/1/2005 16:30:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 6:28:00 1
> 2 1/1/2005 1/1/2005 6:32:00 1
> 2 1/1/2005 1/1/2005 16:30:00 0
> 2 1/1/2005 1/1/2005 16:31:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> 2 1/1/2005 1/1/2005 16:45:00 0
> I want to delete. if fstatus =1 so fisrt record (min(Ftime)) of group
> noid,fdate is not deleted. but if fstatus =0 so last record (max(Ftime)) o
f
> group noid,fdate is not deleted. So its data will be;
> noid fdate ftime fstatus
> ---
> 1 1/1/2005 1/1/2005 6:30:00 1
> 1 1/1/2005 1/1/2005 16:33:00 0
> 2 1/1/2005 1/1/2005 6:27:00 1
> 2 1/1/2005 1/1/2005 16:45:00 0
> Can u help me? How sintax sql? Can it be solved with one statement?
>
>
>

Help me to understand this SQL sentence

Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')what is that sql sentence supposed to be doing?

it looks like it's trying to be recursive|||If we are trying to get some value into the variable , then this query is no good.
"Set @.mSQL = 'SELECT Max([AccountID] "
should actuallu read
"SELECT @.mSQL = Max([AccountID] "

Hope this helps...|||First we must identify the subject, then the verb, and if they exist, the direct object and the indirect object....Oh, sorry.

Any ideas what the value of @.mSQL was before this assignment? Maybe the original programmer was trying to reduce the number of variables he had? (OK, I am reaching, there)|||It looks to me like the code is creating a "Superkey", a concatenation of multiple natural values to fabricate a single unique semi-surrogate key.

Superkeys are database abominations frequently found in legacy systems or in applications created by noob developers.|||Hi all,
The original procedure as follow

--Repaired 03/11/2005

CREATE Procedure IV_spStockReportSummary
(
@.mName Varchar(50),
@.mtxtDate DateTime,
@.moptName TinyInt,
@.mchkReport Bit
)

As
Declare @.mSQL Varchar(3000)

Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + @.mName + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '
Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')

If @.mchkReport=0
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End) AS CategoryName,
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End) AS Unit, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblItemList I INNER JOIN CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN
IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND
LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON C.AccountID = LD.AccountID ON I.ItemID = LD.ItemID INNER JOIN IV_tblUnitOfMeasureList U ON
I.InvUnitOfMeasr = U.UMID INNER JOIN IV_tblCategoryList CI ON I.CategoryID = CI.CategoryID INNER JOIN IV_tblStoreHouseList S ON
LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End), LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then CI.CategoryName Else CI.CategoryName_Secn End),
LD.ItemID, (Case When ' + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End), (Case When ' + '' + @.moptName + '' + '=1 Then U.UMName Else U.UMName_Secn End), L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')
End

Else
Begin
Set @.mSQL='SELECT LD.AccountID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End) AS AccountName,
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End) AS StoreHouseName,
LD.ItemID, L.LotNo, L.ExpireDate, SUM(MC.BeginUnit) AS OnHand, SUM(MC.BeginTotal) AS Amount INTO [xIV_tblStockSumTmp' + @.mName + ']
FROM CF_tblChartAcct C INNER JOIN [xIV_tblStockSumLastDate' + @.mName + '] LD INNER JOIN IV_viewIVMasterCalc MC ON LD.[KEY] = MC.[Key] AND
LD.AccountID = MC.AssetAcctID AND LD.ItemID = MC.ItemID AND LD.StorehouseID = MC.StorehouseID AND LD.BINID = MC.BINID AND LD.LotItemID = MC.LotItemID ON
C.AccountID = LD.AccountID INNER JOIN IV_tblStoreHouseList S ON LD.StorehouseID = S.StoreHouseID LEFT JOIN IV_tblLotNumbers L ON LD.LotItemID = L.LotItemID
GROUP BY LD.AccountID, (Case When ' + '' + @.moptName + '' + '=1 Then C.AccountName Else C.AccountName_Secn End),
LD.StorehouseID, (Case When ' + '' + @.moptName + '' + '=1 Then S.StoreHouseName Else S.StoreHouseName_Secn End),
LD.ItemID, L.LotNo, L.ExpireDate
HAVING (SUM(MC.BeginUnit) <> 0) OR (SUM(MC.BeginTotal) <> 0)')

Set @.mSQL = 'SELECT S.AccountID, S.AccountName, S.StorehouseID, S.StoreHouseName, S.ItemID, (Case When '
Exec (@.mSQL + '' + @.moptName + '' + '=1 Then I.ItemName Else I.ItemName_Secn End) AS ItemName,
(Case When ' + '' + @.moptName + '' + '=1 Then U1.UMName Else U1.UMName_Secn End) AS Unit,
I.CategoryID, (Case When ' + '' + @.moptName + '' + '=1 Then C.CategoryName Else C.CategoryName_Secn End) AS CategoryName, S.LotNo, S.ExpireDate,
(S.OnHand * (Case When V.ConvFactor IS Null Then 1 Else V.ConvFactor End)) AS OnHand, S.Amount, Convert(Varchar(10), Null) AS txtGrp INTO [xIV_tblStockSummaryTmp' + @.mName + ']
FROM IV_tblUnitOfMeasureList U1 LEFT JOIN IV_tblUMConversion V ON U1.UMID = V.UMToID RIGHT JOIN IV_tblUnitOfMeasureList U ON
V.UMFromID = U.UMID RIGHT JOIN IV_tblItemList I ON U1.UMID = I.PrintUnitOfMeasr AND U.UMID = I.InvUnitOfMeasr LEFT JOIN
IV_tblCategoryList C ON I.CategoryID = C.CategoryID RIGHT JOIN [xIV_tblStockSumTmp' + @.mName + '] S ON I.ItemID = S.ItemID')

End

Return

GO|||i can't believe that runs

and the guy that wrote it should be shot

it no longer looks like it's trying to be recursive

but there's a dangling ) after the first Exec, just before If @.mchkReport=0|||i can't believe that runs
...
but there's a dangling ) after the first Exec, just before If @.mchkReport=0Nor me. There look to be a lot of dangly things.

dangquanghai - are you saying that this actually works?

--Repaired 03/11/2005
Doesn't look like it from here|||I m sure It run smoothly.|||I can give you the examble from SQL server book online

C. Use EXECUTE 'tsql_string' with a variable
This example shows how EXECUTE handles dynamically built strings containing variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables (type = U).

Note This example is shown for illustrative purposes only.

DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @.tablename sysname
FETCH NEXT FROM tables_cursor INTO @.tablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
/* A @.@.FETCH_STATUS of -2 means that the row has been deleted.
There is no need to test for this because this loop drops all
user-defined tables. */.
EXEC ('DROP TABLE ' + @.tablename)
FETCH NEXT FROM tables_cursor INTO @.tablename
END
PRINT 'All user-defined tables have been dropped from the database.'
DEALLOCATE tables_cursor|||Uh-Oh... you said the forbidden word... "cursor"...|||Oh sorry friends !!!
this sentence
Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= 'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')

contain two sentences
1. Set @.mSQL = 'SELECT Max([AccountID] + [ItemID] + [StorehouseID] + [BINID] + [LotItemID] + Convert(varchar(10),[BalDate],111)) AS [KEY],
AccountID, ItemID, StorehouseID, BINID, LotItemID INTO [xIV_tblStockSumLastDate' + ']
FROM IV_tblIVMaster
WHERE (BalDate<= '

2.'Exec(@.mSQL + '''' + @.mtxtDate + '''' + ')
GROUP BY ItemID, AccountID, StorehouseID, BINID, LotItemID')

The coder typed it at the same row so it make me confuse
Now, It is so clear
Thank for your consideration|||Uh-Oh... you said the forbidden word... "cursor"...I think that was to explain to us what EXEC does :)|||Ah. So that is what EXEC does. I had no idea. Apparently it is a convenient method for f***ing up an application. The posted code demonstrates it clearly.

Help me pls - with database and Insert statement

I Have an error:

Server Error in '/quanlythietbi' Application.

---

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_yeucau_nhanvien'. The conflict occurred in database 'equipment', table 'nhanvien', column 'manv'. The statement has been terminated.

Source Error:

Line 129:mycommand.Parameters.Add(new SqlParameter("@.noidung_yc1",System.Data.SqlDbType.Text));Line 130:mycommand.Parameters["@.noidung_yc1"].Value = TextBox1.Text;Line 131:int i = mycommand.ExecuteNonQuery();Line 132:if (i>0)Line 133:{

and this is my code:

string sqlstring = "Select * from yeucau where ngayGiaiQuyetxong='"+ Label8.Text +"' and date_yc='" + Label7.Text + "' and manv_yc='"+ TextBox2.Text + "' and noidung_yc='"+ TextBox1.Text+ "'";

myconnection =new SqlConnection(stringconn);

mycommand =new SqlCommand(sqlstring,myconnection);

myconnection.Close();

myconnection.Open();

mycommand =new SqlCommand(insertquery,myconnection);

mycommand.Parameters.Add(new SqlParameter("@.ngayGiaiQuyetxong1",System.Data.SqlDbType.Char,10));

mycommand.Parameters["@.ngayGiaiQuyetxong1"].Value = Label8.Text;

mycommand.Parameters.Add(new SqlParameter("@.date_yc1",System.Data.SqlDbType.SmallDateTime));

mycommand.Parameters["@.date_yc1"].Value = Label7.Text;

mycommand.Parameters.Add("@.manv_yc1",System.Data.SqlDbType.Char,10);

mycommand.Parameters["@.manv_yc1"].Value = TextBox2.Text;

mycommand.Parameters.Add(new SqlParameter("@.noidung_yc1",System.Data.SqlDbType.Text));

mycommand.Parameters["@.noidung_yc1"].Value = TextBox1.Text;

int i = mycommand.ExecuteNonQuery();

if (i>0)

{

lbcheck.Text = "?ã C?p Nh?t Yêu C?u.";

}

---------------------------------------

I don't know what I must do to repair it :(

How does insertquery look like? The error comes from an INSERT command, so you should check the INSERT command to see whether it tries to insert a row which conficts with the FK constraint. Such error will be raised if you try to insert a row with a field value that does not exist in the PRIMARY KEY (in another table) referenced by the FOREIGN KEY. To learn more about FOREIGN KEY constraint, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_8ypg.asp

|||

thank a lot :) I will try to fix it now