|
Here is an example.
CREATE PROCEDURE MyFirstProc @cParam varchar(10)
AS
PRINT 'MyFirstProc ' + @cparam
EXEC MySecondProc @cparam
GO
CREATE PROCEDURE MySecondProc @cTest varchar(20)
AS
PRINT 'This is in MySecondProc ' + @cTest
GO
Call this from SQL Query Analyzer like this:
EXEC MyFirstProc 'testing'
and you will see this:
MyFirstProc testing<br />
This is in MySecondProc testing
However, you said that "Stored Procedure (A) that is being called contains code for a caculated field." This sounds like you would be better off with a User Defined Function.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Here's the deal,
Table A has a field called 'TimeEstimate'. This field is in Days. DataType Integer.
Table B has a field call 'PercentageOfProgress'. This field is calculated. DataType Integer.
When I enter and save all the data for Table A, The procedure that holds the calculation for 'PercentageOfProgress' needs to be fired. Therefore from within the Stored Procedure which saves the Data for Table A, I want to fire the SP for the Caulculated field which stores THAT RESULT in Table B.
See what I mean?
Also, I tried to pass the Parameter like you said in Query Analyser, it gave me this:
Error converting data type varchar to int.
I will have to revise the SPs. In anycase, if you have any suggestions, Please let me know.
Thank you so much for your reply!
|
|
|
|
|
Since you have not listed the code, If you get Error converting data type varchar to int.
explicitly convert it to int and see what happens.
|
|
|
|
|
The example I gave had a varchar parameter, so I put single quotes around it. If your sproc is expecting an int data type, then don't put quotes around it.
It would be easier to help if you provided some code.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thank you very much for your replies.. much appreciated..
I will be looking at this problem very soon, so check your email for this post =D
Something came up and I have to focus on that first..
I will provide the code soon.
|
|
|
|
|
Hi,
i would like to ask how we can unify two triggers!
In first trigger i want to do something when a column is updated and in the other trigger i want to do something else when a different column is updated at the same table.
Now i have the two triggers and they are working properly, but i still have to do it in the same trigger!
Does anyone know how to do it!
CREATE TRIGGER UPDATE_REST1
ON [DBO].[FINTRADE]
FOR UPDATE
AS
IF UPDATE(FTRIDPAYMENT)
UPDATE FINTRADE
SET Z_YPOLOIPO= (SELECT ISNULL(I.TOTLAMOUNT,0) - ISNULL(PLH.TOTLAMOUNT,0) AS Z_YPOLOIPO
FROM INSERTED I, FINTRADE PLH
WHERE I.FTRIDPAYMENT=PLH.ID)
FROM FINTRADE F, INSERTED I
WHERE F.ID=I.ID
CREATE TRIGGER UPDATE_REST2
ON [DBO].[FINTRADE]
FOR UPDATE
AS
IF UPDATE (TOTLAMOUNT)
UPDATE FINTRADE
SET Z_YPOLOIPO= (SELECT ISNULL(PAR.TOTLAMOUNT,0) - ISNULL(I.TOTLAMOUNT,0) AS Z_YPOLOIPO
FROM INSERTED I, FINTRADE PAR
WHERE PAR.FTRIDPAYMENT=I.ID)
FROM FINTRADE F, INSERTED I
WHERE F.FTRIDPAYMENT=I.ID
Thanks in advance!
andreas
|
|
|
|
|
CREATE TRIGGER UPDATE_REST1
ON [DBO].[FINTRADE]
FOR UPDATE
AS
IF UPDATE(FTRIDPAYMENT)
UPDATE FINTRADE
SET Z_YPOLOIPO= (SELECT ISNULL(I.TOTLAMOUNT,0) - ISNULL(PLH.TOTLAMOUNT,0) AS Z_YPOLOIPO
FROM INSERTED I, FINTRADE PLH
WHERE I.FTRIDPAYMENT=PLH.ID)
FROM FINTRADE F, INSERTED I
WHERE F.ID=I.ID
IF UPDATE(TOTLAMOUNT)
UPDATE FINTRADE
SET Z_YPOLOIPO= (SELECT ISNULL(PAR.TOTLAMOUNT,0) - ISNULL(I.TOTLAMOUNT,0) AS Z_YPOLOIPO
FROM INSERTED I, FINTRADE PAR
WHERE PAR.FTRIDPAYMENT=I.ID)
FROM FINTRADE F, INSERTED I
WHERE F.FTRIDPAYMENT=I.ID
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I have already try this!But the application which runs triggers is treated like two triggers!In the aplication which i work i can't run more than 3 triggers and i have two more!
|
|
|
|
|
andreas_635 wrote: the application which runs triggers is treated like two triggers
I don't understand?
I assume you are using SQLServer, based on the trigger syntax. Which version?
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Greetings:
I have been using SQL Server 2000 on this lap-top from the office for a few months now, learning the basics of SQL. I'm pretty sure that when it was installed, they specified that the logon password would be the same as that which is used to logon to the lap-top itself (NT-Server Authentication?). SQL just automatically started each time I started the LT and I just took all that for granted.
Yesterday, I had to change the password for my laptop as we are required to do every three months here. Now the SQL server won't start; says there's a log-on failure. I have looked through all of the SQL administration applications and the help files and I cannot find a single word about changing the password!
Can anybody advise?
Thanks in advance!
Mark
|
|
|
|
|
I normally install SQL Server to log on as LocalSystem if I don't need domain support, or create a special user in the domain whose password does not expire.
To set the password for SQL Server's service, go to the Services tool (Control Panel, Administrative Tools, Services), right-click the MSSQLSERVER service and choose Properties. Go to the "Log On" tab and enter the new password (twice).
|
|
|
|
|
|
how to insert images into database table from enterprise manager using sqlserver.
|
|
|
|
|
do u want to send the images using a query???
sAqIb
"Our scientific power has outrun our spiritual power. We have guided missiles and misguided men."
Dr. Martin Luther King Jr.
|
|
|
|
|
Hi I am looking for a bit of guidance with an sql query I need to build a query to format my data in the following way
http://www.geocities.com/liverpool_anfieldreds/Images/target_results.JPG
but the data is in the following format using this sql
http://www.geocities.com/liverpool_anfieldreds/Images/current_results.JPG
Select IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',
rzd.Target,
rzd.Total,
DATENAME(WEEKDAY, rzd.Date) as 'Day'
From RedZoneData rzd
Join Person p on p.ID = rzd.PlayerID
Where rzd.PlayerID in (3 ,2)
And rzd.Date Between (Select StartDate From RedZoneWeeklyMapping
Where ID = 15)
And (Select EndDate From RedZoneWeeklyMapping
Where ID = 15)
Order By p.ID, rzd.Date
and im not really sure if its possible to change it around so the days are at the top and totals in their cols etc any help or advice would be appreciated thanks in advance
Tim
|
|
|
|
|
i am using c#.net and sql server2000 for my application.
i amable to do all operations with direct access over the database.
Now i am to connect to the server using Remote Data Service (RDS) and do all data base operations over this remote connection
please help me
|
|
|
|
|
I am using a stored procedure and returning rows to display in my report. I am grouping by a SOURCE field in the stored procedure. When the rows for this source are displayed on separate lines, I suppress the printing of the source on the subsequent lines by using the Previous function. In other words, if I have 5 rows, I print the source on the 1st row (occurrence) but not on rows 2-5. That works great. What I want to do now is sum the data columns for that source by adding a total line. I'm using the Previous function to check when the source changes, but I'm printing the total line on the first occurrence of the new source instead of the last (after row 1 instead of after row 5). I basically need to do a look ahead, determine how many rows I have, and then when I get the last row, print the total line. Is there a way of doing this within Reporting Services?
|
|
|
|
|
I have a table in an access database with a DateTime column. I have a query that is supposed to look for all rows with a specific date in the date column. The problem is, for two Date/Time objects to be equal in Access the time part must also match. I only want it to match on the date part (I use the time for something else so I can leave that out all together). I have tried this:
<br />
SELECT Log.id, Log.LibraryID, Log.Action, Log.Date<br />
FROM Log<br />
WHERE (((Log.LibraryID)=[libid]) AND ((DatePart("mm-dd-yyyy",Log.Date))=DatePart("mm-dd-yyyy",[checkdate])));<br />
But I get an error about how its typed incorrectly or is too complex to be evaluated. How due I match on only the Date?
|
|
|
|
|
DatePart("mm",Log.Date)+"-"+DatePart("dd",Log.Date)+"-"+DatePart("yyyy",Log.Date)
or use format function
|
|
|
|
|
I tried that and still got the too complex error:
<br />
SELECT Log.id, Log.LibraryID, Log.Action, Log.Date<br />
FROM Log<br />
WHERE (((Log.LibraryID)=[libid]) AND (DatePart("mm",Log.Date)+"-"+DatePart("dd",Log.Date)+"-"+DatePart("yyyy",Log.Date))=( <br />
DatePart("mm",[checkdate])+"-"+DatePart("dd",[checkdate])+"-"+DatePart("yyyy",[checkdate])));<br />
|
|
|
|
|
SELECT str(datepart('d',now()))+'-'+str(datepart('m',now()))+'-'+ str(datepart('yyyy',now()))
This worked for me. You can try format function as well.
|
|
|
|
|
ello,
I have a database that will need to be modified, i.e. adding columns, stored procedures, etc.
I can't create a backup as the database is live and will be continue to be updated with customer data.
My colleges what me to create the updates in code which I have done. However, my real problem is how do I know if the database has been updated and is current version.
I will not be updating the data that has been entered, only doing things like:
Alter [tableName] add Salary money null
create procedure dbo.insertPayment .....
example
if(currentVersion) then
'No need to update
Else
'Updates are available
'Add column
'Drop column
'create procedure
'etc
Is there any method that I could use to detect if the database is current or not.
I hope I have been clear on this.
Using VS 2005 & SQL Server 2005
Many thanks in advance,
Steve
|
|
|
|
|
steve_rm wrote: Is there any method that I could use to detect if the database is current or not.
That really depends on your idea of "current".
Before each ALTER statement I'd have an
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableName'
AND COLUMN_NAME = 'ColumnName')
BEGIN
-- Add the column we checked for
ALTER TABLE ....
END
(The above code is for SQL Server 2000, there will be a similar equivalent in SQL Server 2005, but I don't know what it is)
You may also like to take a copy of your live database and practice your scripts on the copy first.
|
|
|
|
|
Hi,
I've a problem when using Carlos Antollini ADO class in a "realtime" application. What I do
is, that I call a thread that does all the database operations once every 10 minutes
or so. But it seems like the database operations are not called asyncron, because
the other threads within the application get freezed!?
Would be great if you could give advice, otherwise I've to rewrite the whole DB part
Thanks
Mario
m_hThread = (HANDLE) _beginthreadex(NULL,0,_ThreadWriteToDB,this, 0,&nDummy);
UINT WINAPI CDBManager::_ThreadWriteToDB(LPVOID pParam)
{
ASSERT(pParam);
CDBManager *pDBMan = reinterpret_cast<cdbmanager *=""> (pParam);
DosSetPriority(PRTYS_THREAD, PRTYC_IDLETIME, THREAD_PRIORITY_IDLE, 0);
pDBMan->m_Con.BeginTransaction();
...
...
...
pDBMan->m_Con.CommitTransaction();
// this thread has finished its work so we close the handle
CloseHandle(pDBMan->m_hThread);
}
|
|
|
|
|
hai,
in my application i want to create one trigger.
the situation is i 've two tables T1 and T2.
when i update T1 i want to insert the data before updated ie old values to table T2.
i used one trigger with 'instead of', but the pblm is T1 is not updated but values inserted to T2 perfromed fine.
any help is appreciated!!
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|