i 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 Repliestable2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
View 3 Replies View RelatedI am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?
I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events)
I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
which one is better?
I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible?
My logical approach to this is:
1. Build a handler for the OnInsert event of the first table
2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource.
What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?
How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.
Hi,
I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
i explain
what i mean is if i have stored procedure with 7 conditions
for evry day in the week one condition
like this i have 7 code blocks for Sunday,Monday , .....Saturday
Code Block
CREATE PROC YourProc
@StartDate datetime = NULL,
@EndDate datetime = NULL
AS
SET @StartDate = COALESCE(@StartDate,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))--defaulting to todays date if not supplied
SET @EndDate=COALESCE(@EndDate,DATEADD(m,1,@StartDate))--defaults to 1 month from today
WHILE @StartDate <= @EndDate
BEGIN
SELECT CASE
WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =51 )
THEN 1
WHEN DATENAME( dw,@StartDate)='Monday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =11 )
THEN 2
............
WHEN DATENAME( dw,@StartDate)='Saturday' AND
...
THEN ..
END
SET @StartDate=DATEADD(d,1,@StartDate)
END
GO
like this until the last day of the month
TNX
Searched around for a sample, maybe I missed one?I have an insert statement....INSERT INTO objScores (objID, studentId, courseId) VALUES ( @objID, @userId, @course) This works fine, but problem is it reads only the first value from the tableDECLARE c_oId CURSOR FOR SELECT objID FROM objStructure FOR READ ONLYThe userID and course will always be the same.But, the objID, there will be many in the table objStructure such as Lesson1, Lesson2..... Lesson19I need it to read all of them, 1 - 19 or whatever the count, could be 3 and insert all of them into the table.So, the insert needs to input Lesson1, userID, course ----- Lesson2, userId, course ----- Lesson3, userID, course ---- and so on.It must read the objID from the objStructure table and just tranfer all that is there that = the course.Links? Suggestions?Thanks all,Zath
View 4 Replies View RelatedFirst of all hi for all,
I m trying to make insert stored procedure with parameters, i want to send table name and insert values with parameters, i m trying that below stored procedure but it gives syntax eror to me what shoul i do to correct it ?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertalepform]
-- Add the parameters for the stored procedure here
@type nvarchar(15), @talepbrmno int, @birimisteksayi bigint, @birimistektarih datetime,
@aciklama nvarchar(50), @onay int, @seviye int, @talepformno bigint, @taleptarih datetime
AS
BEGIN
SET NOCOUNT ON;
exec ('INSERT INTO [BelediyeWork].[dbo].['+@type+'TalepFormu]
([TalepBirimNo], [BirimistekSayısı], [BirimistekTarihi], [Acıklama], [Onay]
,[Seviye], [TalepFormNo], [TalepTarih])
VALUES ('+@talepbrmno+','+@birimisteksayi+','+@birimistektarih+','+@aciklama+'
,'+@onay+','+@seviye+','+@talepformno+','+@taleptarih+') ')
END
I have a long complicated storeed procedure that ends by returning the results of a select statement or dataset.
I use the logic in other sprocs too.
Can I Isert the returned dataset into a table variable or user table. sp_AddNamesList returns a list of names. For example something like....
INSERT INTO Insurance (Name)
Exec sp_AddNamesList
Thanks,
Mike
I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.
Here is an example:
-- DROP PROCEDURE testProc
CREATE PROCEDURE testProc AS
BEGIN
SELECT '1111' as col1, '2222' as col2
END
-- this call will fail with message Invalid object name '#tmpTable'.
INSERT INTO #tmpTable EXEC testProc
--- DROP TABLE testTable
CREATE TABLE testTable (col1 varchar(5), col2 varchar(5))
-- this call will succeed
INSERT INTO testTable EXEC testProc
My stored procedure returns one row. I need to insert the result to a table. Can I right something like that:
=============
Insert into table1
exec myProc '1/1/15', 3/1/15'
=====
Or may be I can use Table-valued function insted of myProc?
hi need help
how to "select from pivot stored procedure and insert into table"
this line
INSERT INTO [nili].[dbo].[tb_pivot_edit]
tnx
Code Snippet
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080401', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @NumDays = 28
BEGIN
SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28])
) AS p
END
ELSE IF @Numdays = 30
BEGIN
SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM (
SELECT ID,new_unit,mhlka_id,mhlka,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
INSERT INTO [nili].[dbo].[tb_pivot_edit]
([Fname]
,[new_unit]
,[mhlka_id]
,[mhlka]
,[fld1]
,[fld2]
,[fld3]
,[fld4]
,[fld5]
,[fld6]
,[fld7]
,[fld8]
,[fld9]
,[fld10]
,[fld11]
,[fld12]
,[fld13]
,[fld14]
,[fld15]
,[fld16]
,[fld17]
,[fld18]
,[fld19]
,[fld20]
,[fld21]
,[fld22]
,[fld23]
,[fld24]
,[fld25]
,[fld26]
,[fld27]
,[fld28]
,[fld29]
,[fld30]
,[fld31])
END
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
I have a SP with Parameters as:
R_ID
P1
P2
P3
P4
P5
I need to insert into a table as below:
R_ID P1
R_ID P2
R_ID P3
R_ID P4
R_ID P5
How can I get this?
I have a need to insert stored procedure output a table and in addition to that add a datetimestamp column.. For example, Below is the process to get sp_who output into Table_Test table. But I want to add one additional column in Table_test table with datetimestamp when the procedure was executed.
insert into Table_Test execute sp_who
Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies View RelatedI have the following query in sql 2005:
PROCEDURE [dbo].[uspInsert_Blob] (
@fName varchar(60),
@fType char(5),
@fID numeric(18, 0),
@bID char(3),
@fPath nvarchar(60)
)
as
DECLARE @QUERY VARCHAR(2000)
SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)
SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)
AS Document"
EXEC (@QUERY)
This puts some values including a pdf or .doc file into a table, tblDocTable.
Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.
tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.
Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.
Any ideas how to do this?
Hi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
View 4 Replies View RelatedHi, I have a question regarding inserting numbers automatically. For example, there are 2 fields. One is CountryID and other is CountryName. From Front-end I'm entering the Country Name "India" alone and in my table the CountryID "1" or what ever the sequence should be entered. I believe this can be done using Triggers. I read on KB article in Microsoft website. But didn't get clear idea. Is that the correct way?Can any one show me a small example of this? Or give an idea for this?
View 5 Replies View RelatedI’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox.
Option 1: Presently implemented:
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text)
Although ithis does what is needed it generates the following inner exception
ParamValue {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime} System.Data.SqlTypes.SqlDateTimeDayTicks
<error: an exception of type: {System.Data.SqlTypes.SqlNullValueException} occurred> Integer IsNull True Boolean
Option 2:
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.DBNull.Value, dtDateFollowUp.Text)
No exceptions, no problems that I have seen yet.
Option 3:
Dim dtNull As System.Data.SqlTypes.INullable
Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", dtNull, dtDateFollowUp.Text)
No exceptions, no problems that I have seen yet.
Any advice would be greatly appreciated!
Thanks
Craig
CTARP2
I need to automatically update a datetime field for a record to the current time whenever the record is updated.
create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go
One option would be to use an instead of update trigger.
create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go
update t set value='dick' where id=1 and name='fred'
go
Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.
Any ideas?
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
Hello,
I would like to know the sql-query to insert a new row into a table which has only one column (ID) which is the primary key.
Thanks a lot for any suggestion !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
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 RelatedHow 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
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
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
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.
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