Adding Field Or Stored Procedure To MSDE
Jan 7, 2004Is 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,
ADVERTISEMENT
Adding Field To Stored Procedure For Crystal Reports
Jul 20, 2005Hello:I have a stored procedure for generating our invoices in CrystalReports. I have added a new field to the SP, but when I try to add thefield to my Crystal Report invoice, the field isn't available in thelist. However, if I create a new, blank report using the same storedprocedure as the datasource, the field is available. I've seeminglytried every iteration of "Verify Database" to no avail.The obvious answer would be to simply drop the sp from my existingreport and then re-add it. However, if you do this, all your fields onthe report are dropped.Any ideas?Thanks,Scott
View 1 Replies View RelatedI Need This To Be Done Using Only Single Stored Procedure For Binding Field Value To DropDownBox And For Adding Income. Plz Tell Me How To Do This?
May 22, 2008
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below.
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
ASPX.CS file
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = null;
con = DataBaseConnection.GetConnection();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con);
da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
da.Fill(ds);
IFDdl.DataSource = ds;
IFDdl.DataTextField= ds.Tables[0].Columns[0].ToString();
IFDdl.DataValueField = ds.Tables[0].Columns[0].ToString();
IFDdl.DataBind();
}
protected void IncAddBtn_Click(object sender, EventArgs e)
{
SqlConnection con = null;
try
{
con = DataBaseConnection.GetConnection();
SqlCommand cmd = new SqlCommand("AddIncome", con);
cmd.CommandType = CommandType.StoredProcedure;
//SqlCommand cmd = new SqlCommand("",con);
//cmd.CommandText = "insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)";
cmd.Parameters.Add("PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"];
cmd.Parameters.Add("@PA_IFName", SqlDbType.VarChar, 10).Value = IFDdl.SelectedValue;
cmd.Parameters.Add("@PA_IDesc", SqlDbType.VarChar, 50).Value = IFDescTB.Text;
cmd.Parameters.Add("@PA_IDate", SqlDbType.DateTime).Value = Convert.ToDateTime(IFDateTB.Text);
cmd.Parameters.Add("@PA_IAmt", SqlDbType.Money).Value = Convert.ToDecimal(IFAmtTB.Text);
cmd.ExecuteNonQuery();
IFLabelMsg.Text = "Income Added Successfully!";
} // end of try
catch (Exception ex)
{
IFLabelMsg.Text = "Error : " + ex.Message;
}
finally
{
con.Close();
}
}
Stored Procedure
ALTER PROCEDURE dbo.AddIncome (@PA_UID int,@PA_IFName varchar(10),@PA_IDesc varchar(50),@PA_IDate datetime,@PA_IAmt money)
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/*SET NOCOUNT ON*/
insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)
ASPX File
<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="AddIncome.aspx.cs" Inherits="AddIncome" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<h2>
Add Income </h2>
<br />
<table>
<tr>
<td>
Select Income Field</td>
<td>
<asp:DropDownList ID="IFDdl" runat="server" Width="247px" >
</asp:DropDownList>
<a href="addincomefield.aspx">Add Income Field</a>
</td>
</tr>
<tr>
<td>
Enter Income Amount
</td>
<td>
<asp:TextBox ID="IFAmtTB" runat="server" Width="96px"></asp:TextBox>
Date <asp:TextBox ID="IFDateTB" runat="server" Width="93px"></asp:TextBox>(MM/DD/YY)</td>
</tr>
<tr>
<td>
Enter Income Description
</td>
<td>
<asp:TextBox ID="IFDescTB" runat="server" Width="239px"></asp:TextBox></td>
</tr>
</table>
<br />
<asp:Button ID="IncAddBtn" runat="server" Text="Add Income" OnClick="IncAddBtn_Click" /><br />
<br />
<asp:Label ID="IFLabelMsg" runat="server"></asp:Label>
</asp:Content>
I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
Need Help Adding Onto My Stored Procedure
Apr 6, 2005calculate age:CREATE PROCEDURE selectage ASSELECT Age = datediff(yy, birthday, getdate()) + case when datepart(dy, birthday) <= datepart(dy, getdate()) then 0 else -1endFROM nopaymy ? is how do fix this problem:I add inWHERE (((Age)>= @age1 And (Age)<= @age2)) and it will complain about Age is not a column, so how would i store the underlined and bold Age aboves value?
View 6 Replies View RelatedAdding New Stored Procedure
Mar 27, 2006Currently I add a new stored procedure in Enterprise Manager by right-clicking Stored Procedure and clicking New Stored Procedure then copy/paste text of procedure into window displayed. Whenever we set up new customer, this process is repeated several times to get all of our stored procedures loaded.
Is there a way to automate this process?
Thanks
Adding Stored Procedure
Jan 31, 2008
Hello,
What is the easiest way to add a strored prodedure in SQL- server? Sounds like silly question but it doesn´t seem that obvious to me.
BTW, I´m using SQL-server 2005.
Thanks in advance!
Adding CLR Stored Procedure In ASP.Net Website
Apr 12, 2007Hi all,I am creating a ASP.Net 2.0 website and in it I had to create a CLR stored procedure to do a complex sql procedure. Coming to the problem I created the CLR stored procedure as a different database project . I wanted to know whether its possible to add the CLR managed code into my exisiting ASP.Net project in which case I should get the dll for this stored procedure in order to deploy the stored proc in the SQL Server.Or is there some simplified approach to using clr stored procedures in an ASP.Net project.
View 2 Replies View RelatedAdding A Variable To A Stored Procedure
Jul 19, 2007Hi,I just try to add a variable (ORDER BY) to the end of a select statement and it doesn't work. I try so many things but im a newbie in SQL.Help!!!I want to add @OrderBy at the end of :-----------------SELECT *FROM AnnoncesWHERE CategoryID = @CategoryID AND DateOnCreate >= @DateFinale-----------------My declaration :Declare @OrderBy nvarchar(50)If @Sort = 'date_de' set @OrderBy = ' ORDER BY DateOnCreate DESC'Else set @OrderBy = ' ORDER BY DateOnCreate ASC'
View 8 Replies View RelatedInsert Into A MSDE Database Via A Stored Procedure..
Nov 2, 2003my scriptcode goes like:
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Function populera() As Integer
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='modell2'"
Dim SqlConnection As SqlConnection = New SqlConnection(connectionString)
Dim queryString As String = "procForetag"
Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection)
SqlConnection.Open()
sqlCommand.CommandType = CommandType.StoredProcedure
sqlCommand.Parameters.Add(New SqlParameter("@Foretagsnamn", SqlDbType.varchar, 70))
sqlCommand.Parameters.Add("@Besoksadress", SqlDbType.varchar, 30)
sqlCommand.Parameters.Add("@Boxadress", SqlDbType.varchar, 20)
sqlCommand.Parameters.Add("@Postnummer", SqlDbType.char, 5)
sqlCommand.Parameters.Add("@Postadress", SqlDbType.varchar, 30)
sqlCommand.Parameters.Add("@Telefon", SqlDbType.varchar, 15)
sqlCommand.Parameters.Add("@Kontaktperson", SqlDbType.int, 70)
sqlCommand.Parameters("@Foretagsnamn").Value = txtFnamn.Text
sqlCommand.Parameters("@Besoksadress").Value = txtBadress.Text
sqlCommand.Parameters("@Boxadress").Value = txtBoxadress.Text
sqlCommand.Parameters("@Postnummer").Value = txtPnr.Text
sqlCommand.Parameters("@Postadress").Value = txtPadress.Text
sqlCommand.Parameters("@Telefon").Value = txtTelefon.Text
sqlCommand.Parameters("@Kontaktperson").Value = txtKontakt.Text
return nothing
sqlConnection.Close
End Function
Sub btnOK_Click(sender As Object, e As EventArgs)
dim i As Integer
i = populera()
End sub
</script>
and the stored procedure like this:
create procedure procForetag @Foretagsnamn varchar(70), @Besoksadress varchar(30),@Boxadress varchar(20), @Postnummer char(5),
@Postadress varchar(30),@Telefon varchar(15), @Kontaktperson int
as insert into Foretag
values(@Foretagsnamn, @Besoksadress,@Boxadress, @Postnummer,
@Postadress,@Telefon, @Kontaktperson)
However, when I run the page it doesn't update the database with the values that I provide. So the connection to the database that doesn't seem to work..Does anybody see why?
/asp.net-wannabe
MSDE Update SQL Stored Procedure Syntax
May 10, 2004I have 2 tables, table A and B. Table A has the following fields; Phone (nvchar), Fname (nvchar), Lname (nvchar), DNC (bit). Table B has one field, PhoneNo (nvchar). I would like to update the field DNC in Table A to True(1) if the values Phone in Table A = PhoneNo in Table B.
I tried this syntax but it never updated:
UPDATE dbo.A
SET DNC = 1
WHERE (Phone = 'SELECT MAX dbo.B.PhoneNo FROM dbo.B')
These tables have over 100K records and I would like to make sure it runs through and checks every single record and not just the first 10000.
Search MSDE Database Using Stored Procedure
Apr 21, 2005Hi,
I have a MSDE datatable which containes Documents and a link to thet document.
Is there any way i can search the doument name colunm using a search box from my web site and display the results. I have seen all these "stored procedure" discussions and just wonder whether it would be easy to search using the stored procedure?
I haven't got much experience using MSDE and it seems really a big task for me.
Any help/suggestions would be highly appreciated.
Thanks, RAJESH
Question On Adding A Join To The Stored Procedure
Nov 15, 2007 The stored procedure below was working fine and I have added a inner join to it and it stopped working. I have highlighted the new code I have added to the stored procedure in red. Any suggestions on how to solve this issue?
I am getting the below error
Server: Msg 209, Level 16, State 1, Procedure AIG_GetRECON_TRANSACTION, Line 53
Ambiguous column name 'REINS_TYPE_CD'.
below is the query i changed.
ALTER PROCEDURE [dbo].[AIG_GetRECON_TRANSACTION]
@RECON_TRNSCTN_ID int=NULL,
@RECON_ITEM_ID int=NULL,
@DIVISION_ID int=NULL,
@REINS_TYPE_CD int=NULL,
@TRANSACTION_NO char(10)=NULL,
@TRANSACTION_NAME varchar(100)=NULL,
@REF_NUMBER varchar(20)=NULL,
@POLICY_CRS_REF_NO varchar(20)=NULL,
@GL_DTFrom datetime=NULL,
@GL_DTTo datetime=NULL,
@TRANSACTION_CD int=NULL,
@AMOUNT money=NULL,
@FILE_STATUS_CD int=NULL,
@TRNSCTN_OWR_CD int=NULL,
@ISSUE_CD int=NULL,
@SUPP_ISSUE_CD int=NULL,
@IRC_CLASSIFICATION_CD int=NULL,
@UNDER_90_DAYS money=NULL,
@OVER_90_DAYS money=NULL,
@AGING_DAYS_CNT int=NULL,
@VOCHER_NO varchar(50)=NULL,
@LOADED_DTFrom datetime=NULL,
@LOADED_DTTo datetime=NULL,
@SPUsageMode TINYINT = 0 -- This should be the last parameter
AS
-------------------------------------------------------------------------------
-- SP Usage Audit Info -- DO NOT REMOVE
-- All Stored Procedure code MUST be placed between Section 1 and 2 of
-- SP Usage Audit code
-------------------------------------------------------------------------------
-- SP Usaged Section 1 - Declare
-------------------------------------------------------------------------------
DECLARE @SPStartTime DATETIME
SELECT @SPStartTime = GETDATE()
DECLARE @SPEndTime DATETIME
DECLARE @AuditCount INT
-------------------------------------------------------------------------------
SELECT @GL_DTFROM = ISNULL(@GL_DTFROM, CONVERT(DATETIME,'1/1/1900'))
SELECT @GL_DTTO = ISNULL(@GL_DTTO, CONVERT(DATETIME,'12/31/9999'))
SELECT @LOADED_DTFROM = ISNULL(@LOADED_DTFROM, CONVERT(DATETIME,'1/1/1900'))
SELECT @LOADED_DTTO = ISNULL(@LOADED_DTTO, CONVERT(DATETIME,'12/31/9999'))
Begin
SELECT
RECON_TRNSCTN_ID,
RECON_ITEM_ID,
DIVISION_ID,
REINS_TYPE_CD,
TRANSACTION_NO,
TRANSACTION_NAME,
REF_NUMBER,
POLICY_CRS_REF_NO,
GL_DT,
TRANSACTION_CD,
AMOUNT,
FILE_STATUS_CD,
TRNSCTN_OWR_CD,
ISSUE_CD,
SUPP_ISSUE_CD,
IRC_CLASSIFICATION_CD,
UNDER_90_DAYS,
OVER_90_DAYS,
AGING_DAYS_CNT,
VOCHER_NO,
LOADED_DT,
REI.REINS_TYPE_DS
FROM AIGNET.dbo.RECON_TRANSACTION AS RE
INNER JOIN REINSURANCE_TYPE REI ON RE.REINS_TYPE_CD = REI.REINS_TYPE_CD
WHERE
(@RECON_TRNSCTN_ID IS NULL OR @RECON_TRNSCTN_ID=RE.RECON_TRNSCTN_ID)
AND
(@RECON_ITEM_ID IS NULL OR @RECON_ITEM_ID=RE.RECON_ITEM_ID)
AND
(@DIVISION_ID IS NULL OR @DIVISION_ID=RE.DIVISION_ID)
AND
(@REINS_TYPE_CD IS NULL OR @REINS_TYPE_CD=RE.REINS_TYPE_CD)
AND
(@TRANSACTION_NO IS NULL OR @TRANSACTION_NO=RE.TRANSACTION_NO)
AND
(@TRANSACTION_NAME IS NULL OR @TRANSACTION_NAME=RE.TRANSACTION_NAME)
AND
(@REF_NUMBER IS NULL OR @REF_NUMBER=RE.REF_NUMBER)
AND
(@POLICY_CRS_REF_NO IS NULL OR @POLICY_CRS_REF_NO=RE.POLICY_CRS_REF_NO)
AND
((RE.GL_DT IS NULL) OR (RE.GL_DT BETWEEN @GL_DTFrom AND @GL_DTTo))
AND
(@TRANSACTION_CD IS NULL OR @TRANSACTION_CD=RE.TRANSACTION_CD)
AND
(@AMOUNT IS NULL OR @AMOUNT=RE.AMOUNT)
AND
(@FILE_STATUS_CD IS NULL OR @FILE_STATUS_CD=RE.FILE_STATUS_CD)
AND
(@TRNSCTN_OWR_CD IS NULL OR @TRNSCTN_OWR_CD=RE.TRNSCTN_OWR_CD)
AND
(@ISSUE_CD IS NULL OR @ISSUE_CD=RE.ISSUE_CD)
AND
(@SUPP_ISSUE_CD IS NULL OR @SUPP_ISSUE_CD=RE.SUPP_ISSUE_CD)
AND
(@IRC_CLASSIFICATION_CD IS NULL OR @IRC_CLASSIFICATION_CD=RE.IRC_CLASSIFICATION_CD)
AND
(@UNDER_90_DAYS IS NULL OR @UNDER_90_DAYS=RE.UNDER_90_DAYS)
AND
(@OVER_90_DAYS IS NULL OR @OVER_90_DAYS=RE.OVER_90_DAYS)
AND
(@AGING_DAYS_CNT IS NULL OR @AGING_DAYS_CNT=RE.AGING_DAYS_CNT)
AND
(@VOCHER_NO IS NULL OR @VOCHER_NO=RE.VOCHER_NO)
AND
((RE.LOADED_DT IS NULL) OR (RE.LOADED_DT BETWEEN @LOADED_DTFrom AND @LOADED_DTTo))
End
-------------------------------------------------------------------------------
-- SP Usage Audit Info -- DO NOT REMOVE
-- SP Usage Section 2 - INSERT Audit Info
-------------------------------------------------------------------------------
SELECT @AuditCount = @SPUsageMode +
(SELECT ParameterFlag FROM DBPerfMon.dbo.SPUsageParameters WITH (NOLOCK)
WHERE Parameter = 'SPUsageByPass')
IF @AuditCount < 1
Begin
SELECT @SPEndTime = GETDATE()
INSERT DBPerfMon.dbo.SPUsage (
DatabaseName
, Duration
, ObjectID
, ObjectName
, UserName
)
SELECT
DB_NAME()
, DATEDIFF(ms, @SPStartTime, @SPEndTime)
, OBJECT_ID(OBJECT_NAME(@@PROCID))
, OBJECT_NAME(@@PROCID)
, dbo.fncGetLastUpdatedBy ()
End
-------------------------------------------------------------------------------
-- Absolutely NO Stored Procedure code written beyond this point
-------------------------------------------------------------------------------
Transact SQL :: Adding Values In Row Using Stored Procedure
Aug 18, 2015I'm trying to write a stored procedure that performs a select statement of the RequestID column and the total of the disk size for that row. ie the values on RequestAdditionalDisk1Size + RequestAdditionalDisk2Size + RequestAdditionalDisk3Size where the Requester equals a certain value. I can perform the select statement fine on the individual values, how to add the values of the Disk sizes together and present that back in the select statement.So far the code looks like but is giving me an error around the line performing a SUM.
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spMyRequests]
-- Add the parameters for the stored procedure here
[code]....
SQL Server 2012 :: Adding Some Text To A Stored Procedure
Jul 21, 2015I have made this defination for a stored procedure:
PROCEDURE EP_Conterbalances
@Start_Date_For_Totals_Date DATETIME,
@EmpFilterAddDuty VARCHAR(500),
@CounterBalanceType_id INT,
@dateFrom DATETIME,
[Code] .....
The value of @EmpFilterAddDuty could be:
'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '
If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:
DECLARE@return_value int
EXEC@return_value = [dbo].[EP_Conterbalances]
@Start_Date_For_Totals_Date = N'20120831',
@EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B
[Code] .....
I get this error code:
Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.
I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.
Transact SQL :: Adding Stored Procedure To Master Database?
Apr 24, 2015I have a SQL server instance being used as our data warehousing environment. The instance consists of several databases that I am snapshotting as part of our high availability strategy for data. I've created a stored procedure that takes the source database as an argument and that will create a new snapshot when a new one needs to be created and will also automatically remove the old snapshot. It also updates some synonym tables that point to the new snapshot but that might not be an important detail.
I would like to have the stored procedure stored some place global to all of the databases that I am routinely snapshotting, but that would mean putting it in the master database. Although having it there makes things significantly better in terms of usability, it seems like there's something wrong with putting any stored procedures in the master database. Am I wrong? Is it OK to put stored procedures there in situations like this?
Newbie Question: Adding A Single Value To A List In A Stored Procedure
Apr 20, 2006I have two tables. UserIds is a collection of users, with UserId as the primary key. Contacts simply maps pairs of users. Think of it like IM, where one user can have multiple contacts.
UserIds
----------
UserId - int, primary key
Username etc
Contacts
-------------
UserId - int, the UserId of the user who has the contact
ContactUserId - int, the UserId of the contact
I also have a stored procedure named GetConnectedUserIds that returns all the contacts of a given user. It takes a single parameter, @UserId, the UserId of the user whose contacts I want to get. That's pretty simple:
SELECT ContactUserId FROM Contacts WHERE UserId=@UserId.
Now here's where I get over my head. I actually want that stored procedure to return the user's contacts AND the user's own ID. I want the SELECT statement above, but tack on @UserId to the end of it.
How do I do that?
Thanks in advance for any help. Feel free to answer here or to point me to a useful resource.
Nate Hekman
Need To Add Field In This Stored Procedure
Oct 27, 2006Hello 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
Stored Procedure Returns -1 For A Bit Field
Apr 15, 2008Hello,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 RelatedSum Or Count Value Of Field In Stored Procedure
Apr 19, 2005How 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
Update A Field With A Stored Procedure
Dec 13, 2004I 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
Field List From A Stored Procedure
Apr 16, 2004I 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
Stored Procedure And A New Database Field
Dec 28, 2007I 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.
Field Not Being Updated Within Stored Procedure
Jul 23, 2005I 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 RelatedHow Can I Format A Datetime Field In A Stored Procedure
Dec 14, 2006How can I format a datetime field in a stored procedure to return just the date in MM/DD/YYYY format?
View 13 Replies View RelatedMultiple Filtering On The Same Field Using A Stored Procedure
Apr 24, 2007Hello,
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
Changing Field Values In A Stored Procedure
May 4, 2003Here'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.
Sorting Stored Procedure By Text Field
Mar 19, 2001I 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!
Stored Procedure To Fill A Date Field
Oct 27, 1998I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.
View 2 Replies View RelatedCopy Ntext Field In Stored Procedure
Oct 24, 2007Hello everybody,
i have a problem with copying (read it from one row and set it to another) a ntext field in a stored procedure. I know how
to get a pointer to the data and how to read from it.
But i want to copy the whole data.
Does anybody know how to do that?
Thanks in advance
A.Gempp
-------------------------------
I'm using SQL Server 2000
Stored Procedure To Update A Display Order Field.
Dec 20, 2006I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark
Changing Field In A Stored Procedure To Match Name In A Form
May 15, 2008If 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.
How Can I Insert Id Field Automatic In Table By Stored Procedure
Sep 25, 2005i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key got error because allready record has same value .how i can know the last row's student_id value and input a new valid value in one stored procedure thanks
View 1 Replies View RelatedStored Procedure - Field Not In Aggregate Function Or Group By
Nov 29, 2011best solution for this stored procedure query.I'm getting the following error:
Column 'dbo.Applicants.submitted' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Here is my select query:
Code:
SELECT DISTINCT DATENAME(MONTH, submitted) AS mon, MAX(submitted) AS SubmitDate
FROM dbo.Applicants
WHERE ((CASE WHEN MONTH(submitted) > 8 THEN YEAR(submitted) + 1 ELSE YEAR(submitted) END) = @AcYr)
ORDER BY SubmitDate
The submitted field is a date field.I don't want to add the submitted field to Group By as I want to group by month not date.Is there any solution to avoid grouping by date?