|
consider a payment table with the following fields: billno,invid,amount,date
Also consider tables invoices (contains invid,invno,pkgid) and packages (contain pkgid)...
I need to generate bill for a particular invoice number(invno). That invoice may contain more than 1 packages. I have to generate bill for each packages with a same bill no. If there are 3 packages in invoice number 'I31' then 3 bill must be generated.
How to insert this using package or someother method? I'll pass thes values (billno,invno, and date) from my application to SQL server. The insert command must generate no. of bills that is equal to the number of packages of that invoice. The no. of pkgid can be retrieved by invno, the amount can be retrived by using the pkgid.
Please help me to insert multiple records from one request.
Hope you understand my prob.
Thanks.
|
|
|
|
|
Is is possible to add variables to functions in SQL Server. My issue is I am buildint a dashboard and I am sendint the variables directly from the dropdown menu.
what I need to do is have the ability to modify the Datediff function. By changing the type.
i.e
datediff(@dateRange, getdate())=0
I need to have the @dateRange be a variable instead of (dd, ww, or yy).
Any Ideas?
thanks
|
|
|
|
|
oskardiazdeleon wrote: i.e
datediff(@dateRange, getdate())=0
That's not a legal call to DATEDIFF[^] See the linked documentation for what you actually need.
oskardiazdeleon wrote: Any Ideas?
CASE[^]
CASE @dateRange WHEN 'dd' THEN DATEDIFF(dd, GETDATE(), @someOtherDate)
WHEN 'ww' THEN DATEDIFF(ww, GETDATE(), @someOtherDate)
WHEN 'yy' THEN DATEDIFF(yy, GETDATE(), @someOtherDate)
ELSE 0 END
You could then potentially wrap this in a function[^], however, since GETDATE() is non-deterministic it isn't possible to do what you want, but you could always pass GETDATE() into the function.
e.g.
CREATE FUNCTION DateDifference(@dateRange CHAR(2), @startDate DATETIME, @endDate DATETIME)
RETURNS INT
AS
BEGIN
RETURN CASE @dateRange WHEN 'dd' THEN DATEDIFF(dd, @startDate, @endDate)
WHEN 'ww' THEN DATEDIFF(ww, @startDate, @endDate)
WHEN 'yy' THEN DATEDIFF(yy, @startDate, @endDate)
ELSE 0 END
END
It can be called like this:
SELECT dbo.DateDifference(@dateRange, @someDate, GETDATE())
Does this help?
|
|
|
|
|
Does anyone have any experience with creating data dictionaries for Sql Server?
Our corporation needs to setup a data dictionary to store all our db info however currently, we're just storing it in a spreadsheet. What's the global practice for creating a corporate Data Dictionary?
Or does anyone perhaps know of an online tutorial that explains this?
Thanks.
-Goalie35
|
|
|
|
|
Have you searched Microsoft's web site for database tutorials, if not, then please do, however ...
For non-microsoft produced tutorials about Databases, there are many web sites that can help, here is one you might find helpful ...
http://www.geekgirls.com/menu_databases.htm[^]
and here are some video tutorials
http://www.learnsqlserver.com/VideoTutorials/[^]
Also, make a search here at Code Project, there are many database tutorials at various levels
modified 1-Aug-19 21:02pm.
|
|
|
|
|
Hi,
I am trying to call a procedure within another Stored Procedure.
The Stored Procedure (A) that is being called contains code for a caculated field. The Stored Procedure (B) that is calling it is one which stores data in a table.
Currently I am calling (A) in the following manner from within (B) :
EXEC sp_A
I was told I have to pass a particular parameter when calling (A). How do I do that? is it like this? :
EXEC sp_A(ParameterName)
Let me know if you need anymore detail. The problem with this issue is that, it is sooo complicated if i try to explain it fully. Sorry for the lack to code detail.
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
