Help With Stored Procedures Needed !!!

Sep 24, 1998

Hi,

I am trying to create a SP where selected fields are passed as parameters. Can
this be done ?

Thank you for your time,
Robert

View 2 Replies


ADVERTISEMENT

Help With Stored Procedures Needed -- URGENT

Jul 20, 2005

Hi all!I am in need of writing a few stored procedures.The first one is to create a stored procedure to recover a databasefrom backup and the second one is to create a stored procedure toexecute a transaction log backup (even though I know this can be donethrough a maintainence plan). Any help would be greatly appreciated.I know the commands to recover and backup -- but I dont know how toformulate them into a stored procedureThanks in advance!

View 1 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

Stored Proc Help Needed

Aug 8, 2007

 I have a created a stored procedure which gets the values from the 'Orders' table. The procedure goes like this:
CREATE PROC DataSource_GetDetails@OrderId int
ASDECLARE @ColNumber int
SELECT @ColNumber = (Select Coalesce(Col_length('Orders','BranchId'),0))
IF(@ColNumber >0)
BEGIN
SELECT Orders.OrderID,
Orders.Buyer,Orders.Seller
Orders.BranchIDFROM Orders
WHERE Orders.OrderID = @OrderId
END
ELSE
BEGIN
SELECT Orders.OrderID,
Orders.Buyer,Orders.Seller
FROM Orders
WHERE Orders.OrderID = @OrderId
END
 Here  i check whether a column named 'BranchId' exists in the 'Orders' table in a particular database and based on the result 2 different select statements are returned. The problem is, if i run this stored proc in the database which does not have a column 'BranchId' it throws me an errorMsg 207, Level 16, State 3, Line 1
Invalid column name 'BranchID'.
How do i tackle this in the stored proc?
Thanks

View 22 Replies View Related

Stored Procedure Help Needed

Jun 22, 2005

SP's are still not my forte, but getting there!I am trying to hit two tables in this one and return the values but keep getting the error code = 1 built into the sp and can't see why.CREATE PROCEDURE dbo.retrieveScores @studentId         VARCHAR(20), @courseId         VARCHAR(20), @lessonLocation    VARCHAR(20)  OUTPUT, @lessonStatus      VARCHAR(20)  OUTPUT,  @lessonScoreRaw      VARCHAR(10)  OUTPUT, @lessonScoreMin      VARCHAR(10)  OUTPUT, @lessonScoreMax      VARCHAR(10)  OUTPUT,  @objId            VARCHAR(20)  OUTPUT, @objStatus        VARCHAR(20)  OUTPUT, @objScoreRaw      VARCHAR(10)  OUTPUT, @objScoreMin      VARCHAR(10)  OUTPUT, @objScoreMax      VARCHAR(10)  OUTPUT  ASBEGIN DECLARE @errCode   INT
 DECLARE c_courseScores CURSOR FOR  SELECT lessonLocation, lessonStatus,   lessonScoreRaw, lessonScoreMin, lessonScoreMax          FROM   courseScores  WHERE  studentId = @studentId AND courseId = @courseId FOR READ ONLY
 OPEN c_courseScores  FETCH c_courseScores INTO @lessonLocation, @lessonStatus,    @lessonScoreRaw, @lessonScoreMin,         @lessonScoreMax CLOSE c_courseScores DEALLOCATE c_courseScores
 DECLARE c_objScores CURSOR FOR  SELECT  objId, objStatus,    objScoreRaw, objScoreMin, objScoreMax          FROM   objScores  WHERE  studentId = @studentId AND objId = @objId AND courseId = @courseId FOR READ ONLY
 OPEN c_objScores  FETCH c_objScores INTO    @objId, @objStatus, @objScoreRaw, @objScoreMin,         @objScoreMax CLOSE c_objScores DEALLOCATE c_objScores
 
 IF( @@FETCH_STATUS <> 0 ) BEGIN  SET @errCode = 1  GOTO HANDLE_APPERR END
 SET @errCode = 0 RETURN @errCode
HANDLE_APPERR: IF( CURSOR_STATUS( 'local', 'c_courseScores' ) >= 0 ) BEGIN  CLOSE c_courseScores  DEALLOCATE c_courseScores END
 SET @errCode = 1 RETURN @errCodeENDGOSuggestions?Thanks all,Zath

View 1 Replies View Related

Stored Procedure Help Needed...please Help Me.

Sep 30, 2007

userid logintime logouttime subject question answer correct status
1000 9/2/2007 12:10:10 chemistry 0
1000 chemistry 1 T 1
1000 chemistry 2 F 0
1000 chemistry 3 a 1
1000 chemistry 4 a 0
1000 chemistry 5 b 1
1000 9/2/2007 12:20:30 chemistry 1
3000 9/2/2007 12:40:00 Math 0
3000 Math 1 c 1
3000 Math 2 a 1
3000 Math 3 T 0
3000 Math 4 F 1
3000 Math 5 b 0
3000 9/2/2007 12:50:25 Math 1
2000 9/2/2007 12:10:10 Biology 0
2000 Biology 1 T 1
2000 Biology 2 F 0
2000 Biology 3 a 1

Logintime means time person started the test.
Logouttime means time person stopped the test.
Correct 1 means correct answer
Correct 0 means wrong answer
Status 0 means test is started.
Status 1 means test is stopped. (Some time people don't stop test due to network failure. So that record got no status 1 or logouttime).


i need to find total score of correct answer.

Based on below table, i need to find who passed and who failed.
Subject Total_Question Main_Question Pass_Score
Math 5 3,5 3
Biology 5 4 4
Chemistry 5 1,2 5

i need to write a stored procedure to do calculation.....so i want to find the below result......

userid logintime logouttime Duration Subject Score Question_Missed Result
1000 9/2/2007 12:10:10 9/2/2007 12:20:30 10:20 Chemistry 2 2 Fail
3000 9/2/2007 12:40:00 9/2/2007 12:50:25 10:25 Math 3 3,5 Pass
2000 9/2/2007 12:10:10 Biology 2 4 Fail
How can i combine both table and get this result?...Please help me. I need help.







View 13 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Calling Stored Procedures From Another Stored Procedure

May 8, 2008

I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.

The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?

Thanks in advance

John

View 5 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Help Needed With Simple Stored Procedure

Mar 31, 2007

Hey all,
Can anyone tell me why this stored procedure doesn't work?
 
ALTER PROCEDURE [dbo].[RemoveLocation]
@Id int,
@Name varchar
AS
DELETE FROM Classifieds_Terminals
WHERE [Id] = @Id and [Name] = @Name
 
 
I try exeuting it like this:
USE [CLASSIFIEDSDB2.MDF]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[RemoveLocation]
@Id = 18,
@Name = N'Terminal A'
SELECT 'Return Value' = @return_value
GO
 
 
It returns 0 and nothing happens....???
 
Here is the table:
id | Name 
18 Terminal A18 Terminal B18 Terminal C

View 2 Replies View Related

Redefine Stored Procedure - Help Needed

May 1, 2007

Hi,
I have a stored procedure which returns a dataset after some intense calculations. It actually calculates the daily work hours (wages) for each employee in a month. I am using a temporary table where for each employee, a day's calculations are stored as a row. So if e.g. there are 2 employees, a total of 2 x 31 = 62 rows are saved in the temporary table. In the end i flip the temporary table horizontally (each employee row than displays daily calculations for upto 31 days possibly) to produce a monthly report. The problem is that as the data has grown alot, it takes a very very long time (more than a minute) to calculate these values. The actual time is consumed in flipping the table horizontally. User can select any montly from Jan-Dec.
Can any expert given me some advice on redefining this stored procedure (to optimize for speed) where i can calculate monthly wedges (each day included in the report) for each employee (over 400). In short, we can look at all daily calculations (31 days possibly) in one row for each employee.
Regards....

View 7 Replies View Related

Stored Procedure Advice Needed

Jul 20, 2007

How do most people handle database searches for things like a product database?  What I mean by that is your typical product table may look like:
ProductIDProductTitleProductDescriptionProductCategoryIDEtc...
Assuming you have a Full Text Catalog set up on the ProductDescription field, would you use Dynamic Sql or a Stored Procedure?
I'm wrestling with this because I haven't found a good way to either parse a parameter in SQL to make a stored procedure work with the same flexibility as Dynamic TSQL, and I would prefer not to have any direct access to the table from the application.
My other option (which to me isn't a great one) is to have a fairly large number of optional "keyword" parameters in the stored procedure and then parse them on the application side...this is less than favorable in that in theory someone could pass more keywords than I've allotted in my stored procedure.
 How would you approach this challenge?
Thanks in advance.
Ryan

View 2 Replies View Related

Is Commit Needed In Stored Procedure?

Sep 19, 2007

If I have a stored pocedure that just does one simple update command, do I need a commit after the update even if it is not part of a transaction. Or does it commit automatically? Thanks!

View 2 Replies View Related

Stored Procedure Assistance Needed

Jun 23, 2000

Hello everyone. I've attached a copy of my recently created stored procedure but now I want to expound on it by creating synatx within it that will allow me to create a 'weighting' type of scenario based on pre-determined weight/ranking classifications (example: a selection of skill '1' would grant the user 2 points toward their ranking, a selection of skill '2' might grant the user 4 pts., etc.) In the end, the users would be tallied and sorted based on the highest ranking (in pts) to the lowest.
The business I'm in is that we develop a web site interface for recruiters and potential job seekers to post resumes, develop a career plan and rank their current work status against the open market.
In short, does anyone out there know how I can implement a "ranking" type system into the syntax provided below?
I've considered the CASE statement but was not clear on how it would work.
Any suggestions would be great.

Claude
cjohnson@staffmentor.net



CREATE PROCEDURE spListMatch

@job_id uniqueidentifier
AS
declare @jobcity varchar(50)
declare @jobposition uniqueidentifier
declare @jobrelocate bit
declare @jobtravel uniqueidentifier
declare @jobyears int
declare @jobIndustry uniqueidentifier
declare @Jobstate varchar(2)
declare @candcity varchar(50)
declare @candposition uniqueidentifier
declare @candrelocate bit
declare @candtravel uniqueidentifier
declare @candstate varchar(2)
declare @candindustry uniqueidentifier
declare @candyears int
declare @holdid uniqueidentifier
declare @candidateid uniqueidentifier
declare @displayid int
declare @ks1 varchar(50)
declare @ks2 varchar(50)
declare @ks3 varchar(50)
declare @ks4 varchar(50)
declare @ks5 varchar(50)
declare @match int
declare @key_skill_desc varchar(50)
declare @strongest int
declare @candIndustrydesc varchar(50)
declare @candPositiondesc varchar(50)
declare @candTraveldesc varchar(50)
declare @prefcity varchar(50)
declare @prefstate varchar(2)
declare @citymatch int

declare @icount numeric
declare @totcount numeric
declare @debug int
select @debug = 1

set nocount on
select @jobcity = city, @jobposition = position_id, @jobrelocate = relocate_assist, @jobtravel = travel_id, @jobstate = state, @jobyears = position_yrs from t_job_order where job_id = @job_id


select @totcount = count(*) from t_job_vstat where job_id = @job_id


select @totcount = @totcount + 3

DECLARE Cand_Cursor CURSOR FOR


select candidate_id, key_skill_desc, strongest from t_cand_vstat, t_key_skill where t_cand_vstat.key_skill_id in (select key_skill_id from t_job_vstat where job_id = @job_id) and
t_cand_vstat.key_skill_id = t_key_skill.key_skill_id
order by candidate_id



CREATE TABLE #ReturnTemp (
candidateid uniqueidentifier NOT NULL,
displayid int,
city varchar(50),
state varchar(2),
Industry varchar(50),
travel varchar(50),
position varchar(50),
hitcount smallint,
tpercent numeric,
ks1 varchar(50),
ks2 varchar(50),
ks3 varchar(50),
ks4 varchar(50),
ks5 varchar(50)
)


OPEN Cand_Cursor

declare @candidate_id uniqueidentifier

FETCH NEXT FROM Cand_Cursor into @candidate_id, @key_skill_desc, @strongest
select @holdid = @candidate_id

WHILE @@FETCH_STATUS = 0
BEGIN

if @candidate_id <> @holdid
begin
select @icount = @icount + 1
if @match = 1
update #ReturnTemp set hitcount = @icount, tpercent = (@icount/@totcount * 100), ks1 = @ks1, ks2 = @ks2, ks3 = @ks3, ks4 = @ks4, ks5 = @ks5 where candidateid = @holdid
select @match = 1
select @ks1 = ""
select @ks2 = ""
select @ks3 = ""
select @ks4 = ""
select @ks5 = ""
select @holdid = @candidate_id
select @icount = 1
select @candrelocate = relocate, @candtravel = travel_id from t_cand_pref where candidate_id = @candidate_id
select @candcity = city, @candstate = state, @displayid = display_id from t_candidate1 where candidate_id = @candidate_id
select @candposition = position_id, @candyears = position_yrs, @candindustry = cat_sub_cat_id from t_cand_seek where candidate_id = @candidate_id
if @candposition = @jobposition select @icount = @icount + 10
if @candyears = @jobyears select @icount = @icount + 8
if @candtravel = @jobtravel
begin
select @icount = @icount + 2
end


else if @jobtravel <> '91C858C8-4A46-4FD8-9B73-87FEE00F799E'
begin
if @candtravel = '91C858C8-4A46-4FD8-9B73-87FEE00F799E'
begin
select @match = 0
end
else
begin

select @icount = @icount + 1
end
end

DECLARE City_Cursor CURSOR FOR




select distinct city, state from t_cand_pref_city_state C, t_city_state S where
c.city_state = s.city_state and C.candidate_id = @candidate_id


OPEN City_Cursor


FETCH NEXT FROM City_Cursor into @prefcity, @prefstate

WHILE @@FETCH_STATUS = 0
BEGIN


FETCH NEXT FROM City_cursor
into @prefcity, @prefstate
select @citymatch = 0
if ((@prefcity = @jobcity) and (@prefstate = @jobstate))
begin
--do nothing
select @citymatch = 1
select @icount = @icount + 1
end


END



CLOSE City_Cursor

DEALLOCATE City_Cursor

if @citymatch = 0
select @match = 0

if @candindustry <> @jobindustry
select @match = 0
if @strongest = 1
begin
if @ks1 = ""
select @ks1 = @key_skill_desc
else if @ks2 = ""
select @ks2 = @key_skill_desc
else if @ks3 = ""
select @ks3 = @key_skill_desc
else if @ks4 = ""
select @ks4 = @key_skill_desc
else if @ks5 = ""
select @ks5 = @key_skill_desc
end
if @match = 1
begin

select @candIndustrydesc = cat_sub_desc from t_cat_sub_cat where cat_sub_cat_id = @candIndustry
select @candPositiondesc = position_desc from t_position where position_id = @candPosition
select @candTraveldesc = travel_desc from t_travel where travel_id = @candtravel

INSERT INTO #ReturnTemp(Candidateid,
displayid,
city,
state,
Industry,
travel,
position,
hitcount)



values (@candidate_id,
@displayid,
@candcity,
@candstate,
@candIndustrydesc,
@candtraveldesc,
@candpositiondesc,
@icount)


end
end



else
begin



if @strongest = 1
begin
if @ks1 = ""
select @ks1 = @key_skill_desc
else if @ks2 = ""
select @ks2 = @key_skill_desc
else if @ks3 = ""
select @ks3 = @key_skill_desc
else if @ks4 = ""
select @ks4 = @key_skill_desc
else if @ks5 = ""
select @ks5 = @key_skill_desc
end
select @icount = @icount + 1
end
--look at other stuff

FETCH NEXT FROM Cand_cursor
into @candidate_id, @key_skill_desc, @strongest


END



CLOSE Cand_Cursor

DEALLOCATE Cand_Cursor

select * from #ReturnTemp

View 2 Replies View Related

Help With Stored Procedure Needed ASAP!

Oct 12, 2004

Hello,
I'm new to SQL. I wrote this Stored Procedure but it does not work. When I'm executing it I get these errors:

Server: Msg 170, Level 15, State 1, Procedure Test, Line 12
Line 12: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 15
Line 15: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 19
Line 19: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Procedure Test, Line 24
Line 24: Incorrect syntax near '='

Can anyone please help me with that?
Thank you in advance.




CREATE PROCEDURE Test As
BEGIN TRANSACTION
Select PVDM_DOCS_1_5.DOCINDEX1, TableTest.DOCINDEX2, TableTest.DOCINDEX3, TableTest.DOCINDEX4
From PVDM_DOCS_1_5, TableTest

WHERE TableTest.DOCINDEX1 = PVDM_DOCS_1_5.DOCINDEX1

AND (TableTest.DOCINDEX2 Is NULL or TableTest.DOCINDEX2 ='' OR TableTEst.DOCINDEX3 Is NULL or TableTest.DOCINDEX3 ='' or TableTest.DOCINDEX4 is NULL or TableTEst.DOCINDEX4 = '');


IF TableTest.DOCINDEX2 is NULL or TableTest.DOCINDEX2 = ''
UPDATE TableTest.DOCINDEX2 = DOCINDEX2
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF

If TableTest.DOCINDEX3 is NULL or TableTest.DOCINDEX3 = ''
UPDATE TableTest.DOCINDEX3 = PVDM_DOCS_1_5.DOCINDEX3
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF

If TableTest.DOCINDEX4 is NULL or TableTest.DOCINDEX4 = ''
UPDATE TableTest.DOCINDEX4 = PVDM_DOCS_1_5.DOCINDEX4
WHERE (DOCINDEX1 IN
(SELECT DOCINDEX1
FROM PVDM_DOCS_1_5))
END IF;

DELETE PVDM_DOCS_1_5 WHERE DOCINDEX1 = DOCINDEX1

IF (@@ERROR <> 0) GOTO on_error

COMMIT TRANSACTION
-- return 0 to signal success
RETURN (0)


on_error:
ROLLBACK TRANSACTION
-- return 1 to signal failure
RETURN (1)
GO

View 2 Replies View Related

Trigger/Stored Proc Help Needed

Nov 29, 2007

I am fairly new to using triggers and stored procs in SQL Server and could use some help.

Scenario:

I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.

Questions:
1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?

2) Can a Stored Proc be used in a trigger?

3) Can a Stored Proc call an outside function, such as an API call for third party software?

Thanks in advance.

View 1 Replies View Related

Stored Procedure Problem ? Help Needed...

Aug 22, 2006

While trying to run an .exe file which analyzes the log files from IIS 5.0, for a web-site, I get this error...

Run-time error '5'. Invalid procedure call or argument

Is it possible this error to be generated from a stored procedure ???
:confused:

Thanks !

View 13 Replies View Related

Hewlp Needed Creating A Stored Proceedure

Feb 21, 2006

Can someone please help me....I have created a DNN module that works on
the test site but when I upload the module zip to a new site I get an
error on creating my stored proceedure as follows:



StartJob
Begin Sql execution

Info
Executing 01.00.00.SqlDataProvider

StartJob
Start Sql execution: 01.00.00.SqlDataProvider file

Failure
SQL Execution resulted in following Exceptions:
System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'.
Line 51: Incorrect syntax near '@Str_Title'. at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText) at
DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean
UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder
tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF
ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID],
[PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner],
[Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like
COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text
'%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN
@SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either
field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text],
[DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID =
@PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like
COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN
DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC

EndJob
End Sql execution: 01.00.00.SqlDataProvider file


The SP looks like this:

/* -------------------------------------------------------------------------------------
/   ListTAS_Journal
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
    @PortalID int,
    @SortOrder tinyint = NULL,
    @Str_Title varchar(100) = '',
    @Str_Text varchar(100) = ''
AS

IF ISNULL(@Str_Title, '') = '' or   ISNULL(@Str_Text, '') = ''

SELECT
    [EntryID],
    [PortalID],
    [ModuleID],
    [Title],
    [Text],
    [DateAdded],
    [DateMod],
    [Owner],
    [Access]
FROM
    TAS_Journal
WHERE
    PortalID = @PortalID AND
    (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND
    Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))


ORDER BY
    (CASE
    WHEN     @SortOrder = 1 THEN DateAdded
    WHEN     @SortOrder = 0 THEN DateMod
    END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
    [EntryID],
    [PortalID],
    [ModuleID],
    [Title],
    [Text],
    [DateAdded],
    [DateMod],
    [Owner],
    [Access]
FROM
    TAS_Journal
WHERE
    PortalID = @PortalID AND
    (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR
    Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))


ORDER BY
    (CASE
    WHEN     @SortOrder = 1 THEN DateAdded
    WHEN     @SortOrder = 0 THEN DateMod
    END) DESC, EntryID DESC
GO


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This SP works on the test site.
Any help would be creatly apreciated

Mark

View 2 Replies View Related

Assistance With Stored Procedure And ASPX Page Needed

Nov 7, 2007

Hello, I have the following stored procedure and the following aspx page.  I am trying to connect this aspx page to the stored procedure using the SqlDataSource.  When the user enters a branch number in textbox1, the autonumber generated by the database is returned in textbox2.  I am not quite sure what to do to get this to execute.  Can someone provide me assistance?  Will I need to use some vb.net code behind?
Stored ProcedureCREATE PROCEDURE InsertNearMiss             @Branch Int,            @Identity int OUT ASINSERT INTO NearMiss            (Branch)VALUES            (@Branch) 
SET @Identity = SCOPE_IDENTITY() 
GO
 
ASPX Page
     <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NearMissConnectionString %>"        InsertCommand="InsertRecord" InsertCommandType="StoredProcedure" SelectCommand="InsertRecord"        SelectCommandType="StoredProcedure">        <SelectParameters>            <asp:ControlParameter ControlID="TextBox1" Name="Branch" PropertyName="Text" Type="Int32" />            <asp:ControlParameter ControlID="TextBox2" Direction="InputOutput" Name="Identity" PropertyName="Text" Type="Int32" />        </SelectParameters>        <InsertParameters>            <asp:Parameter Name="Branch" Type="Int32" />            <asp:Parameter Direction="InputOutput" Name="Identity" Type="Int32" />        </InsertParameters>    </asp:SqlDataSource>        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

View 2 Replies View Related

Help Needed : Not Able To See Fields Value When Creating A Report By Calling A Stored Procedure

Mar 23, 2008

Details :
Reporting Services 2000, SQL 2000 database, Visual Studio . Net 2003

In Report Design view

In "Data" tab, I can see records for column 'sRCName' returned from the stored procedure(usp_GetRouteCodeData) after clicking '!' icon. When I moved to "Preview" tab, I am getting below error message.
"The value expression for the textbox €˜sRCName€™ refers to the field €˜sRCName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Observation : there is no value returned from the dataset on 'Fields' panel. The SP is accessing a table called tblRCM.
If I go to the Data--> Dataset --> Query, change the "Command Type" from 'Stored Procedure' to 'Text' and entered
select * from tblRCM at Query string area, the report is running fine.

Issue: This issue only happens at my laptop, my team member can create the same report using the same stored procedure without any error. The database is sitting on a server.

In the troubleshooting process, I tried to create a simple report by calling a stored procedure(CustOrderHist) from NorthWind DB in my local SQL server, I am able to see the data/value in 'Fields' panel and sucessfully view the data in 'Preview' tab.
Looks like the issue only happen on my machine, for a report that using stored procedure to access a DB sitting on a server.

I hope to hear from anyone who have encountered the similiar issue before, or, have any clue to resolve the issue.

Thanks.



View 3 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

View 3 Replies View Related

Stored Procedures

Jun 17, 2006

hi

i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend

looking forward for a replygayathri

View 1 Replies View Related

Stored Procedures

Dec 15, 2006

I am interested to know about stored procedures in Mssql .Can anyone please help me out.
 
Thanx in advance. 

View 1 Replies View Related

Help With Stored Procedures

Feb 24, 2007

Hello I have two stored procedures
@ID INT
AS
SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER
FROM (SELECT (SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM,
(SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X
and
@ID INT
AS
SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL(
(SELECT SUM(TRANSACTION_VALUE)
FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1
GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P
undefined P
 
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings
 I would like to combine the results so I can get the percentage and wininngs in one query,  in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player
Thanks in advance for any light you can shine on this.

View 4 Replies View Related

Stored Procedures

Feb 25, 2007

I have a question about stored procedures, Is it better to use stored procedures even if I only use it once at my site? Or is it better to write the sql-part directly in the sqldatasource?
And am I forced to create two different stored procedures even if they are exactly the same except the "Where-part"?
Now I have around 40 stored procedures, and quite many of them looks the same except the where-part...
(Iam just a beginner with SQL)

View 2 Replies View Related







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