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

Changing Field Values In A Stored Procedure

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

Store Procedure Match Field

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

How To Display Data Using Stored Procedure In My Asp.net Form

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

Problem Useing Stored Procedure Form Vb.net

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

Depending On The Selection Of The User Of The Asp.net Web-form How Can I Do Changes In The Stored Procedure

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

How To Change View Code Or Table Form Stored Procedure + -

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

How To Get The Null Values In My Stored Procedure I Am Getting Error Dbnullvalue On Front End Aspx Form

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

Changing A Stored Procedure To Inline Sql

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

Changing Stored Procedure &#39;type&#39;

May 25, 2000

Is there any way to change the 'type' of a stored procedure from 'user' to 'system'?

View 1 Replies View Related

Changing Databases In An Stored Procedure

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

Changing Database Within A Stored Procedure

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

Changing An Activatin Stored Procedure

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

Changing Parameters To Wildcard In Stored Procedure

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

Stored Procedure For Changing Recovery Model ?

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

Changing Stored Procedure Freezes Crystal

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

Best Way To Send Email From A Stored Procedure (dynamically Changing Paramenters And Attachment)

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

Analysis :: Changing Ssas Cube Form-view Background Color?

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

Any Part Of Field Match

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

Match Field And Return To 1

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

Need To Add Field In This Stored Procedure

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

MAX_LENGTH Field Does Not Match The Value Displayed?

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

Stored Procedure Returns -1 For A Bit Field

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

Sum Or Count Value Of Field In Stored Procedure

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

Update A Field With A Stored Procedure

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

Field List From A Stored Procedure

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

Stored Procedure And A New Database Field

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

Field Not Being Updated Within Stored Procedure

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

Searching For A Partial Match In A Varchar Field

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

How Can I Format A Datetime Field In A Stored Procedure

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

Multiple Filtering On The Same Field Using A Stored Procedure

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

Adding Field Or Stored Procedure To MSDE

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

Sorting Stored Procedure By Text Field

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







Copyrights 2005-15 www.BigResource.com, All rights reserved