Sproc/identity Question
Mar 26, 2002
Hello,
This may be a partial ASP question, but here goes...
This procedure returns a 'Item cannot be found in the collection corresponding to the requested name or ordinal' error. The sp runs ok and completes the insert ok, but returns nothing for @@identity...any ideas?
Thank you!
Ty
--------------------Procedure----------------------------
CREATE procedure insert_order
@officeID int,
@orderNo varchar(50),
@ProofReqID int,
@ShipMethID int,
@orderDate datetime,
@notes varchar(200),
@userID int
AS
BEGIN
INSERT INTO orders (
officeID, orderno,
proofreqid, shipmethid,
orderdate, notes,
userid )
VALUES (
@officeID, @orderno,
@proofreqid, @shipmethid,
@orderdate, @notes,
@userid )
SELECT @@IDENTITY as orderID
END
GO
----------------ASP code---------------------------------
SQL = "insert_order '"& int(officeID) & "','" & (OrderNo) & "','" & int(ProofReqID) &"','" & int(shipmethID) & "', '" & (date) & "','" & (notes) & "','" & int(UserID) &"'"
Set sRS=results.Execute(SQL)
rsIdentity = sRS("orderID")
View 1 Replies
ADVERTISEMENT
Jun 12, 2002
I have 2 tables (Master / Detail)
and a SP which does the following:
1) Inserts a record in the Master table (table1) where the key is an identity column
2) Then, takes the identity value of Table 1 and procedes to insert the details in the detail table (table2)with this identity number.
Here is an example of how the SP is coded:
CREATE PROCEDURE dbo.LogEventEx
Here are a Bunch of Parameters
declare @lasterror int
declare @last bigint
BEGIN TRAN
.....
BEGIN
INSERT INTO BI_V_Transacciones ( CodBan, NacCli, CedRif, Login, Accion, Objeto, DirIP, Canal, CodRes,MtoDeb,Concepto,CtaDeb)
VALUES (@CodBan,@NacCli,@CedRif,@Login,@Accion,@Objeto,@D irIP,@Canal,@CodRes,@MtoDeb,@Concepto,@CtaDeb)
set @lasterror = @@error
if @lasterror <> 0
begin
rollback tran
return @lasterror
end
SET @last = @@IDENTITY
INSERT INTO BI_V_SChequeras (ID,Direccion, Status, ObsTra, CodSud,Accion)
VALUES (@last,@Direccion,@Status,@ObsTra,@CodSud,@Accion)
if @lasterror <> 0
begin
rollback tran
return @lasterror
end
END
COMMIT TRAN
select '<mantis v="1.0"><resultado estatus="OK" /><respuesta/></mantis>'
return 0
------------
BI_V_Schequeras and BI_V_Transcacciones are Views where the people who implement this are inserting on.
This procedure is heavily used in a transactional application (High Cuncurreny). The SP is called by DLL SOAP Wrapped
Overlooking the fact that the use of views is not the recommended approach for inserting:
The REAL problem is that some insertions are going wrong in the detail table.
This is because the identities recorded in the detail table (table2) are not the right identity value but they are higher than the identity of its parent in table1.
With this behaviour, the Master/Detail relationship is lost from the conceptual perspective (Wrong detail data assigned to master record -- different identities)
We are seeing here the statement @@Identity inside of a Transaction, so we asume the identity value returned is the on of the Session, However in some cases the identity inserted is a superior like the identity inserted is the one of other session????
I've been thinking to replace the @@Identity with Identity_Scope() function, but I'm not quite sure this will resove the issue.
Some clues or help about this issue???
Thanks,
View 1 Replies
View Related
Jun 26, 2006
Hi. I am trying to figure out the code for sorting a manual (non-identity) number column in my table. the purpose is to
show the user's pictures in perfect order (1,2,3,4,5,6...).
The Jist of my problem... When a user first inserts six pictures, he gets:
|1|
|2|
|3|
|4|
|5|
|6|
All is good. But, say he deletes picture |3|. Now the list order looks like this:
|1|
|2|
<- |3| is removed
|4|
|5|
|6|
And, then he inserts two more pictures, now he his this:
|1|
|2|
|4|
|5|
|6|
|7| <- |7| & |8| are added
|8|
What i want to acheive is a "reshuffling" of the number order every time a picture is removed. So, when |3| is removed, |4| becomes |3|, |5| becomes |4| and so on. There should never be a gap in the order.
I am new to stored procedures, and have been trying to figure this out. Below is my guesswork:
Code:
ALTER PROCEDURE dbo.sp_NewPersonalPic
(
@photo_name VARCHAR(50) = NULL,
@photo_location VARCHAR(100) = NULL,
@photo_size VARCHAR(50) = NULL,
@user_name VARCHAR(50) = NULL,
@photo_caption VARCHAR(150) = NULL,
@photo_default BIT = NULL,
@photo_private BIT = NULL,
@photo_number INTEGER = NULL,
@photo_date DATETIME = NULL
)
AS
BEGIN
SELECT @photo_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
BEGIN
SET @photo_number = 1
SELECT
@photo_number = (
SELECT COUNT(*)
FROM dbo.PersonalPhotos b
WHERE
a.photo_date < b.photo_date
)
FROM
dbo.PersonalPhotos a
ORDER BY
a.photo_date
END
BEGIN
My thinking is that it would be a safe bet to use the "photo_date" column as a litmus for my "photo_number" column (ie, the most recent record inserted by the user will always be at a later date than the previously inserted record). So:
photo_number photo_date
|1| 2006-06-26 21:43:36.653
|2| 2006-06-26 21:43:50.000
|3| 2006-06-26 21:45:25.217
|4| 2006-06-26 21:45:33.763
|5| 2006-06-26 22:39:42.670
|6| 2006-06-26 22:39:49.200
If |3| is removed above, the numbers are reordered based on the time of entry sequence.
Any suggestions on how to acheive this in my stored procedure? Currenly, i get the correct order, but it goes crazy when i delete and add.
Thanks and sorry for the verbose post.
View 5 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
Jul 9, 2006
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
View 10 Replies
View Related
Jun 30, 2006
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
View 12 Replies
View Related
Jan 25, 2015
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
View 6 Replies
View Related
Sep 19, 2005
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies
View Related
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Aug 1, 2014
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
View 4 Replies
View Related
Jan 25, 2008
This sproc seems to be way over my head. First off, let's start with the scenario. I have two tables. tblInventory and tblTempCart. Each contain an ItemID and Quantity. I need an sproc that will loop through the rows in tblTempCart and sum the quantity of each ItemID. Then, it needs to update the quantity in tblInventory based on what has been ordered for that ItemID.
What I have tried thus far:
UPDATE dbo.[4HCamp_tblStoreInventory]SET Quantity = Quantity - (SELECT SUM(dbo.[4HCamp_tblStoreTempCart].Quantity) AS Quantity FROM dbo.[4HCamp_tblStoreTempCart] WHERE dbo.[4HCamp_tblStoreTempCart].ItemID = dbo.[4HCamp_tblStoreInventory].ItemID)
This works other than if the ItemID doesn't exist in tblTempCart, then it updates the quantity in tblInventory to NULL instead of retaining it's current value. I have no experience with looping in sql so any help will be greatly appreciated.
Thanks!
Amanda
View 1 Replies
View Related
May 2, 2008
I am trying to design a stored procedure to list out all of the unique software items that have been approved. There are multiple tables involved: CISSoftware, Software, Manufacturers, SoftwareTypes. Despite putting DISTINCT, I am still receiving rows of records where the software title (the title field) is a duplicate. Why is this query not working? Am I overlooking something?
SELECT DISTINCT CISSoftware.SoftwareID, Software.Title, Manufacturers.ManufacturerID, Manufacturers.ManufacturerName, SoftwareTypes.SoftwareTypeID, SoftwareTypes.Type
FROM CISSoftware, Software, Manufacturers, SoftwareTypes
WHERE CISSoftware.SoftwareID = Software.SoftwareID
AND Software.ManufacturerID = Manufacturers.ManufacturerID
AND Software.SoftwareTypeID = SoftwareTypes.SoftwareTypeID
View 4 Replies
View Related
Apr 10, 2004
hi all,
I'm trying to learn using sproc in ASP.NET, but ran into problems I couldn't solve. Here're the details
My Table (JournalArticle)
ArticleID - int (PK)
ArticleTitle - varchar
ArticleContent - text
I could run a normal sql string against the table itself in ASP.NET and got the results I expect.
but when using a sproc, i couldn't get anything
The sproc
CREATE PROCEDURE dbo.sp_ArticleSearch(@srch text)
AS SELECT ArticleID, ArticleTitle, ArticleContent
FROM dbo.JournalArticle
WHERE (ArticleAbstract LIKE @srch)
GO
After reading some of the threads here, I experimented by changing ArticleContent and @srch to type varchar, still no luck, it's not returning anything.
I think the problem is when i set the value of @srch (being new at this, I could be seriously wrong though), like this:
prmSearch.ParameterName = "@srch"
prmSearch.SqlDbType = SqlDbType.Text
prmSearch.Value = Request.Form("txtSearch")
My original string looks like this
strSQL = "SELECT * FROM JournalArticle WHERE (ArticleContent LIKE '%" & Request.Form("txtSearch") & "%')"
What am I doing wrong?? Thanks in advance for any help.
View 7 Replies
View Related
Dec 2, 2004
I have tried to mix this around every way I can think of but the procedure inserts two rows instead of one. You will notice that I specify two commands/sprocs. I did that as part of my trying everything. when it was one command/sproc it did the same thing... What am I doing wrong? Please Help! :)
___________________
SPROC:
___________________
CREATE PROCEDURE dbo.sp_addMembershipRole
@INCID Int
AS
declare @literal NVarChar (10)
SET @literal = 'RTRListing'
INSERT INTO dbo.RTR_memberPermissions ([memberID], [Role])
VALUES (@INCID, @literal)
GO
___________________
CODE:
___________________
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Security.Cryptography;
using System.Web.Security;
namespace admin
{
/// <summary>
/// Summary description for createMembership.
/// </summary>
public class createMembership : System.Web.UI.Page
{
protected System.Web.UI.WebControls.RequiredFieldValidator Vusername;
protected System.Web.UI.WebControls.RequiredFieldValidator Vpassword;
protected System.Web.UI.WebControls.RequiredFieldValidator Vretype;
protected System.Web.UI.WebControls.TextBox username;
protected System.Web.UI.WebControls.TextBox password;
protected System.Web.UI.WebControls.TextBox retype;
protected System.Web.UI.WebControls.Label lblError;
protected System.Web.UI.WebControls.Label lblDate;
protected System.Web.UI.WebControls.Button btnAdd;
private void Page_Load(object sender, System.EventArgs e)
{
if(! Page.IsPostBack)
{
string StrContactID = Request.QueryString["CID"].ToString();
Session["CID"]= StrContactID;
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
public void btnAdd_Click(object sender, System.EventArgs e)
{
int intContactID;
string StrCID = Session["CID"].ToString();
intContactID= Int32.Parse(StrCID);
if(password.Text != retype.Text)
{
lblError.Text = "Retyping of your desired password did not match. Please try again.";
return;
}
string pwdSalt = CreateSalt(5);
string pwd = password.Text;
string pwdHash = CreatePasswordHash(pwd, pwdSalt);
string connStr= "server=****; uid=****; pwd=****; database=****";
string cmdStr= "sp_addMembershipUandP";
string cmd2Str= "sp_addMembershipRole";
SqlConnection CONN = new SqlConnection(connStr);
CONN.Open();
SqlCommand cmd = new SqlCommand(cmdStr, CONN);
cmd.CommandType = CommandType.StoredProcedure;
SqlCommand cmd2= new SqlCommand(cmd2Str, CONN);
cmd2.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50);
param.Value = username.Text;
param = cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50);
param.Value = pwdHash;
param = cmd.Parameters.Add("@salt", SqlDbType.NVarChar, 50);
param.Value = pwdSalt;
param = cmd.Parameters.Add("@CID", SqlDbType.Int, 4);
param.Value = intContactID;
SqlParameter param2;
param2 = cmd2.Parameters.Add("@INCID", SqlDbType.Int, 4);
param2.Value = intContactID;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
CONN.Close();
return;
}
private static string CreateSalt(int size)
{
RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();
byte[] buff = new byte[size];
rng.GetBytes(buff);
return Convert.ToBase64String(buff);
}
private static string CreatePasswordHash(string pwd, string salt)
{
string saltAndPwd = String.Concat(pwd, salt);
string hashedPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(saltAndPwd, "SHA1");
return hashedPwd;
}
}
}
View 2 Replies
View Related
Jan 22, 2004
create procedure
GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
as
begin
declare @ZipID integer
declare @AddrID integer
set @AddrID=1
if lTrim(@Addr1)<>''
EXEC @ZipID= dbo.GetZipID(@City,@State,@Zip5)
set @AddrID = (select Min(lngAddrID) from dbo.Addrs where lngZipCodeID=@ZipID and Address1=@Addr1 and Address2=@Addr2)
return(@AddrID)
end
GO
In the above sproc I m trying to call another sproc GetZipID . Its giving me an error stating that
"Incorrect syntax near @City. "
Can you help me out? The same syntax works for passing one variable but not for three.
FYI this is the other sproc
CREATE PROCEDURE dbo.GetZipID(@City varchar(30), @State char(2), @Zip5 char(6))
AS
BEGIN
DECLARE @CityID integer
DECLARE @StateID integer
DECLARE @ZipID integer
SET @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
EXEC @CityID = dbo.GetCityID @City
EXEC @StateID = dbo.GetStateID @State
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
Return @ZipID
print @ZIPID
END
GO
Thx in advance
View 1 Replies
View Related
May 17, 2004
ha ha ,
havent used sql serv in years, and having trouble wit me sprocet! What devilish deed did i do?
Use Contacts
GO
CREATE PROC MaserIn
@entreddatetime= GETDATE(),
@initialsvarchar(2)=Null,
@Vendorvarchar(50),
@CkNovarchar(20),
@expTypevarchar(15),
@ckDatedatetime,
@ckAmtmoney,
@mIdintOUTPUT
AS
INSERT INTO [Contacts].[dbo].[Master](
[entered], [initials], [Vendor],
[CkNo], [expType], [ckDate], [ckAmt])
VALUES(@entered,
@initials,
@Vendor,
@CkNo,
@expType,
@ckDate,
@ckAmt)
SELECT @mID=@@iDENTITY
View 6 Replies
View Related
Nov 28, 2007
Hi.
I wanted to know if its possible to do this in a sproc.
if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.
If yes how can i do it..
Any help will be appreciated.
Regards
Karen
View 8 Replies
View Related
Dec 19, 2007
Hi, I am trying to Implement Multi parameter...
If i give NULL it works fine but if i give '7,4' I get this error message Msg 102, Level 15, State 1, Line 18 Incorrect syntax near '17'.
This is my sproc
ALTER Procedure [dbo].[usp_GetOrdersByOrderDate]
@ClientId nvarchar(max)= NULL,
@StartDate datetime,
@EndDate datetime
AS
Declare @SQLTEXT nvarchar(max)
If @ClientId IS NULL
Begin
Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC
END
ELSE
BEGIN
SELECT @SQLTEXT = 'Select
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
FROM
[Order] o
Inner Join ClientPlan cp on o.PlanId = cp.PlanId
Inner Join ClientUser c on o.CreatedByUserId = c.UserId
WHERE
cp.ClientId in (' + @ClientId + ')
AND
o.OrderDate BETWEEN ' + Convert(varchar,@StartDate) + ' AND ' + convert(varchar, @EndDate) + '
ORDER BY
o.OrderId DESC'
execute (@SQLTEXT)
END
any help will be appreciated.
Regards
Karen
View 4 Replies
View Related
Jul 5, 2004
hi all
i need to design a SPROC which will return me top n rows from a table.
like GetTopN 4, will give me top 4 tuples
/**
GetTopN
To list top n rows
Date - 05 July 2004
Yogesh Jangam
*/
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'GetTopN' AND type = 'P')
DROP PROCEDURE GetTopN
GO
/************************ Actual SP Code *********************************/
Create procedure GetTopN (@intN int)
AS
BEGIN
SET NOCOUNT ON
declare
SELECT TOP @intN from Employee -- this part i am not able to write
end
is there a answer?
Thanks
View 2 Replies
View Related
Sep 21, 2005
How can I determine when a sproc or table was last used?
I suspect that I have many obsolete tables and sprocs in my database but how can I find out for sure??
Thanks,
DL
View 5 Replies
View Related
May 14, 2006
I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?
Here is my complete code:
USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP
GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE@ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE@CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
While @ListStr<>''
Begin
Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
If @CP<>0
Begin
Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
End
Else
Begin
Set @SV=Cast(@ListStr as varchar)
Set @ListStr=''
End
Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End
Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit
and my VB6 Code:
Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String
Set adoConn = New ADODB.Connection
adoConn.Open connString
Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command
With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"
Set adoRS = .Execute
Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop
End With
Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub
Any ideas?
Thanks
View 2 Replies
View Related
Sep 23, 2006
I'm optimizing an Access mdb to run in front of a SS 2005 database. My approach is to move as much of the processing as possible out of Access and in to SQL Server.
I have a report uses an Access query as it's source. One field in that report is generated via a series of 4 or 5 sub-queries that are finally joined in to the report's source query.
I have enough knowhow to turn each individual Access query into a veiw inside SQL server, but I'm wondering if this wouldn't be better accomplished using a stored procedure?
Essentially, I'd need the sproc to open up a set of 50-60 records, loop through them until it finds the first record with certain criteria, then return a certain value as it's result. Finally, I need that vallue to be joined to a view that I will point to as the source for my report in Access.
Is it possible to do this with a sproc? Is this the right way to use a sproc?
View 9 Replies
View Related
Mar 11, 2008
Hi SQLersI'm in an unusual position of using a SQL box I'm not an admin of. To make it worse, it is SQL 2k. I haven't used this in anger for over a year. I don't even have the 2k tools installed and I'm just using SSMS.sp_adduser ain't working. I've nosed around in master and the reason why is somebody, for some reason, has created a sproc called (you guessed it) master.dbo.sp_adduser.I can't poke around as I would like and I don't have a 2k instance to bugger about with. Correct me if I am wrong but if there is some user defined master.dbo.sp_adduser sproc then there can't be a system sproc to add users? CREATE USER is SQL 2k5 too right? Users do get created by EM by the team managing the box so what command does this use? Anyone know or (flutters eyelashes) fancy running a profiler trace on EM to see?I want to convert my SQL 2k5 security objects script for SQL 2k. I can create the objects in SSMS so not critical."Overriding" system sprocs is not something I would ever do and I have no equivalent testing environment so any help highly appreciatedCheers
View 12 Replies
View Related
Jan 20, 2004
CREATE proc dbo.sp_address ( @Abbr char(2) )
as
DECLARE @StateID int
SET @Abbr = UPPER(ISNULL( @Abbr, '' ))
SET @StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @Abbr )
set @StateID=53
IF ( @StateID is null )
INSERT into dbo.States( strAbbr, strName ) VALUES( @Abbr, @Abbr )
if @@ERROR = 0
SET @StateID = @@Identity
return(@StateID)
GO
Can I execute the above stored procedure in a function like this:
create function
sf_GetStateID( @Abbr char(2))
returns integer
begin
declare @StateID int
exec sp_address
return(@StateID)
end
I just want to cross check.
Tks
View 1 Replies
View Related
Jan 23, 2004
I am trying to insert a value with Insert statement in a sproc
create procedure AddNewPersonsFromALSHeadr
as
begin
insert into dbo.Persons(lngSSN,
strNamePrefix,
strFirstName,
lngHomeID,
lngMailID)
select lngFedTaxID,
strNamePrefix,
strFirstName,
exec @HomeId = GetAddress H.strAddress1,strAddress2,H.strCity,H.strState,H.s trZip,H.intZip4,
@HomeId
from dbo.ALSHeadr H
end
go
The probl is while inserting the fourth column values I have to execute a sproc
"GetAddress" and take the value and insert it in "lngHomeID" and the the same value in
"lngMailID". I tried all possible ways for the syntax but 'm getting the error
incorrect sytanx near exec and also syntax error near " . " at H.strAddress1 etc......
FYI this is the sproc GetAddress(I omitted few lines in this sproc)
CREATE procedure dbo.GetAddress(@Addr1 varchar(40), @Addr2 varchar(40), @City varchar(30), @State char(2), @Zip5 char(6), @Zip4 smallint)
as
begin
------------------------------------------
------------------------------------------
--------------------------------------
EXEC @ZipID= dbo.GetZipID @City,@State,@Zip5
return(@AddrID)
print (@AddrID)
end
GO
Tks
View 3 Replies
View Related
May 20, 2008
Hi ALL,
wondering if anybody can help me, i've writen a stored procedure to query my database, i want to be able to added a wildcard into a parameter incase i don't want to use it.
(don't laugh at my efforts!!!!:))
i've tried
IF @<PARAM> = NULL
@<PARAM> = LIKE %
I sure this is a simple thing to add basically i need to replace a null with everything!!
can anybody help?
View 3 Replies
View Related
Oct 30, 2007
I'm trying to create a stored procedure on my master database that I can query other databases. The reason behind putting it on the master db is so I will have one place to modify the stored procedure instead of potentially a hundred different databases. I am trying to create a stored procedure like the one below, but it seems to not like it and I get the error below. Any suggestions are appreciated.
DECLARE @uName AS nvarchar(30), @dbName as nvarchar(5)
SET @uName='TU'
SET @dbName = '00000'
DELETE FROM [DASHDB].[dbo].users WHERE Username=@uName
EXECUTE('USE ['+@dbName+']')
DELETE FROM DashboardBranches WHERE Username=@uName
DELETE FROM MyDashboardSettings WHERE Username=@uName
DELETE FROM MyFavorites WHERE Username=@uName
DELETE FROM MyModules WHERE Username=@uName
DELETE FROM MyStocks WHERE Username=@uName
DELETE FROM BoardPackage WHERE PackageID IN (SELECT ID FROM BPackages WHERE Username=@uName)
DELETE FROM BPackages WHERE Username=@uName
ERROR:
Msg 208, Level 16, State 1, Line 6
Invalid object name 'DashboardBranches'.
Thanks,
Josh
View 4 Replies
View Related
Aug 16, 2005
HiI can't seem to get this procedure to work correctly. Here's my code:DECLARE @PropertyDetails nvarchar (6)Select @PropertyDescription = PropertyDescription from Property whereApplicationID = @applicationidIf @PropertyDescription is not null or @PropertyDescription <> ''BeginIf (select isnumeric(PropertyDescription) from Property where ApplicationID =@applicationid) = 1INSERT INTO #errors VALUES (1410,@ApplicationID, 0, 'Y')ELSEINSERT INTO #errors values (1410, @ApplicationID, 0, 'N')EndI am trying to bring up an error advising users not to capture alphabets in afield called Property Description.I need to bring up the error from the #ERRORS table with the rule number 1410.My Syntax checks successful, but my error does not come up for the users. AmI missing something?Thanks for any help at all, guys.Driesen Pillay--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200508/1
View 5 Replies
View Related
Oct 1, 2005
Hi guysI having trouble with this sproc. I get the following error when testing:Server: Msg 245, Level 16, State 1, Procedure UTL_CompletenessCheckLoan, Line231Syntax error converting the varchar value 'danwet w125 ' to a column of datatype int.I have declared @Sellername as Varchar. Please can someone tell me what I'mdoing wrong? All it needs to do is return the 'Y' value if there are numericsin the sellername.declare @sellername varchar(40),@applicationid INTselect @sellername = sellername from Seller where ApplicationId =@ApplicationIdIf @sellername is not null or @sellername <> ''beginif (select isnumeric(Sellername) from seller where ApplicationId =@ApplicationId) = 1select 'Y'ELSEselect 'N'endThanks for any helpDriesen--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200509/1
View 4 Replies
View Related
Jun 7, 2007
HiI am doing a check and on failing, i have a raiseerror command.I was assuming, once proc reaches raiseerror, it would stop the proc,but i see that the proc gives me an error message, but continues torun thru all the rest of the steps after raiseerror.how do i make the proc quit after reading raiseerror, only thrulabels ?IF @CheckIFFileHasOnlyOneOutputType 1BEGINRAISERROR ('Process Stopped. Input xls file is invalid, as it has morethan one output type specified in the OutputType Column', 16, 1)END--more proc steps are here ...Thanks in advance :RS
View 1 Replies
View Related
Jul 20, 2005
HiI've written this sproc. Can any one suggest any optimisations to themain query and its sub-queries please? Truncated DDL afterCREATE PROCEDURE dbo.spGetUnbilledInfoByClient(@ClientID varchar(5),@Sort int = 0,@Dir int = 0) ASSET NOCOUNT ON-- The Main QuerySELECT mmatter, mdesc1, tkfirst + ' ' + tklast AS Contact,CONVERT(varchar(10), mopendt, 103) As [Date], CONVERT(DECIMAL(18,2),tTime.Cost) AS COST, tTime.Hours, tDisbursements.DisbursementsFROM matterINNER JOIN timekeep ON mbillaty = timekeep.tkinitlEFT JOIN (SELECT cmatter, SUM(Disbursements) AS Disbursements FROM (SELECT dbo.cost.cmatter,CASE WHEN dbo.cost.cauth = 'SO' OR dbo.cost.cauth = 'CF'THEN dbo.cost.cbillamt * 1.175ELSEdbo.cost.cbillamtEND AS DisbursementsFROM dbo.costWHERE (dbo.cost.cstatus = 'b') AND (dbo.cost.cinvoice IS NULL) AND(LEFT(dbo.cost.cmatter, 5) = @ClientID)) AS costsGROUP BY cmatter) AS tDisbursementsON dbo.matter.mmatter = tDisbursements.cmatterINNER JOIN(SELECT dbo.timecard.tmatter, SUM(dbo.timecard.tbillhrs) AS Hours,SUM(dbo.timecard.tbilldol) AS CostFROM dbo.timecard JOIN dbo.batch ON dbo.timecard.tbatch =dbo.batch.bbatchWHERE (dbo.timecard.tinvoice IS NULL)AND (dbo.timecard.tstatus = 'b')AND (LEFT(dbo.timecard.tmatter, 5) = @ClientID)AND (dbo.timecard.tbilldt IS NULL)AND ( NOT dbo.batch.bfindt IS NULL)GROUP BY tmatter) AS tTimeON dbo.matter.mmatter = tTime.tmatter--Sort (see http://www.sqlteam.com/forums/topic...942&whichpage=3)ORDER BYCASE -- numeric fieldsWHEN @Sort = 4 AND @Dir = 0THEN tTime.CostWHEN @Sort = 5 AND @Dir = 0THEN tTime.HoursWHEN @Sort = 6 AND @Dir = 0THEN tDisbursements.DisbursementsEND DESC,CASE -- datetimeWHEN @Sort = 0 AND @Dir = 0THEN mopendtWHEN @Sort = 3 AND @Dir = 0THEN mopendtEND DESC,CASE -- character fieldsWHEN @Sort = 1 AND @Dir = 0THEN mdesc1WHEN @Sort = 2 AND @Dir = 0THEN tkfirst + ' ' + tklastEND DESC,-- ASCCASE -- numeric fieldsWHEN @Sort = 4 AND @Dir = 1THEN tTime.CostWHEN @Sort = 5 AND @Dir = 1THEN tTime.HoursWHEN @Sort = 6 AND @Dir = 1THEN tDisbursements.DisbursementsEND ASC,CASE -- datetimeWHEN @Sort = 0 AND @Dir = 1THEN mopendtWHEN @Sort = 3 AND @Dir = 1THEN mopendtEND ASC,CASE -- character fieldsWHEN @Sort = 1 AND @Dir = 1THEN mdesc1WHEN @Sort = 2 AND @Dir = 1THEN tkfirst + ' ' + tklastEND ASCGOCREATE TABLE [matter] ([mname] [varchar] (24) COLLATE Latin1_General_CI_AS NULL ,[mmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,[mrtcode] [smallint] NULL ,[mdept] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,[mopendt] [datetime] NULL ,[mbillaty] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,[mdesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [cost] ([cindex] [int] IDENTITY (1, 1) NOT NULL ,[cmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,[camount] [money] NULL ,[cbilldt] [datetime] NULL ,[cbillamt] [money] NULL ,[cinvoice] [int] NULL ,[ccode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,[cbatch] [int] NULL ,[cauth] [varchar] (10) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [costcode] ([cocode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,[codesc1] [varchar] (48) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [timecard] ([tindex] [int] IDENTITY (1, 1) NOT NULL ,[tmatter] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,[ttk] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,[tbilldt] [datetime] NULL ,[tbillhrs] [decimal](16, 2) NULL ,[tbillrt] [decimal](16, 2) NULL ,[tbilldol] [money] NULL ,[tinvoice] [int] NULL ,[tcode] [varchar] (7) COLLATE Latin1_General_CI_AS NULL ,[tbatch] [int] NULL) ON [PRIMARY]GOCREATE TABLE [batch] ([bbatch] [int] IDENTITY (1, 1) NOT NULL ,[btype] [varchar] (2) COLLATE Latin1_General_CI_AS NULL ,[bfindt] [datetime] NULL ,[bop] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,[bper] [varchar] (4) COLLATE Latin1_General_CI_AS NULL ,[bpflag] [varchar] (1) COLLATE Latin1_General_CI_AS NULL ,[bdopen] [datetime] NULL ,[btothrs] [decimal](12, 2) NULL ,[btotdol] [money] NULL) ON [PRIMARY]GO
View 10 Replies
View Related