Changing Field In A Stored Procedure To Match Name In A Form
May 15, 2008
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data.
There is a web app already created that has the followig code to get the data from the database
Dim strSQL ast string = "UsersSelectCommand"
intLoginID = objDataReader("LoginID")
My stored procedure is the following:
CREATE PROCEDURE UsersSelectCommand
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
Select Lastname, FirstName, Login from Users
Order by LastName
GO
The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
View 2 Replies
ADVERTISEMENT
May 4, 2003
Here's the deal:
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A
[ = Plan B
+ = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
Thanks in advance.
View 3 Replies
View Related
Oct 31, 2013
I need some store procedure script , from two table i want to pickup matching field and insert into main table ,give some sample store procedure coding...
If 1st table as cheque no and 2nd table as cheque no - matching then data would be inserted in to main table using two table...
View 2 Replies
View Related
Jul 29, 2004
I want to display the data in datagrid using the stored procedure,
Can you please tell me, how i can create the stored procedure for the following:
using select query(SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders)
I want to display the stored procedure data in my Datagrid.
Thank you very much for the help.
the following is complete inline code on my webform.
Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("NorthwindConnection"))
Dim objCmd As New SqlCommand
Dim dataAdapter As SqlDataAdapter
objCmd.Connection = objConn
objCmd.CommandType = CommandType.Text
objCmd.CommandText = "SELECT Top 10 OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"
objConn.Open()
dataAdapter = New SqlDataAdapter
dataAdapter.TableMappings.Add("Table", "Orders")
dataAdapter.SelectCommand = objCmd
dataSet = New DataSet("Orders")
dataAdapter.Fill(dataSet)
dtgOrders.DataSource = dataSet
dtgOrders.DataBind()
objConn.Dispose()
View 1 Replies
View Related
Nov 13, 2006
i have created a stored prcedure but is always give error : and please check the ways is correct using stored procedure
"Procedure or Function 'Add_Cb_Entry' expects parameter '@Date', which was not supplied."
Dim SqlPrm As SqlParameter
Dim SqlCmd As New SqlCommand
With SqlCmd
.Connection = SqlConnection 'this is my connection setting
.CommandText = "Dbo.Add_Cb_Entry"
.CommandType = CommandType.StoredProcedure
End With
SqlPrm = SqlCmd.Parameters.Add("@Cashbook_Id", Nothing)
SqlPrm.Direction = ParameterDirection.Output
SqlPrm = SqlCmd.Parameters.Add("@Date", SqlDbType.DateTime, 8, TxtDate.Text)
SqlPrm.Direction = ParameterDirection.Input
SqlPrm = SqlCmd.Parameters.Add("@Entry_ID", SqlDbType.Int, 4, "4")
SqlPrm = SqlCmd.Parameters.Add("@Entry_Name", SqlDbType.VarChar, 75, "Irfan Imdad Memon")
SqlPrm = SqlCmd.Parameters.Add("@Description", SqlDbType.VarChar, 100, "Chk")
SqlPrm = SqlCmd.Parameters.Add("@Amount", SqlDbType.Money, 13, "1000.20")
SqlPrm = SqlCmd.Parameters.Add("@Type", SqlDbType.NChar, 2, "DB")
SqlPrm = SqlCmd.Parameters.Add("@Entry_Status", SqlDbType.VarChar, 20, "Account")
SqlPrm = SqlCmd.Parameters.Add("@Ref_No", SqlDbType.Int, 4, "1")
SqlPrm = SqlCmd.Parameters.Add("@Ref_Status", SqlDbType.VarChar, 20, "MeterialPurchase")
OpenConnection() 'this is my connection setting
SqlCmd.ExecuteNonQuery()
CloseConnection() 'this is my connection setting
TxtInvoiceNo.Text = SqlCmd.Parameters("@Cashbook_Id").Value
View 1 Replies
View Related
May 11, 2007
Hi frdz, I have created the following stored procedure in sql server 2005. In my database i have one option for the payment mode which can be done thru cash or credit(cheque). I have created my web-application in asp.net with C# 2005. There i have a dropdownlist box for the user to select the option whether wants to do the payment thru cash or cheque.Depending on that selection if user selects cheque then all the reqt for cheque like it's no,dt,bankname etc...are visible.but if user selects the option as cash then the cheque details become invisible.Depending on the selection of the user of the asp.net web-form how can i do changes in the stored procedure... i can write the condition likeif paymentmode=cash then ..........else.............but where and how can it be written ...pls tell methanxs in adv...u can go thru my below SP ALTER PROCEDURE MiscellaneousStoredProcedure
@miscid int output,
@storename varchar(20),--store name to storeid
@accountname varchar(20),
@groupname varchar(20),
@paymentdt datetime,
@payeename varchar(30),
@paymode varchar(20),
@bankname varchar(50),
@chqdt datetime,
@chqno varchar(20),
@amt numeric(10, 2),
@bal numeric(10, 2),
@remarks varchar(50)
as
declare
@storeid int,
@accountid int,
@groupid int
begin
set nocount on
select @miscid = isnull(max(@miscid),0) + 1 from miscellaneourpay
if exists (select * from storemaster where storename = @storename)
select @storeid = storeid from storemaster where storename = @storename
if exists (select * from accountmaster where accountname =@accountname)
select @accountid = accountid from accountmaster where accountname =@accountname
if exists (select * from accountgroupmaster where groupname=@groupname)
select @groupid=groupid from accountgroupmaster where groupname=@groupname
begin transaction
insert into miscellaneourpay
(
miscid,
storeid,
accountid,
groupid,
paymentdt,
paymode,
payeename,
bankname,
chqdt,
chqno,
amt,
bal,
remarks
)
values
(
@miscid,
@storeid,
@accountid,
@groupid,
@paymentdt,
@paymode,
@payeename,
@bankname,
@chqdt,
@chqno,
@amt,
@bal,
@remarks
)
commit transaction
end
View 2 Replies
View Related
Jun 1, 2008
need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example
if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1
Code Snippet
SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)
AS new_unit
FROM dbo.empList
my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1
like create stored procedure name "plus" + so if i run this name stored procedure name "plus"
the stored procedure go to the viewor table and change the code view or table value
so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1
it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value
the important thing is that i can forward +1 or backward -1
evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1
TABLE dbo.empList
empid ShiftType unit_date unit
----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3
TNX for the help
View 7 Replies
View Related
Nov 2, 2004
I have the following stored proc. which i am using on the front end to get all the record from table:
if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.
CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO
Thank you very much.
View 2 Replies
View Related
Jan 7, 2008
Hi i have the following stored procedure, i am trying to convert it to inline sql, however i got stuck, because how would i output a value e.g "@Access_Right_ID", this is the stored procedure below
CREATE PROCEDURE dhoc_AccessRight_Insert @AccessLevel char(50), @Access_Right_ID int OUT,
@Tstamp timestamp out
ASSELECT * FROM tblAccess_Right
WHERE AccessLevel = @AccessLevel IF @@ROWCOUNT <>0 BEGIN RAISERROR('This record is already in the database',16,1) RETURN 14 END
ELSE BEGIN SET NOCOUNT OFF; INSERT INTO tblAccess_Right ( AccessLevel ) VALUES (@AccessLevel); SET @Access_Right_ID = @@Identity SET @Tstamp = (SELECT Tstamp FROM tblAccess_Right WHERE Access_Right_ID=@Access_Right_ID)
--Make sure this has saved, if not return 10 as this is unexpected error IF @@rowcount = 0 BEGIN RAISERROR('This record has not been inserted at this time, it might have been inserted by another user, please try again',16,1) RETURN 10 END ELSE BEGIN IF @@error <>0 BEGIN RETURN @@error END ENDEND
GO
View 8 Replies
View Related
May 25, 2000
Is there any way to change the 'type' of a stored procedure from 'user' to 'system'?
View 1 Replies
View Related
Aug 24, 1999
Hello all,
I have discovered that you cannot use the "USE" command in a stored procedure to change to another database.
Is there any way I can change to another database within a stored procedure besides calling another stored procedure on the database I want?
Thanks,
Nev.
View 1 Replies
View Related
Jul 20, 2005
I need to create a stored procedure in the master database that canaccess info to dynamically create a view in another database. Itdoesn't seem like it should be very hard, but I can't get it to work.Here's an example of what I want to do.CREATE PROCEDURE create_view @dbname sysnameASBEGINDECLARE @query varchar(1000)SELECT @query = 'use ' + @dbname + ' go CREATE VIEW ........'EXEC(@query)ENDIn this case, I get an error with the word "go". Without it, I get a"CREATE VIEW must be the first statement in a batch" error. I tried asemicolon in place of "GO" but that didn't help either.Thanks
View 4 Replies
View Related
Mar 5, 2007
Hello,
After I changed the activation stored procedure of a queue, the old activation stored procedure keeps being launched by the service associated with the queue. I turned the queue off and on, but to no avail. I also checked the dynamic view sys.service_queues to make sure the proper stored procedure exists in the field activation_procedure. Do you have any idea about what might have gone wrong?
Thanks
View 3 Replies
View Related
Mar 7, 2001
I have a stored proc which will be fed a value that is compared to a char value for a selection. One of the choices might be 'all' for which I want to return all records. Something like this
create procedure name @location char(40)
as select ----------------
where locname like @location
I tried using LIKE COALESCE but no luck. Any ideas
Thanks
View 1 Replies
View Related
Mar 29, 2004
The SQL Server is running full recovery model but they would like it to be changed to simple while the data backup occurs then set back to full. Is there a way to do this in a stored procedure or is it all done via options ? Thanks
View 8 Replies
View Related
Aug 18, 2006
After I change a fairly complex stored procedure and I run a reportagainst it, crystal hangs at "assesing database". I have verified thedatabase. When I run a trace on SQL is shows repeated cachemiss overand over. I let it run for 30 minutes and nothing.Anyone?
View 5 Replies
View Related
Jul 23, 2005
Hello everyone,I need advice of how to accomplish the following:Loop though records in a table and send an email per record. Emailrecipient, message text and attachment file name - that's all changesrecord by record.Is it doable from a stored procedure (easily I mean, or am I better offwriting a VB app)? There are so many options of sending mail from SQLserver - CDONTS, SQL MAIL TASK, xp_sendmail. What's easier to implementand set up?Thanks a lot!!!(links and fragments of sample code would be greatlyappreciated)Larisa
View 2 Replies
View Related
May 13, 2015
I want to change the color of the ssas cube calculated member in formview from red background to white or back to default color settings. This also includes changing the text color back to default. What properties do I have to change?
View 4 Replies
View Related
Jun 10, 2004
How can I make my search button have the "Any part of field" match as a default? with a simple query...
I have a field in MS access with hundreds of words (cv)... I want to be able to find a word in "Any part of field"
my try:
WHERE ((([cv].[detail cv])=[detail]));
detail is nowhere to find... i am prompt to give a value. fine.but it
equals whole field; detail must be the sole value of the field detail cv...
help!
mchel
View 7 Replies
View Related
May 14, 2007
hi..i'm new in sql progaming,i try to make make a query that in table field "match" return to "1"if no member record in another table and return to "0" if there is anyrecord member :extable member:member idA 12B 14Table Incoming.member note matchC bla..bla 1A bla..bla 0D bla..bla 1...... ....... .....can anyone help me please?D
View 3 Replies
View Related
Oct 27, 2006
Hello Everyone,
I have the following stored procedure:
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[usp_MLSReport_LRR]
@UserId int,
@ReportGroupId int = null,
@BranchTranType varchar(50) = null,
@BranchDivision varchar(50) = null,
@BranchRegion varchar(50) = null,
@BranchNbr int = null,
@BranchSatId varchar(1) = null,
@BeginDate datetime,
@EndDate datetime
AS
-- DECLARE @UserId int,
-- @ReportGroupId int,
-- @BranchTranType varchar(50),
-- @BranchDivision varchar(50),
-- @BranchRegion varchar(50),
-- @BranchNbr int,
-- @BranchSatId varchar(1),
-- @BeginDate datetime,
-- @EndDate datetime
--
-- SET @UserId = 4602
-- --SET @ReportGroupId = 46
-- SET @BranchDivision = 'DENVER DIRECT'
-- SET @BeginDate = '4/27/05'
-- SET @EndDate = '9/30/05'
SET NOCOUNT ON
DECLARE @UseCTD int
SET @UseCTD = CASE WHEN @BranchTranType IS NULL
AND @BranchDivision IS NULL
AND @BranchRegion IS NULL
AND @BranchNbr IS NULL
AND @BranchSatId IS NULL THEN 1 ELSE 0 END
SELECT DISTINCT
L.ProviderId,
L.OriginalProviderId,
S.SourceCode,
SG.SourceGroupCode,
SGDescription=SG.Description,
RG.ReportGroupId ,
ReportGroupDesc=RG.Description ,
L.VendorId,
VEN.VendorName,
B2.BranchTranType,
B2.BranchDivision,
B2.BranchRegion,
B2.BranchName,
L.BranchNbr,
L.BranchSatId ,
Branch=CONVERT(varchar, L.BranchNbr) + L.BranchSatId,
LoNbr = ISNULL(L.LONbr, 9999),
LoName = ISNULL(LO.LOName, ' - NOT ASSIGNED -') ,
LoEmploymentStatus = LO.EmploymentStatus,
LeadCount = 1,
Completed = CASE WHEN L.RecCompleted = 'C' THEN 1 ELSE 0 END,
InCompleted = CASE WHEN L.RecCompleted = 'I' THEN 1 ELSE 0 END ,
NoContact = CASE WHEN LD.Disposition = 16 THEN 1 ELSE 0 END,
Contacted = CASE WHEN LD.Disposition <> 16 THEN 1 ELSE 0 END,
Pending = CASE WHEN LD.Disposition IS NULL THEN 1 ELSE 0 END,
NoSale = CASE WHEN LD.Disposition <> 1 THEN 1 ELSE 0 END,
Apps1003 = CASE WHEN LD.Disposition = 1 THEN 1 ELSE 0 END ,
AppraisalsOrdered = CASE WHEN ISNULL(T3._@8034, 0) = 0 THEN 0 ELSE 1 END,
ClosedApps= CASE WHEN (@UseCTD = 1 AND SMT2._H0770 IS NOT NULL) OR SMT._H0770 IS NOT NULL THEN 1 ELSE 0 END,
LoanVolume = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(T1._@2026, 0) ELSE ISNULL(T1._@2026, 0) END,
OrigLoanBalance = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2._H0360, 0) ELSE ISNULL(SMT._H0360, 0) END,
Revenue = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2.calc_TotalIncomeDollars, 0) ELSE ISNULL(SMT.calc_TotalIncomeDollars, 0) END,
MarketingCost = CASE WHEN L.VendorId IN (59, 60) THEN 0 ELSE ISNULL(S.Cost, 0) END,
CAM.LeadTypeId,
SMT.calc_TotalIncomeDollars,
SP.SubjectAddrState,
SP.SubjectSCF,
S.LeadFormLabel
FROM MLS..tbl_MLS_Leads L (NOLOCK)
INNER JOIN MLS..tbl_MLS_LeadDispositions LD (NOLOCK) ON (LD.ProviderId = L.ProviderId)
INNER JOIN MLS..tbl_MLS_Branches B1 (NOLOCK) ON (B1.BranchNbr = L.BranchNbr AND B1.BranchSatId = L.BranchSatId)
INNER JOIN MLS..vw_MLS_AUCodeSecurity AU (NOLOCK) ON (B1.AUCode = AU.AUCode AND AU.UserId = @UserId)
INNER JOIN CHEC..SMT_Branches B2 (NOLOCK) ON (B1.BranchNbr = B2.BranchNbr)
INNER JOIN MLS..tbl_MLS_SubjectProperties SP (NOLOCK) ON (SP.ProviderId = L.ProviderId)
LEFT JOIN CHEC..TMRPT100 T1 (NOLOCK) ON (T1._@LOAN# = LD.TMOLoanNbr)
LEFT JOIN CHEC..TMRPT300 T3 ON (T1._@LOAN# = T3.R3LOAN)
LEFT JOIN CHEC..SMT3 SMT (NOLOCK) ON (SMT._H0010 = LD.TMOLoanNbr)
LEFT JOIN CHEC..SMT3 SMT2 (NOLOCK) ON (SMT2._H0010 = LD.CTDLoanNbr)
LEFT JOIN CHEC..SRVDSR SRV (NOLOCK) ON (SMT._H0010 = SRV._LOAN_NUM)
LEFT JOIN CHEC..SRVDSR SRV2 (NOLOCK) ON (SMT._H0010 = SRV2._LOAN_NUM)
LEFT JOIN MLS..tbl_MLS_MarketingSources S (NOLOCK) ON (S.SourceId = L.SourceId)
LEFT JOIN tbl_MLS_MarketingSourceGroups SG (NOLOCK) ON (S.SourceGroupId = SG.SourceGroupId)
LEFT JOIN tbl_MLS_ReportGroups RG (NOLOCK) ON (RG.ReportGroupID = SG.ReportGroupID)
LEFT JOIN (
SELECT
LONbr,
LOName = FullName,
EmploymentStatus,
ReHireDate,
HireDate,
TermDate
FROM MLS..vw_MLS_UsersWithLONbrs (NOLOCK)
) LO ON (LO.LONbr = L.LONbr)
LEFT JOIN MLS..tbl_MLS_Campaigns CAM (NOLOCK) ON (CAM.CampaignId = SG.CampaignId)
LEFT JOIN MLS..tbl_MLS_Vendors VEN (NOLOCK) ON (VEN.VendorId = L.VendorId)
WHERE L.DateReceived BETWEEN @BeginDate AND @EndDate + ' 23:59:59'
AND (CASE WHEN @ReportGroupId IS NULL THEN 1 WHEN @ReportGroupId = RG.ReportGroupId THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchTranType IS NULL THEN 1 WHEN @BranchTranType = B2.BranchTranType THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchDivision IS NULL THEN 1 WHEN @BranchDivision = B2.BranchDivision THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchRegion IS NULL THEN 1 WHEN @BranchRegion = B2.BranchRegion THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchNbr IS NULL THEN 1 WHEN @BranchNbr = L.BranchNbr THEN 1 ELSE 0 END) = 1
AND (CASE WHEN @BranchSatId IS NULL THEN 1 WHEN @BranchSatId = L.BranchSatId THEN 1 ELSE 0 END) = 1
AND L.ProviderId = L.OriginalProviderId
AND L.VendorId NOT IN (59, 60, 131) -- Exclude Turndown or Ghost leads
AND L.Deleted = 0
I need to add the field DateReceived from the following view:
dbo.vw_MLS_Leads
How should I code this?
As always thanks for the great answers and suggestions in advance.
Have a great day!
Kurt
View 8 Replies
View Related
Mar 5, 2007
Hi,
I wonder if anyone can help. I have installed the adventureWorksDW and when I run this query
select * from sys.all_columns where object_id = 85575343 and column_id = 2
The max_length filed value is 50 but if I view the column in explorer view the max_length is 25 I.e. ProductAlternateKey(nvarchar(25) ,null)
I don;'t know if I am looking at the correct table value?
View 3 Replies
View Related
Apr 15, 2008
Hello,I have a stored procedure: -- Get an individual league match by IDALTER PROCEDURE [dbo].[mb_League_GetLeagueMatchByID]( @LeagueMatchID int)ASSET NOCOUNT ONSELECT * FROM mb_LeagueMatch WHERE mb_LeagueMatch.LeagueMatchID = @LeagueMatchIDThe mb_LeagueMatch table has a column named IsActive that is a bit datatype.The value for all rows is set to true (in database explorer in visual studio 2005).When I execute the above stored procedure I always get -1 (I'm guessing that means null) as a result for IsActive if it was true and 0 when false (as expected).However, when I run a query on the database for the same parameter, I get the expected 1 as the value for IsActive.Has anyone seen this before?Thanks,Howard
View 4 Replies
View Related
Apr 19, 2005
How can i create a stored procedure that count or sum value of field
e.g.
f1 f2
f3 f4 f5
record 1
1 2
3 1
and get answer like this 1=4 - 2=1 - 3=1
View 1 Replies
View Related
Dec 13, 2004
I need to update a field with the code below, is it possible to do this with a table join? The Query Analyzer is giving me an error at "Join".
An example:
(ManufSerNo is a field in table ASSETS,
Status is a field in table HISTORY)
GO
CREATE PROCEDURE sp_ChngeAssetStatus
@ManufSerNochar(10),
@Statusvarchar(1)
AS
UPDATE HISTORY
SET Status = @Status
JOIN ASSETS
ON HISTORY.AssetID = ASSETS.AssetID
WHERE ManufSerNo = @ManufSerNo
--ELSE
--RAISERROR ('Sorry, but the Asset ID specified does not exist. Record not updated.', 10 , 1)
GO
View 2 Replies
View Related
Apr 16, 2004
I am writing a utility that creates Java code to access a database. I am looking for a way to get a list of fields and types that are returned by an sproc. Is there any easy way to get this from the master? Do you need to parse the SQL? This list would be like what Visual Studio.NET shows, or interdev if I remember correctly.
Thanks,
Larry
View 5 Replies
View Related
Dec 28, 2007
I am very new to stored procedures and fairly new to SQL in general. I have a stored procedure that looks like this:
CREATE PROCEDURE sp_UpdateProductsTable
@prodName varchar(50),
@prodManufacturer varchar(50),
@prodShortDescrip text,
@prodLongDescrip text,
@prodCatId int,
@prodPrice varchar(6),
@prodSortOrder int,
@prodActive int,
@prodId int
AS
if( @prodId <> 0 )
begin
update Products set
Name = @prodName,
Manufacturer = @prodManufacturer,
ShortDescription = @prodShortDescrip,
LongDescription = @prodLongDescrip,
CategoryID = @prodCatId,
Price = @prodPrice,
SortOrder = @prodSortOrder,
Active = @prodActive
where ID = @prodId;
select ID as prodId from Products where ID = @prodId
end
if( @prodId = 0 )
begin
insert into Products ( Name, Manufacturer, ShortDescription, LongDescription, Price, CategoryID, SortOrder, Active )
values( @prodName, @prodManufacturer, @prodShortDescrip, @prodLongDescrip, @prodPrice, @prodCatId, @prodSortOrder, @prodActive );
SELECT SCOPE_IDENTITY() AS prodId
end
GO
I have recently added two new fields to the database that I need the stored procedure to act upon, but adding them into the above code does not work. I read somewhere that this would happen with new fields, but I do not know how to fix this issue. Could someone point me in the right direction and help me out? I also have some other tables and SP's that I need to update, but its the same issue, I think so fixing this one should help me out across the board.
Thanks in advance for your help.
View 8 Replies
View Related
Jul 23, 2005
I have this stored procedure that loops through a table and updates acouple of fields. For some reason one of the fields is not beingupdated. If I run the same code from query analyzer, it works fine.Let me know if anyone can figure out why @lastscandate would ever beNULL. If it is null it should be equal to @maildate. The senerio thatseems to fail is when no records are returned from the select statementto fill in @lastscandate. This should then active the next ifstatement and set the @lastscandate equal to the @maildate. MailDateis always filled in in the database and LastScanDate will be NULL.Thanks for your help.DECLARE c1 CURSOR LOCAL FORSELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid= 0) AND (m.maildate ='' OR m.maildate IS NULL)AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycleAND p.status NOT IN ('BM', 'PM'))OPEN c1FETCH FROM c1 INTO @mid, @acctno, @ordid, @cycleWHILE @@fetch_status = 0BEGIN--Get MailDate from Manifest - if NULL then use GetDateset @maildate = NULLSELECT @maildate = MAX(whenmailed) FROM manifest WITH (nolock)WHERE acctno = @acctno AND ordid = @ordid AND cycle = @cycleif @maildate is NULLset @maildate = getdate()--Get Last Scan Date from Transactions - if NULL then use MailDateset @lastscandate = NULLselect @lastscandate=max(actiondate) from transactions whereacctno=@acctno and ordid=@ordid and cycle=@cycle and actionid=303if @lastscandate is NULLset @lastscandate = @maildateBEGIN TRANSACTIONUPDATE master SET printstatus = 'ML', maildate = @maildate,lastscandate=@lastscandateWHERE id = @midINSERT INTO transactions (initials, actionid, machinelogin, acctno,ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @acctno, @ordid,@cycle, 'Update Mail Dates')COMMIT TRANSACTIONFETCH NEXT FROM c1 INTO @mid, @acctno, @ordid, @cycleENDCLOSE c1
View 1 Replies
View Related
Jan 12, 2006
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
View 2 Replies
View Related
Dec 14, 2006
How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?
View 13 Replies
View Related
Apr 24, 2007
Hello,
I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below:
Input field: Product Description
So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned.
SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill"
I guess there'll need to be a nested Select and loop to parse the space separated input field.
Any pointers would be appreciated.
Thank you
Lee
View 2 Replies
View Related
Jan 7, 2004
Is there a way to add a field or a stored procedure to a server running MSDE? Like a script on the command line or?? how can this be done.
Thank you,
View 5 Replies
View Related
Mar 19, 2001
I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:
sp_select_thing 10, 'thing_name', 'asc'
It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible
Thanks!
View 1 Replies
View Related