Stored Procedure Retrieving Duplicate Data
Apr 17, 2008
Hi i have the following stored procedure which should retrieve data, the problem is that when the user enters a name into the textbox and chooses an option from the dropdownlist it brings back duplicate data and data which should be appearing because the user has entered the exact name they are looking for into the textbox. For instance
Pmillio Jones
Pmillio Jones
Pmillio Jones
Robert Walsh
Here is my stored procedure;
ALTER PROCEDURE [dbo].[stream_UserFind]
-- Add the parameters for the stored procedure here
@userName varchar(100),
@subCategoryID INT,@regionID INT
AS
SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,
Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategoriesON
Users.userID= UserSubCategories.userIDINNER JOIN
SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHEREuserName LIKE COALESCE(@userName, userName) OR
SubCategories.subCategoryID = COALESCE(@subCategoryID,SubCategories.subCategoryID);
View 11 Replies
ADVERTISEMENT
Mar 31, 2007
I have created the following stored procedure and tried to retrieve it's output value in C#, however I am getting exceptions. Can anyone tell me what I am doing wrong? Thanks! 1 ALTER PROCEDURE [dbo].[GetCustomerById]
2
3 @CustId NCHAR(5),
4 @CustomerName NVARCHAR(50) OUTPUT
5
6 AS
7 BEGIN
8
9 SELECT @CustomerName = ContactName
10 FROM Customers
11 WHERE CustomerId = @CustId
12
13 END
14
15 RETURN
16
17
18
19
20
21
22 SqlConnection conn = GetConnection(); //retrieves a new SqlConnection
23 SqlCommand cmd = new SqlCommand();
24 cmd.Connection = conn;
25 cmd.CommandType = CommandType.StoredProcedure;
26 cmd.CommandText = "GetCustomerById";
27
28 SqlParameter paramCustId = new SqlParameter();
29 paramCustId.ParameterName = "@CustId";
30 paramCustId.SqlDbType = SqlDbType.NChar;
31 paramCustId.Direction = ParameterDirection.Input;
32 paramCustId.Value = "ALFKI";
33
34 SqlParameter paramCustomerName = new SqlParameter();
35 paramCustomerName.ParameterName = "@CustomerName";
36 paramCustomerName.SqlDbType = SqlDbType.NVarChar;
37 paramCustomerName.Direction = ParameterDirection.Output;
38
39 cmd.Parameters.Add(paramReturn);
40 cmd.Parameters.Add(paramCustId);
41 cmd.Parameters.Add(paramCustomerName);
42
43 conn.Open();
44 SqlDataReader reader = cmd.ExecuteReader();
45
46 string custName = cmd.Parameters["@CustomerName"].Value.ToString();
View 6 Replies
View Related
May 25, 2008
Hi,
For a particular application i retrieve records using stored procedure and bind the same to the data grid and display the same. The data are fetched from a table say A joined with couple of tables to staisfy the requirement. Now this table A has around 3700000 + records. The Select statement is as
Select column1, column 2, column 3...................... column 25
from table A
inner join hash table D
on A.Column3 = D.Column3
and A.Column4 = D.Column4
and a.nColumn1 = @ParameterVariable
Inner join table B
on a.nColumn1 =b.nColumn1
inner join table C
on A.column2 = C.column2
Indices defined on A are
IDX1 : Column 3, Column 4
IDX2 : Column 1
Around 350 records per second are inserted into the table A during the peak operation time. During this time when executing the procedure, it takes around 4 mins to fetch just 25000 records.
I need to fine tune the procedure. Please suggest me on same.
Thanks
View 4 Replies
View Related
Jan 9, 2008
Hi,
I'm at my wit's end with this. I have a simple stored procedure:
PROCEDURE [dbo].[PayWeb_getUserProfile] ( @user_id_str varchar(36), @f_name varchar(50) OUTPUT)AS
BEGINDECLARE @user_id uniqueidentifier;
SELECT @user_id = CONVERT(uniqueidentifier, @user_id_str);
select @f_name=f_name FROM dbo.tbl_user_profile WHERE user_id = @user_id;
ENDRETURN
The proc runs perfectly from the Management Console. In my ASP.net form (C#), the following always results in: "Invalid attempt to read when no data is present." (I'll highlight the line where the error results):
SqlConnection conn = new SqlConnection(<connection string data>);SqlDataReader reader;
SqlCommand comm = new SqlCommand("dbo.PayWeb_getUserProfile", conn);comm.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter f_name = new SqlParameter("@f_name", SqlDbType.VarChar, 50);
f_name.Direction = ParameterDirection.Output;comm.Parameters.Add(f_name);
comm.Parameters.Add("@user_id_str", SqlDbType.VarChar, 36).Value = Membership.GetUser().ProviderUserKey.ToString().ToUpper();reader = comm.ExecuteReader();Response.Write("Num: " + reader["@f_name"].ToString()); // ERROR: Invalid attempt to read when no data is present.
reader.Close();conn.Close();
The data is there. When I put a watch on 'Reader', I see the correct first_name data there.
Your help is appreciated.
View 8 Replies
View Related
Nov 1, 2007
Hi.
I have a stored procedure "sp1" which returns a value (with the sql statement Return @ReturnValue).
Is it possible for my asp.net page to retrieve this return value, and to do it declaratively (meaning without writing code to connect to the database in the code behind). If it is possible to do it like this please tell me how, and if not please tell me how to do it with code.
Thanks in advance .
View 5 Replies
View Related
Feb 28, 2008
Lets say I have the following stored procedure
Code:
View 2 Replies
View Related
Jul 23, 2004
Is there a way to retrieve the parameter list for a given stored procedure?
I am trying to create a program that will autogenerate a list of stored procedures and their parameters so that changes to the database can be accurately reflected in code.
Thanks,
Allen K.
View 1 Replies
View Related
Jul 21, 2000
Hello all,
We have a domain where all computers are on GMT(Greenwitch Mean Time). We have an access front end that timestamps certain fields according to the client time that the program is running on, but we will be moving our client workstations off of GMT time and keep our SQL Server on GMT time, and want to keep the timestamps GMT.
So, I wanted to know if it was possible to create a stored procedure that gets the Server's time and returns it to the Access frontend for entry into the timestamp fields?
Or, if anyone has a better idea of how to get the time from the server to use on the clients, I would greatly appreciate it!!!
Kevin Kraus
View 2 Replies
View Related
Mar 3, 2008
Please excuse me if this question has been asked before; I couldn’t find it. Perhaps someone could point me to the solution.
A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0. I am having very good success however I can’t figure out how to retrieve data from a stored procedure.
I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order. Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc. If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP)
SELECT @MessageCode AS MessageCode, @MessageText AS MessageText
I need to retrieve both the @MessageCode and the @MessageText values in my application. In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset.
In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control. I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedure�. Everything actuly works just fine. The items are added to the order and the business logic in the SP works fine. I just have no way of telling the user that something important has happened.
Does anyone know how I can pickup these values so that I can show them to my users? Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.
View 10 Replies
View Related
Jul 23, 2005
I'm generating a list of parameters needed by stored procedures, and
I'd like to know which ones have default values assigned to them.
To retrieve the parameter information I use:
sp_sproc_columns @Procedure_Name='InsertUser''
However, the column that is supposed to give the default value,
'COLUMN_DEF' always returns as NULL, even when that column has a
default value assigned to it.
i.e.
CREATE PROCEDURE InsertUser@UserID INT = 10,.....
And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
Thanks
View 1 Replies
View Related
Jul 11, 2006
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
View 3 Replies
View Related
Oct 24, 2005
Hi All;
I'm new to SQL, the long and short is I am trying to access some data from an SQL Table to use in my VBA Code in my MS Access front-end.
Here is what I am doing now;
HSTUHoursPriorPeriods = CDbl(Nz(DLookup("[Hours]", "dbo_TIMS_HSTU_HoursHist_Insync", "[eeLink] =" & EmployeeLink), 0))
I am accessing data (YTD Hours for a selected employee) through the view "dbo_TIMS_HSTU_HoursHist_Insync" which is connected by ODBC to my application. The result "HSTUHoursPriorPeriods" is used to determine the rate of pay for a specific employee and is run literall each time a user enters a pay record at data entry.
Because the view itself returns about 1,000 records, before I select the specific record and associated Hours, it is a little slow from a data entry standpoint.
I understand I can achieve the same result through an SQL Procedure, but
for the life of me I don't know how.
ANY thoughts would be greatly appreciated.
View 1 Replies
View Related
Feb 4, 2008
I'm calling the stored procedure below to insert a record but every record is inserted into my table twice. I can't figure out why. I'm using Sql Server 2000. Thanks.CREATE PROCEDURE sp_AddUserLog(@Username varchar(100),@IP varchar(50))AS SET NOCOUNT ONINSERT INTO TUserLogs (Username, IP) VALUES (@Username, @IP)GO Sub AddUserLog(ByVal Username As String)
Dim SqlText As String
Dim cmd As SqlCommand
Dim strIPAddress As String
'Get the users IP address
strIPAddress = Request.UserHostAddress
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
SqlText = "sp_AddUserLog"
cmd = New SqlCommand(SqlText)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
cmd.Parameters.Add("@Username", SqlDbType.VarChar, 100).Value = Username
cmd.Parameters.Add("@IP", SqlDbType.VarChar, 100).Value = strIPAddress
Try
con.Open()
cmd.ExecuteNonQuery()
Finally
con.Close()
End Try
End Sub
View 7 Replies
View Related
Feb 11, 2004
Hi,
I have written the following stored procedure:
alter proc GetProducts
@prodcatint=null
as
select distinct pd.productcategory,pd.imagepath,pd.[description],p.productid,p.[name]
,p.designer,p.weight,p.price
from productdescription pd inner join products p on pd.productcategory=p.productcategory
where @prodcat=p.productcategory
order by p.productid
return
My Results are:
ProductCategory ProductID (Rest of the columns)
22 47
22 47
22 58
22 58
In my productdescription table there are 2 rows in the productcategory column which has number 22. In the products table there are 2 rows(productid 47&58) in the productcategory column which has number 22. I believe this is many to many relationship problem but I do not know how to correct it. My results need to show only 2 records and not 4.
Does anybody have any suggestions.
Thank you in advance,
poc1010
View 4 Replies
View Related
Oct 22, 2007
hi every body
can any onehelp me in making a stored procedure to delete a duplicate rows in tables
thanks in advance
View 9 Replies
View Related
Feb 1, 2005
I have a web app that calculates tax filing status and then stores data about the person.
Facts
The insert is done through a stored procedure.
All the sites that this program is being used are connecting through a VPN so this is not an external site.
The duplicate records are coming from multiple sites (I am capturing there IP address).
I am getting a duplicate about 3 or 4 times a day out of maybe 300 record inserts.
Any help would be greatly appreciated.
There are many sqlcmdInsert.Parameters("@item").Value =
cnTaxInTake.Open()
sqlcmdInsert.ExecuteNonQuery()
cnTaxInTake.Close()
And that is it.
View 6 Replies
View Related
Nov 14, 2014
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
View 1 Replies
View Related
Oct 8, 2007
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source?
Something like this:
CREATE PROCEDURE TestHardDisk
AS
BEGIN
DECLARE CURSOR HardDisk_Cursor
FOR Exec xp_FixedDrives
-- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source
OPEN CURSOR HardDisk_Cursor
FETCH NEXT FROM HardDisk_Cursor
INTO @Drive, @Space
WHILE @@FETCH_STATUS = 0
BEGIN
...
END
END
View 6 Replies
View Related
Jul 23, 2005
What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer
View 4 Replies
View Related
Jan 29, 2008
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
View 6 Replies
View Related
May 15, 2007
Hi,
We are using a simple CLR sp where an SQL statement is executed inside the same. THe execution happens in a server of different context that is connected inside CLR sp. The result set is returned in tabular format using pipe. But, when we access the result as below
create table #t(c1 int, c2 int, c3 int)
insert #t exec TestProc
select * from #t
it give the following error. Any help is appreciated. It seems to be some error with distibuted transaction. We tried adding BEgin Distributed trnasaction, but still the error comes.
Msg 6522, Level 16, State 1, Procedure TestProc, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "TestProc":
System.Transactions.TransactionException: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025) ---> System.Runtime.InteropServices.COMException: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025)
System.Runtime.InteropServices.COMException:
at System.Transactions.Oletx.ITransactionShim.Export(UInt32 whereaboutsSize, Byte[] whereabouts, Int32& cookieIndex, UInt32& cookieSize, CoTaskMemHandle& cookieBuffer)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
System.Transactions.TransactionException:
at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Class1.RunRemoteMetalProc()
Regards,
Kart
View 1 Replies
View Related
Oct 2, 2006
I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.The problem is that cmd.Parameters["@UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.Here is my code:using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("mys_ExistsPersonWithUserName", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("@UserExists", SqlDbType.Int);
cmd.Parameters["@UserExists"].Direction = ParameterDirection.Output;
cn.Open();
int x = (int)cmd.Parameters["@UserExists"].Value;
cn.Close();
return (x>1);
} And the corresponding stored procedure: ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName
(
@UserName varchar(16),
@UserExists int OUTPUT
)
AS
SET NOCOUNT ON
SELECT @UserExists = count(*)
FROM mys_Profiles
WHERE UserName = @UserName
RETURN
View 1 Replies
View Related
Apr 8, 2008
Hello there!
I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with.
Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte datetimesummary nvarchar(50)etryUserID nvarchar(50)rqstStatusCdeKey bigint
ReqStatusCode TablerqstStatusCdeKey bigintstatusCode nvarchar(50)
I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!
View 4 Replies
View Related
May 1, 2006
I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------------------------------------------------------------------------------------------------------------------------
Row# 1 from current stored proc
Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------------------------------------------------------------------------------------------------------------------------
100000 33455 200 300 345555 4455555 5666666 Arizona 56000
103400 22455 900 700 777555 3333555 5444666 Illinois 660000
The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
http://www.webfound.net/CurrentMonthCollections_AZ.txt
http://www.webfound.net/CurrentMonthCollections_IL.txt
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount
) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows
View 7 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Oct 15, 2013
I have written the following stored procedure to export a csv file. I am wanting to put a If statement in here so if view UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW returns nothing then the procedure does not run.
INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_HEADER_VIEW
INSERT INTO UDEF_DISPATCHER_INTERFACE_ORDER_LINE_TABLE_TEMP
SELECT * FROM UDEF_DISPATCHER_INTERFACE_ORDER_LINE_VIEW
DECLARE @BcpHeader AS VARCHAR (2000)
[Code] ....
View 2 Replies
View Related
Feb 4, 2007
I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.
View 5 Replies
View Related
Jan 16, 2008
Hi,I've got some XML which exists as a text variable in a temp table in SQL Server 2000.I need to pass this XML into sp_xml_preparedocument so I can rebuild a table out of it. But I can't figure out the syntax.If I try doing this:declare @idoc intexec sp_xml_preparedocument @idoc output, (select XmlResult from #cache)I get an error, with or without the brackets round the select statement.The temp table is created using an SP, but I can't call that directly either. This:declare @idoc intexec sp_xml_preparedocument @idoc output, exec Search$GetCache @searchIDAlso throws an error.I can't put it into a
local variable because they can't be of type text. I can't pass it into
the SP somewhere as it's being generated on the fly.How can I get my xml into sp_xml_preparedocument?Cheers,Matt
View 3 Replies
View Related
Jan 31, 2008
Can someone tell me why my stored procedure is repeating the Name in the same column?
Here's my stored procedure and output:
select distinct libraryrequest.loanrequestID, titles.title, requestors.fname + ' ' + requestors.lname as [Name],
Cast(DATEPART(m, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.requestDate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.RequestDate) as Varchar(5)) as RequestDate,
Cast(DATEPART(m, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(d, libraryrequest.shipdate) as Varchar(5)) + '/' + Cast(DATEPART(yy, libraryrequest.shipdate) as Varchar(5)) as ShipDate
from LibraryRequest
join requestors on requestors.requestorid=libraryrequest.requestoridjoin titles on titles.titleid = requestors.titleidwhere shipdate is not null
Output:
ID Title Name Request Date Ship Date
29 Heads, You Win Brenda Smith 1/18/2008 1/18/200835 Still More Games Brenda Smith 1/22/2008 1/22/200851 The Key to.. Brenda Smith Brenda Smith 1/29/2008 1/29/200852 PASSION... Brenda Smith Brenda Smith 1/29/2008 1/29/200853 LEADERSHIP Brenda Smith Brenda Smith 1/29/2008 1/29/2008
Going crazy ugh...
View 3 Replies
View Related
Feb 14, 2008
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure.
for example:
alter procedure [dbo].[AddDetails]
(
@IndustryName nvarchar(50),
@CompanyName nvarchar(50),
@PlantName nvarchar(50),
@Address nvarchar(100),
@Createdby int,
@CreatedOn datetime
)
as
begin
insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn)
insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn)
insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp)
end
Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this.
Please help me, i need the solution soon.
View 11 Replies
View Related
Feb 27, 2008
Hi iam working with two dropdownlists,one gets the data dynamically when the pageload.later after selecting particular item in the dropdownlist1 i must get data to the dropdownlist2 depending on 1.
For example:
Dropdownlist1 is for industry and 2 is for company.
when i select particual industry in ddl1 i must get companies based on this industry in ddl2.Both the Industry name and company name are maintained in two different tables industry and company with common field ID.please help me with a stored procedure to sort out this problem...
View 6 Replies
View Related
Jul 27, 2004
Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code.
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 )--,
--@fldFirstName char( 30 ) OUTPUT,
--@fldLastName char( 30 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
GO
############### login page ################
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
Dim strFirstName, strLastName As String
sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
myConn = New SqlConnection(sqlConn)
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
myCmd.Parameters.Add(Trim("@fldUsername"), Trim(strUsername))
myCmd.Parameters.Add(Trim("@fldPassword"), Trim(strPassword))
myCmd.Parameters.Add("@fldFirstName", strFirstName)
myCmd.Parameters.Add("@fldLastName", strLastName)
myCmd.Parameters.Add("@fldPassword", strPassword)
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
myConn.Close()
'If strPassword = 55555555 Then
' Session("intDefaultPass") = 1
'End If
Session("strFullName") = strFirstName & " " & strLastName
Session("strPassword") = strPassword
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
Return intResult
End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after?
Michael
View 4 Replies
View Related
Feb 26, 2006
I have an SQL query that can generate XML file. However, it does not seemed to work as a stored procedure. Basically, i want to be able to generate an XML file based on the data stored in a SQL table and be able to do this using script...Also, if there is a script (or stored procedure) that will allow me to generate the XML file with the specification of an XML schema would even be better...
e.g Sample XML file required...<Person><Name>Raymond</Name><NickName>The Legend</NickName></Person><Person><Name>Peter</Name><NickName>The King</NickName></Person>
sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Web Assistant Procedures', 1;GORECONFIGUREGOsp_makewebtask @outputfile = 'C:MyExportFile.xml', @query = 'SELECT * FROM MyTableName for XML AUTO, TYPE, ELEMENTS', @templatefile = 'C:Template.tpl'
View 1 Replies
View Related