The Multi Delete && Multi Update - Stored Procedure Not Work Ok

Feb 4, 2008

the stored procedure don't delete all the records
need help



Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0




TNX

View 2 Replies


ADVERTISEMENT

Spaces Problem On Multi Update Delete Stored Procedure

Feb 5, 2008


spaces problem on multi update delete stored procedure
on the the insert i get it like this


58830803, 55329429, 308962604, 55696314, 309128726



as you see only the first empid=58830803 is without spaces
how to fix this
my code




Code Snippet
DECLARE @empid varchar(500)
set @empid ='58830803, 55329429, 308962604, 55696314, 309128726'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0




TNX

View 4 Replies View Related

Help Send An Personal Email From Database Mail On Row Update-stored PROCEDURE Multi Update

May 27, 2008

hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email


i use FUNCTION i get on this forum to use split from multi update

how to loop for evry update send an single eamil to evry employee ID send one email

i update like this


Code Snippet
:

DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3

now
how to send an EMAIL for evry ROW update but "personal email" to the employee



Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'


TNX

View 2 Replies View Related

Stored Procedure Multi Update Problem - Limited Rows

May 25, 2008

hi need help
i have this stored procedure
the problem is that
i canot update like this not more than 20 - 30 rows
(i send it from a web page from check box)
it work but it limited rows for update not more than 20 - 30 rows
in one time



Code Snippet
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [sto_update_snha]
@id varchar(1000)
as
UPDATE Snha
SET fld5 = 3
WHERE charindex(','+CONVERT(varchar,[id])+',',','+@id+',') > 0 AND (fld5 = 2)




TNX

View 16 Replies View Related

Passing Multi Value Parameter To Stored Procedure

Aug 7, 2007

I wrote a Stored Procedure spMultiValue which takes Multi Value String Parameter "Month". The stored procedure uses a function which returns a table. when I Execute the stored procedure from SQL Server 2005 with input "January,February,March" everything works fine.
In the dataset , I set command type as Text and typed the following statement.
EXEC spMultiValue " & Parameters!Month.Value &"
its not returning anything.
can anyone tell me how to pass multivalue parameter to stored procedure.
Thanks for your help.

View 2 Replies View Related

Stored Procedure Multi-value Parameter And Return Value

Feb 12, 2008

What is the best way to design this stored procedure for speed?

I want to pass in a list of IDs and have a return value of a table that contains the ID and its associated value. Is there a way to pass in as table and come out as table?

input parameter
ID
0
1
2

return value
ID, CurrentDue, PastDue
0, 100, 0
1, 100, 100
2, 123, 0

View 11 Replies View Related

Using Stored Procedure To Implement Multi-keywords Query

Mar 28, 2000

Hello,

I am greener in using SQL Server 7.0. I created a stored procedure named usp_Test (please see the following codes for reference). It worked fine when passed through a single keyword into it (for example usp_Test 'Satellite'), but it did not return the query results what I wanted when passed through multiple keywords into it (for example usp_Test 'Satellite Remote'). In fact, it returned nothing but the column names specified in SELECT statement. Below is the sample of procedure how it looks like. Where am I wrong? Any idea?

CREATE PROCEDURE usp_Test
@strKeywords varchar(50)
AS

DECLARE @DelimiterPos integer
DECLARE @Delimiter char(1)
DECLARE @strWHERE varchar(1000)

SET @DelimiterPos = 0
SET @Delimiter = CHAR(32)
SET @strWHERE =""
SET @strKeywords = RTRIM(LTRIM(@strKeywords))

SET @DelimiterPos = CHARINDEX(@Delimiter,@strKeywords,1)

IF @DelimiterPos > 0
BEGIN
WHILE (@DelimiterPos > 0)
BEGIN
IF LEN(@strWHERE) > 0
BEGIN
SET @strWHERE = @strWHERE + " AND tblClips.Title Like %" + LEFT(@strKeywords,@DelimiterPos - 1)+ "%" + CHAR(13)
END
ELSE
BEGIN
SET @strWHERE = @strWHERE + "%" + LEFT(@strKeywords,@DelimiterPos - 1) + "%" + CHAR(13)
END
SET @strKeywords = LTRIM(RIGHT(@strKeywords, LEN(@strKeywords) - @DelimiterPos))
SET @DelimiterPos = CHARINDEX(@Delimiter, @strKeywords, 1)
IF @DelimiterPos > 0
BEGIN
CONTINUE
END
ELSE
IF LEN(@strKeywords) > 0
BEGIN
SET @strWHERE = @strWHERE + " AND tblClips.Title Like %" + @strKeywords + "%" + CHAR(13)
BREAK
END
END
END
ELSE
BEGIN
SET @strWHERE = @strWHERE + "%" + @strKeywords + "%"
END
PRINT 'WHERE tblClips.Title Like ' + @strWHERE
SELECT tblClips.Title,tblTapes.ClassificationNo,tblTapes. Inventory
FROM tblClips INNER JOIN (tblClipTape
INNER JOIN tblTapes
ON tblClipTape.fkTapeID = tblTapes.TapeID)
ON tblClips.ClipID = tblClipTape.fkClipID
WHERE tblClips.Title LIKE @strWHERE

Thank you very much in advance.
Changbaoi

View 1 Replies View Related

Pass Multi-values In A Single Parameter For Stored Procedure

Apr 16, 2013

I got issue when passing multiple values to a single parameter. Here is my stored procedure as following:

CREATE PROCEDURE [dbo].[School]
@Grade AS varchar(50),
@Class As varchar(50)
AS
BEGIN
SELECT Name, Grade, Class
FROM School
WHERE Grade = (IsNull(@Grade, Grade)) AND Class IN (IsNull(@Class, Class ))
END

In the front end, I got multiple values for Subject parameters such as Math, English, Reading, etc... in a specified class. How do I can modify my above stored procedure to receive multiple values for a single parameter.

View 14 Replies View Related

Error With Multi-Valued Report Parameter Using Stored Procedure

Dec 19, 2007

Hi All,
I'm unable to run the report the report with multi-valued parameter using the below StoredProcedure as dataset:

CREATE PROCEDURE spprodsales
@productid int

AS

select SalesOrderID,OrderQty,UnitPrice,ProductID

FROM Sales.SalesOrderDetail

Where ProductID IN (@productid)

RETURN


And when I'm replacing this dataset to a query as below I'm able to run the report with multiple values selected for productid parameter :

select SalesOrderID,OrderQty,UnitPrice,ProductID

FROM Sales.SalesOrderDetail

Where ProductID IN (@productid)

So, can anyone please help me out possibly using the same stored procedure as above.

Thanks,
Kripa

View 5 Replies View Related

Getting Results With Stored Procedure From Single Textbox Searching Table With Multi Columns

Feb 12, 2007

I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.

What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.

1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.

The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.


After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.

Hopefully someone can get me pointed in the right direction! Thanks for your help!

View 12 Replies View Related

SQL 2012 :: Disaster Recovery Options For Multi-Database Multi-Instance Environment

Sep 23, 2014

Disaster Recovery Options based on the following criteria.

--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.

What I have looked into is:

1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.

View 1 Replies View Related

SQL 2012 :: MSDTC In Multi-node / Multi-instanced Cluster

Aug 17, 2015

More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.

1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?

View 9 Replies View Related

Multi Value Select Doesn't Work

Jan 31, 2008

My report runs from a stored proc, which gets the user to input a project id and a status. So the where statement in my stored proc looks like this:


Where (p.project LIKE @project_ID + '%' AND p.status IN(@Active))

On my report, I have set the @Active parameter to multi-value, and entered the available values(active and inactive). When I run the report, and only select one of the values, the report runs just fine, but when I use the "select all" option, I get no results at all.

I can't see where I am going wrong. Anyone got any ideas?

Thanks in advance!

View 4 Replies View Related

Not Much Success With MULTI DELETE

May 11, 2006

Trying to delete records associated with the same contact id from all the tables used for that contact. But I'm getting an error. Strange error because the table does exist and I've been able to do single deletes from that table. So, something tells me I'm not getting something right in my SQL statement. How do I delete records from all 3 tables using the same id (as opposed to doing 3 different SQL calls, one for each)?

-----------------
DELETE names, addresses, phones
FROM names AS n, addresses AS a, phones AS p
WHERE n.id = '12' AND a.id = '12' AND p.id = '12'

Error: Unknown table 'names' in MULTI DELETE
-----------------

(BTW, how do tag my code so it shows up in a code div?)

View 1 Replies View Related

Help With Multi Join Or Multi Tier Select.

Jul 20, 2005

Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid

View 5 Replies View Related

Delete Data On Behalf Of Multi Rows

Mar 13, 2008



HI,

is there any statement by using that i can delete data from the table on behalf multi rows.

like

delete from Table where = (select id from table1 where group=1)

second statemetn returns 4 rows by group =1

is there single statment..

Thanks

View 2 Replies View Related

JDBC 2005 Update Statement - Failing Multi Row Update.

Nov 9, 2007

It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.

View 5 Replies View Related

Multi-database Multi-server

Mar 27, 2007

I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)



I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:



Dataserver A

Database A1

Database A2

Database A3



Dataserver B

Database B1

Database B2



Dataserver C

Database C1

Database C2

Database C3



I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3



Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.



Is this something that Reporting Services is able to handle or do I need to look at some other solution?



Thanks,



Michael

View 5 Replies View Related

Multi Row Update

Jan 5, 2004

Hi All,

I am trying to update a huge table with about 70 million records and the table do not have a primary key.

I want to update a "FLAG" field in the table with value 'N' default value of flag fields for all records is NULL.

I want to update the whole table in chunks say 50,000 records a time...

like
update tablename (first 50000)
set flag = 'N' where flag = NULL.

Hope I am clear..:-)

thanks
hardlyworking..

View 7 Replies View Related

Multi-Table Update??

May 26, 2004

Being an access guy, I am having a hard time understanding why I can't do a join statement on an UPDATE?

What is the alternatives??

I have to match two tables up and use records from one to update the other and creating a view isn't working....

View 5 Replies View Related

Multi-Row Update Trigger

Nov 20, 2007

Hi,

I need to update LastReceivedQty and LastReceivedDate fields in the Product table each time a DeliveryNoteDetail entry is created for a PurchaseOrderDetail line.

DeliveryNote -> DeliveryNoteDetail -> PurchaseOrderDetail -> Product

DeliveryNote has the ReceivedDate
DeliveryNoteDetail has the ReceivedQty

I made the following trigger for handling single row updates, which works fine.

UPDATE Purchasing.Product
SET LastReceivedQty = i.ReceivedQty, LastReceivedDate = dn.ReceivedDate
FROM Purchasing.DeliveryNote dn INNER JOIN
Purchasing.DeliveryNoteDetail dnd ON dn.DeliveryNoteID = dnd.DeliveryNoteID INNER JOIN
inserted i ON dnd.DeliveryNoteDetailID = i.DeliveryNoteDetailID INNER JOIN
Purchasing.PurchaseOrderDetail pod ON dnd.PurchaseOrderDetailID = pod.PurchaseOrderDetailID INNER JOIN
Purchasing.Product p ON pod.VendorVendorProductID = p.VendorVendorProductID

Now I don't know how to handle multi-row situations when the same product is updated.
Since I cannot rely on the order that the updates are performed I need to somehow select the MAX(ReceivedDate).

View 1 Replies View Related

Update Multi File

Aug 24, 2007

Hi,
I have the following case:-

File a(transaction File)__File b(summary File)___File c(summary)
acctno___Value DB/CR_____Acctno DB,CR_______acctno DB,CR
1___________100db___________1______________________1
1___________300cr___________2______________________2
1___________600db
2___________700db

I want to update the account on table b and c from table a.

My current update is just for one table as follows:-
UPDATE amaster
SET cmdb = cmdb +
(SELECT SUM(transact.damt) AS Expr1
FROM transact
WHERE (acctno = amaster.acctno)
GROUP BY acctno),
cmcr = cmcr +
(SELECT SUM(transact.camt) AS Expr1
FROM transact
WHERE (acctno = amaster.acctno)
GROUP BY acctno)
FROM amaster INNER JOIN
transact ON transact.acctno = amaster.acctno



thanks

View 1 Replies View Related

SQL Server Insert Update Stored Procedure - Does Not Work The Same Way From Code Behind

Mar 13, 2007

All:
 I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update.
When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#.
When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening?
Here is the basic shell of my SP:
CREATE PROCEDURE [dbo].[spHeader_InsertUpdate]
@FID  int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime
AS
Declare @rtncode int
IF NOT EXISTS(select * from HeaderTable where FormID=@FID)
 Begin  begin transaction
   --Insert record   Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4)    Values (@FLD1, @FLD2, @FLD3,@FLD4)   SET @FID = SCOPE_IDENTITY();      --Check for error   if @@error <> 0    begin     rollback transaction     select @rtncode = 0     return @rtncode    end   else    begin     commit transaction     select @rtncode = 1     return @rtncode    end      endELSE
 Begin  begin transaction
   --Update record   Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4    where FormID=@FID;
   --Check for error   if @@error <> 0    begin     rollback transaction     select @rtncode = 0     return @rtncode    end   else    begin     commit transaction     select @rtncode = 2     return @rtncode   end
End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 Thanks,
Blue.

View 5 Replies View Related

Stored Procedure For Insert / Update And Delete

Nov 26, 2013

How could I possibly write a SP for Insert, Update and Delete for the below tables under this condition. On some conditions they need to be queried, initially for Insert, first the data should be inserted to PROFILES table and then to ROLES table. When inserting, if an entry is new then it should be added to both the tables. Sometimes when I make an entry which is already present in PROFILES table, then in this case the value should be added to the ROLES table. And a delete query to delete rows from both the table.

CREATE TABLE PROFILES(
USER_ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL

[code]...

View 5 Replies View Related

MULTI-Table Update Queries

Jul 23, 2005

I'm new to adp w/ sql server but I have to use it on a project i'mdoing...One of the MUSTS for this project is the ability to update a 00 - 09text value with the appropriate text description from another table...Easy as pie in .mdb. Of course In the stored procedure it barks at meand tells me that an update query can only have one table.. ouch thathurts...I'm currently reading on the subject but this group has been veryhelpful in the past.....I found this link...http://www.sqlservercentral.com/col...stheeasyway.aspUnfortunetly I'm using MSDE not Enterprise so I don't think I can usethe query analyser.. But I tryed it in my Access ADP anywayit barked at me..I tried to go from this....SELECT dbo.LU_SEX.SEX_CODE, dbo.TEST.DEFECTS_DP1FROM dbo.TEST INNER JOINdbo.LU_SEX ON dbo.TEST.SEX_DP1 =dbo.LU_SEX.SEX_DECTo this...UPDATE dbo.TEST.SEX_DP1SET dbo.TEST.SEX_DP1 = dbo.LU_SEX.SEX_CODEFROM dbo.LU_SEX INNER JOINdbo.TEST ON dbo.LU_SEX.SEX_DEC =dbo.TEST.SEX_DP1Maybe I need a good book on this?Thanks,Charles

View 2 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure To Do Add / Update / Select And Delete

Apr 29, 2015

I would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.

USE [SomeTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]

[Code] ....

So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?

View 8 Replies View Related

Trigger Behaving Differently On Multi-row Update

Aug 1, 2007

Let me set the scene:

I have an update trigger on a table. When a specific column is updated, I get the rowid from 'inserted' and then pass it via service broker to another database that will fire off a maintenance routine at a later time. This whole process seems to work fine if I update a single row at a time through Query Analyzer.

During testing (of the service broker part) I found that if in Query Analyzer I run an update that updates all of the records at once, then the trigger seems to fire only once for the entire process, therefore killing the rest of my process.

I would have thought that regardless of how a record was being updated the trigger would fire atomically for each row.

Any guidance on this would be MOST appreciated!

View 20 Replies View Related

General Stored Procedure, For Insert, Update, Select, Delete?

May 7, 2007

Hi All,
As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery)
that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery
(
@select_query nvarchar(500)
)
as
begin

exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out


end
 
Is this a good approach design, or its bad???
 
Thanks all

View 5 Replies View Related

Multi Search Procedure

May 3, 2007

ALTER PROCEDURE usp_r_search_consultant
@head_line varchar(150)
,@func_area varchar(150)
,@loc varchar(50)
,@exp varchar(50)
,@k_skills varchar(50)
,@operator char(1)
--select * from RPO.dbo.REQUIREMENT_RESOURCE
AS

if(@operator='A')
BEGIN
declare @sql varchar(1000)
select @sql= --'select * from RPO.dbo.REQUIREMENT_RESOURCE where 1=1'
'''SELECT Resume_head_line,Employer,CTC,Qualification1'' + '','' + ''Qualification2 AS Education,Current_city as Location,Preferred_Location From RPO.dbo.REQUIREMENT_RESOURCE (nolock) where 1=1'' '
print @sql

IF @head_line !=''
begin
SELECT @sql = @sql + ' and head_line='''+@head_line+''''

print'1'
print @sql
exec sp_executesql @sql
end

END
whats wrong in the procedure

Malathi Rao

View 13 Replies View Related

T-SQL (SS2K8) :: Update Table With Multi-line Comments?

Apr 14, 2014

How to update column with multiline comments.

eg. I need to update Notification column in my table as below.

Exit Formalities Notification sent to:

Employee: LastName, FirstName
EmployeeEmail: EmployeeEmailAddress

View 4 Replies View Related

I Have One Stored Procedure For Insert, Update And Delete Operations, I Need Automatically Logged

May 27, 2007

I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks

View 1 Replies View Related

How To Create Trigger For Multi Insert Employees Update Tb Employee Once

Jan 24, 2008

how to do this
i have table of employee ,evry employee have a unique ID "empid"
empid VAL_OK
--------------------------
111 0
222 0
333 0

now insert multiple insert to my work_table shifts for all month for evry employee
like this
(this is work_table)
empid date val
--------------------------------------------------
111 01/02/2008 1
111 02/02/2008 2
...............
111 29/02/2008 5
--next employee
222 01/02/2008 1
222 02/02/2008 4
...............
222 29/02/2008 6
--next employee
333
--next employee
444
--next employee
555
-------------------------------------------------------------


now i need for evry OK insert (for all month) each employee
go to the TB_Employee
and update each employee once !!
from VAL_OK=0 to VAL_OK=1
like this

empid VAL_OK
--------------------------
111 1
222 1
333 1
----------------------
like this i know who is the employee have shift for all month and who NOT !

i think it like this



Code Snippet
Create trigger for_insert on tb_work
For insert
begin
if @@rowcount = 1
Update tb_employee
Set
val_ok= 1

else
/* when @@rowcount is greater than 1,
use a group by clause */
Update tb_employee
set
val_ok= 1
select empid from tb_work
group by tb_work.empid

End







TNX

View 4 Replies View Related

(Select All) In Multi-select Enabled Drop Down Parameters Doesn't Work

Apr 29, 2008

Hello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.

select distinct ProductType
from Product
order by ProductType

Any suggestion? thx


View 12 Replies View Related







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