SQL/SPROC Beginner: Loop Logic Problem
Sep 23, 2005
hi.
i am getting some weird behaviour in my sql server 2000 code
pasted below. When using the step-through, it seems that
i get to the line: While objReader.Read()
and then the compiler jumps to "End Try" without going inside the objReader.Read() statement. I am new to this and would
appreciate some insight. why isn't entering the conditions within the while loop? I am new to sql and stored procedures so, i'd appreciate any advice at the moment.
thanks in advance.
Code:
Private Function VerifyCredentials(ByVal emailAddress As String, _
ByVal password As String) As Boolean
'<sumamry>
' ||||| Declare Required Variables
' ||||| Access appSettings of Web.Config for Connection String (Constant)
'</summary>
' ||||| First is the Connection Object for an Access DB
Dim MyConn As SqlConnection = New SqlConnection("server=ARIA;database=dushkinmedia;Integrated Security=SSPI")
'<sumamry>
' ||||| Create a OleDb Command Object
' ||||| Pass in Stored procedure
' ||||| Set CommandType to Stored Procedure
'</summary>
' ||||| To Access a Stored Procedure in Access - Requires a Command Object
Dim MyCmd As New SqlCommand("sp_ValidateUser", MyConn)
' ||||| To Access a Stored Procedure in SQL Server - Requires a Command Object
MyCmd.CommandType = CommandType.StoredProcedure
' ||||| Create Parameter Objects for values passed in
Dim objParam1, objParam2 As SqlParameter
'<sumamry>
' ||||| Add the parameters to the parameters collection of the
' ||||| command object, and set their datatypes (OleDbType in this case)
'</summary>
objParam1 = MyCmd.Parameters.Add("@emailAddress", SqlDbType.VarChar)
objParam2 = MyCmd.Parameters.Add("@password", SqlDbType.VarChar)
'' ||||| Set the direction of the parameters...input, output, etc
objParam1.Direction = ParameterDirection.Input
objParam2.Direction = ParameterDirection.Input
'' ||||| Set the value(s) of the parameters to the passed in values
objParam1.Value = _emailAddress.Text
objParam2.Value = _password.Text
' ||||| Try, catch block!
Try
' ||||| Check if Connection to DB is already open, if not, then open a connection
If MyConn.State = ConnectionState.Closed Then
' ||||| DB not already Open...so open it
MyConn.Open()
End If
' ||||| Create OleDb Data Reader
Dim objReader As SqlDataReader
objReader = MyCmd.ExecuteReader(CommandBehavior.CloseConnection)
' ||||| Close the Reader and the Connection Closes with it
'PROBLEM HERE: NEVER ENTERS CONDITIONS OF WHILE LOOP
While (objReader.Read())
If CStr(objReader.GetValue(0)) <> "1" Then
Return False
'lblMessage.Text = "Invalid Login!"
Else
objReader.Close() ' ||||| Close the Connections & Reader
Return True
End If
End While
Catch ex As Exception
lbTEMP.Text = ex.ToString 'tmp errorhandling
Return False
'lblMessage.Text = "Error Connecting to Database!"
End Try
View 7 Replies
ADVERTISEMENT
Jul 23, 2005
SQL Server 2000I have a stored procedure that uses an extended SPROC to send an emailnotification to my customers when a document is distributed.However, the SPROC has an unexpected side effect.If I run it in its current incarnation, it only sends one email andthen exits. However, if I remove or comment out the block/* Set Job to processed */UPDATEtblJobsSETfldEmailProcessed = 1WHERE(fldJobID = @JobID)then it runs through the whole list as anticipated. Conceptually, itseems that the records in the cursor are changed if the underlyingtable is updated.Here is pseudo-code for what the SPROC does - whole SPROC below (withpart of the "WHERE" clause removed for readability). I haven'tincluded any table schemae but I don't think they're relevant.1. Open a cursor and fetch a list of all companies that need emailnotification for pending jobs.2. While records in the cursor...a) Format and send email from the cursorb) Write a record to the audit tablec) Update the jobs table for the current record3) Fetch next from cursorThere is an update trigger on the tblJobs table thus:CREATE TRIGGER "tblJobs_UTrig" ON dbo.tblJobs FOR UPDATE ASSET NOCOUNT ON/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tblDistributionMaster' */IF UPDATE(fldDistributionID)BEGINIF (SELECT COUNT(*) FROM inserted) !=(SELECT COUNT(*) FROM tblDistributionMaster, inserted WHERE(tblDistributionMaster.fldDistributionID = inserted.fldDistributionID))BEGINRAISERROR 44446 'The record can''t be added or changed.Referential integrity rules require a related record in table''tblDistributionMaster''.'ROLLBACK TRANSACTIONENDEND/* * PREVENT UPDATES IF DEPENDENT RECORDS IN 'tblPrinterJobs' */IF UPDATE(fldJobID)BEGINIF (SELECT COUNT(*) FROM deleted, tblPrinterJobs WHERE(deleted.fldJobID = tblPrinterJobs.fldJobID)) > 0BEGINRAISERROR 44446 'The record can''t be deleted orchanged. Since related records exist in table ''tblPrinterJobs'',referential integrity rules would be violated.'ROLLBACK TRANSACTIONENDENDI can't see that this is relevant - I think it's something to do withwhere I'm updating the fldEmailProcessed field, but I need to do thishere, as outside the loop the fldJobID might be lost.Sorry it's all such a mess. Hope someone can help!ThanksEdward/*Checks all Jobs that were set to Despatched more than 24 hours ago,selects those that the companies elected to get email notification, andsends them emails.*/CREATE PROCEDURE stpSendEmailNotificationASDECLARE @rc intDECLARE @JobID intDECLARE @CompanyID intDECLARE @DocumentNumber varchar(50)DECLARE @Email varchar(50)DECLARE @DocumentURL varchar(750)DECLARE @Dat varchar(20)DECLARE @Subject varchar(100)SET @Dat = LEFT((CONVERT(varchar, GETDATE(), 100)), 11)DECLARE MailList CURSOR FORSELECTtblJobs.fldJobID,tblJobs.fldDocumentNumber,tblCompany.fldEmail,tblCompany.fldCompanyID,tblJobHistory.fldDocumentURLFROMtblJobHistory INNER JOINtblJobs ON tblJobHistory.fldJobID = tblJobs.fldJobIDINNER JOINtblDistributionMaster ON tblJobHistory.fldDistributionID =tblDistributionMaster.fldDistributionID INNER JOINtblCompany ON tblJobHistory.fldCompanyID =tblCompany.fldCompanyIDWHERE(tblJobs.fldEmailProcessed = 0)OPEN MailListFETCH NEXT FROM MailList INTO@JobID,@DocumentNumber,@Email,@CompanyID,@DocumentURLWHILE @@FETCH_STATUS = 0BEGIN/* Format and send the email to the customer here */SET @Subject = N'Document Distribution No: ' + @DocumentNumber +N' - Date: ' + @Datexec @rc = master.dbo.xp_smtp_sendmail@FROM = N'techlib@myco.co.uk',@FROM_NAME = N'Edward Collier',@replyto = N'techlib@myco.co.uk',@TO = @Email,@CC = N'',@BCC = N'',@priority = N'NORMAL',@subject = @Subject,@type = N'text/plain',@message = @DocumentURL,@messagefile = N'',@attachment = N'',@attachments = N'',@codepage = 0,@server = N'12.34.5.67',@timeout = 10000select RC = @rc/* Write result to audit table */INSERT INTO tblEmailAudit(fldRCNo,fldEmail,fldDocumentNumber,fldDate,fldCompanyID)VALUES(@rc,@Email,@DocumentNumber,GETDATE(),@CompanyID)/* Set Job to processed */UPDATEtblJobsSETfldEmailProcessed = 1WHERE(fldJobID = @JobID)FETCH NEXT FROM MailList INTO@JobID,@DocumentNumber,@Email,@CompanyID,@DocumentURLENDCLOSE MailListDEALLOCATE MailListGO
View 1 Replies
View Related
Jan 14, 2006
I am using C# and ASP.NET 2.0, with SQL Server 2000. In my database I have a table that is similar to the following:
WebpageId WebpageAddress Handler 1 /company/about ~/about.aspx 2 /blog ~/blog.aspx
As you can guess, one of my queries will be a SELECT command where WebpageAddress = @address.
The hiccup I have is with a friendly URL such as the following:
/blog/2005/10/6
The friendly URLs have no extension, so I cannot immediately "pick out" the extension. (If the address were /blog.aspx/2005/10/6, then things would be simpler.)
What I am doing at present is using a loop in C# where I first perform a query with that full address. If no match is found, I trim the address back to the last slash (/blog/2005/10) and perform another query. If no match is found, I trim the address (/blog/2005) and perform another query. Again, no match is found, so I trim the address again (/blog) and perform yet another query. This time, a match is found, in which case the URL rewriting looks vaguely like this:
~/blog.aspx?parameters=2005/10/6
While this works fine, these friendly URLs require hitting the database up to four or five times. My question is, can this looping logic be moved to SQL Server?
My SQL knowledge is extremely basic, so I am just looking for someone to point me in the right direction. If it is not possible, I'd love to know now rather than wasting hours trying. If it is possible, I've love to know the keyword or technique involved, so that I can Google for the full answer.
View 1 Replies
View Related
Sep 21, 2005
Hi. It seems to be very simple, actually, but I don't know if it isfeasible in TSQL. I have a sproc which gathers in one place many callsto different other sprocs, all of them taking a 'StoreGroupe'parameter. I would like to add a case where if the call has NOStoreGroupe parameter, the sproc should LOOP thru all records in tableStoreGroupeTable, read the column StoreCode, and pass that value as aparam to the other sprocs, as in:CREATE PROCEDURE MySproc(@StoreGroupe nvarchar(6) = NULL)ASif (@StoreGroupe is not null)BeginExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............EndElseBeginA 'Group Code' has NOT been specifiedI want to take all the StoreGroups in tableStoreGroupeTable, in turn.I would like to do SOMETHING LIKE THIS:Do While not [StoreGroupeTable].EOFRead [Code] from [StoreGroupeTable]Set @StoreGroupe = The value I just readExec _Sproc1 @StoreGroupeExec _Sproc2 @StoreGroupeExec _Sproc3 @StoreGroupeExec _Sproc4 @StoreGroupe...............LoopEndGOIs that feasible in a sproc, or do I have to do this in the client(ADO) ?Thanks a lot.Alex.
View 4 Replies
View Related
Jan 17, 2008
Hi friends, I've been stumped on this for almost a week now. Everything works in the stored procedure code below except for the 'INSERT INTO @Uppdates' block of code. I have a SQL Analyzer test driver and when the code gets to the SELECT statement below the INSERT INTO @Updates line the value of the select line is displayed on the screen and nothing gets written to @Updates. I hope I'm being clear about this. Any ideas? IF @Edit=1 AND LEN(@Changes) > 0
BEGIN
--Split and parse changes
DECLARE @curRec int, @nxtRec int, @Record varchar(8000), @TNum int, @TNam varchar(50), @PDesc varchar(512), @PChk varchar(8), @SNum varchar(12), @NScr varchar(10), @OScr varchar(10),
@curField int, @nxtField int, @curSRec int, @nxtSRec int, @subRec varchar(8000), @curSField int, @nxtSField int
DECLARE @NewProj table (
ProjectID int,
SchoolNumber varchar(20),
ArtTeacherNumber int,
TeacherNumber int,
TeacherName varchar(40),
ProjectDescription varchar(512) NULL,
[Checksum] varchar(20) NULL)
DECLARE @Updates table (
ProjectID int,
StudentNumber varchar(12),
NewScore varchar(10) NULL,
OldScore varchar(10) NULL)
SET @curRec = 1
WHILE @curRec IS NOT NULL
BEGIN
SET @nxtRec = NULLIF(CHARINDEX(CHAR(1), @Changes, @curRec), 0)
SET @Record = SUBSTRING(@Changes, @curRec, ISNULL(@nxtRec,8000)-@curRec) --Extract a class record
SET @curField = 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNum = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Number
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNam = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Name
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @PDesc = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Project Description
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)-- Step over existing checksum
SET @PChk = RIGHT('0000000' + dbo.int2base(Checksum(@PDesc),16),8)-- Calculate new checksum based on project description that may have been changed.
SET @curField = @nxtField + 1
INSERT INTO @NewProj (ProjectID, SchoolNumber, ArtTeacherNumber, TeacherNumber, TeacherName, ProjectDescription, [Checksum])
SELECT DISTINCT Students.ProjectID, @SchoolNumber, @ArtTeacherNumber, @TNum, @TNam, @PDesc, @PChk
FROM @Students Students
WHERE Students.SchoolNumber=@SchoolNumber AND Students.TeacherNumber=@TNum
SET @curSRec = 1
WHILE @curSRec IS NOT NULL
BEGIN
SET @nxtSRec = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)
SET @subRec = SUBSTRING(@Record, @curField, ISNULL(@nxtSRec,8000)-@curField) -- Extract a score sub record. Consists of Student Number, new Score, old Score.
SET @curSField = 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @SNum = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract Student Number
SET @curSField = @nxtSField + 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @NScr = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract new Score
SET @curSField = @nxtSField + 1
IF @curSField > LEN(@subRec)
SET @Oscr = NULL-- If no Old Score specified
ELSE
BEGIN
SET @nxtSField = LEN(@subRec) + 1
SET @OScr = SUBSTRING(@subRec, @CurSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract old Score
END
-- Check for errors
IF ISNUMERIC(@SNum) = 0 OR @NScr IS NULL OR LEN(ISNULL(@PChk,0)) <> 8
BEGIN
SET @UpdateErr = 1
BREAK
END
-- Update the updates table and find ProjectID from existing data table
INSERT INTO @Updates (ProjectID, StudentNumber, NewScore, OldScore)
SELECT DISTINCT Students.ProjectID, @SNum, @NScr, @OScr
FROM @Students Students
WHERE Students.StudentNumber=@SNum
SET @curField = @nxtSRec + 1
SET @curSRec = @nxtSRec + 1
select * from @Updates
END
IF @UpdateErr = 1
BEGIN
BREAK
END
SET @curRec = @nxtRec + 1
END
Thanks in advance for looking at this,
View 3 Replies
View Related
Dec 17, 2007
Hi All,
This is the code for calculating a formula field in Crystal Reports.
I want to implement the same in Sql Server Reporting Services..
But it doesn't have the feature of For Loop....
The strings started with @ are formula fields....
Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR;
for YR := 1 to {@CalcFiscalAge} step 1 do
(
IF YR = {@CalcLifeCode} +1 then
locFactor1 := 1;
exit for;
IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN
HALF_YEAR := 2
ELSE
HALF_YEAR := 1 ;
LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4);
MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4);
IF MACR >= LINEAR then
DEP := MACR
ELSE
DEP := LINEAR;
locFactor1 := locFactor1 + DEP ;
REM := 1 - locFactor1;
locFactor := locFactor1;
);
Thanks in advance
Regards,
View 3 Replies
View Related
Mar 5, 2015
I'm trying to do something like this:
Loop through #Temp_1
-Execute Sproc_ABC passing in #Temp_1.Field_TUV as parameter
-Store result set of Sproc_ABC into #Temp_2
-Update #Temp_1 SET #Temp_1.Field_XYZ= #Temp_2.Field_XYZ
End Loop
It appears scary from a performance standpoint, but I'm not sure there's a way around it. I have little experience with loops and cursors in SQL. What would such code look like? And is there a preferable way (assuming I have to call Sproc_ABC using Field_TUV to get the new value for Field_XYZ?
View 2 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
May 28, 2010
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies
View Related
Feb 22, 2006
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
View 6 Replies
View Related
Mar 3, 2006
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.
I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.
Any solution to this? anyone experienced anything similar
View 1 Replies
View Related
Jul 8, 2006
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
View 18 Replies
View Related
Aug 4, 2000
I am just a sql beginner. I wonder if it is possible to jump to table B to continue searching while I am still doing search on table A. Thanks
J
View 4 Replies
View Related
Oct 9, 2000
Can anyone tell me where I can learn more about DTS.
As I have to export data from a .CSV(comma separated file) into SQL Server.
View 5 Replies
View Related
Sep 12, 2007
I have MS SQL 2000 and 2005 Express install on my computer. I would like to know where I can get some good beginner's training and I also need to know how to run sql scripts and import database to both.
This is so easy to do in mysql.
Thanks
View 1 Replies
View Related
Aug 19, 2005
I have a SQL server that my web host has provided but I don't know who to set it up! I'm currently have an MS Access database on my web site and I want to change it to MSSQL using the Access upsizing wizards, can anyone talk me through it?
View 2 Replies
View Related
Dec 8, 2006
hi there. this is my first post on this forum. im new to SQLServer so please go easy on me. :)
i am trying desperately to call a stored procedure from within another stored procedure. if i post my code below could somebody tell me where i am going wrong. the SP i have written is executing OK but the OUTPUT parameter Id_User is coming back as null, whereas it should be giving my a value there. i have tried changing the line 'EXEC sp_User_I' for 'INSERT INTO @User_Id EXEC sp_User_I' but this wont compile.
CREATE PROCEDURE sp_Account_I]
@Username varchar(16),
@Password varchar(88),
@Surname varchar(32),
@DateBirth datetime,
@Email varchar(64),
@Id_Account int OUTPUT,
@Id_User int OUTPUT
AS
DECLARE @ErrorCode int;
BEGIN
SET NOCOUNT ON;
EXEC sp_User_I
@Username,
@Password,
@FirstName,
@Surname,
@DateBirth,
@Email,
@Id_Country,
@Id_User
...do some other stuff here for the account...
END
View 4 Replies
View Related
Mar 19, 2008
Hi all,
I've been banging my head against this issue, and I haven't managed to find a solution. I was hoping that maybe somebody here has done something similar.
I am trying to get the latest available pricing from a Rate table, which is based on the customer ID and the provided date from another table (Table1).
This is what the code looks like, essentially:
select
table1.*
,table2.*
,table3.Rate
from table1
left outer join table2 on table1.Id = table2.Id
left outer join (
Select Top 1 Rate from RateTable
Where RateTable.date < table1.date
order by RateTable.date desc
) as table3 on table3.custId = table1.custId
I understood that table1.date can't be explicitly passed into the nested join within table3, but does anybody know a work around that can achieve the above example (the environment is SQL server 2000 and inside a view not on in a stored proc)?
Thanks in advance for any tips or workaround.
View 1 Replies
View Related
Jul 23, 2005
Hi,I am completely new to the BCP utility and fairly new to SQL ServerI am learning from a book and I am trying the following example (the serverI'm learning on is called contractor and a password has not been give to thesa)bcp pubs..authors out authors.txt -C -r -t, -U sa -P -S contractorWhen I run this in Query Analyser i get the error message..Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '.'.I have tried puuting quotes around the database and the table name asfollows-bcp "pubs..authors" out authors.txt -C -r -t, -U sa -P -S contractorand get the errorServer: Msg 179, Level 15, State 1, Line 1Cannot use the OUTPUT option when passing a constant to a stored procedure.Please can anybody help me to get this first bit working?Thanks in anticipation.
View 2 Replies
View Related
Nov 15, 2005
What is the best way to go about learning SQL? Where should I start?
View 20 Replies
View Related
Sep 4, 2006
How do i learn SQL server 2000...I'm new to development, and need to get my basics right before i proceed further.
Do give me your feedback / learning tips.
View 1 Replies
View Related
Apr 19, 2008
Hi
is there any SQL server book or online meterial for beginners?? i've checked msdn its seems not helpful at beginner level.like issues for installing and first time configuring SQL Server. and installing its clients etc.
View 1 Replies
View Related
Jan 23, 2006
hello all i'm new in dot net and as every one beginning in something i have some problems hope u guys can help me my first question is really easy i'm feeling silly to ask such a question i made database in sql server 2005 and i made a table and columns but i cant insert data into the table i dont know how to fill the table with data? my second question is can i convert sql server 2000 database to 2005 if i can how i cando that? that's all for now hope u can really help me thanks in advance
View 4 Replies
View Related
Aug 31, 2006
My company needs a database. My first thought was to do it in Access, because it's available! But I wanted to check that that was smart, what its limitations are, when it's better to move to bigger / more expensive software, etc.
It's probably going to have several hundred thousand records in and will grow by more than a hundred thousand every year. Is there a size limit?
Not that many fields, though - it's not that complicated a database.
Many thanks in advance for any help :beer:
View 4 Replies
View Related
Feb 7, 2007
Hi!
I'm trying to make a dictionary.
WordEng(#id,word);
WordIta(#id, word);
tables with words in Italian and English.
Connect1(#id,id1,id2); connecting word form Eng with Ita translation.
Connect2(#id,id1,id2); same for Italian.
Is this correct? 3. normalisation? Will this work?
View 5 Replies
View Related
Dec 8, 2006
if i am creating an SP then i only specify output parameters if the SP is to return a single set of values. is that correct? if my SP is returning multiple rows from a SELECT statement then i would not specify any parameters on the SP. can somebody clarify this for me. thanks!
View 2 Replies
View Related
Aug 17, 2007
Hi guys,
I am new to database programming... i know the answer is somewhere in the past forums and i unfortunately i can't locate it... I have this problem i hope you can help me.
I am trying to insert values into table hosp_wareitem and one of it is the itemid where in i would like to get the item id of an existing medicine... (i hope you can understand me guys) here is my query.. right now im puzzled and i know the answer is already there.. i just cant see it...
set xact_abort on
declare @id as integer
begin transaction
select @id (select medicines_id from
hosp_medicines where medicines_id = medicines_id)
insert into Hosp_WareItem
(ItemID,
Department_ID,
SalesGL_Code,
COSGL_Code,
InventoryGL_Code,
ExpenseGL_Code,
ReadersFeeGL_Code,
CreateBy,
UpdateBy,
DeleteBy,
CreateDate,
UpdateDate,
DeleteDate)
values
(@ID,
'1',
'AAA-111',
'AAA-000',
'AAA-112',
'',
'',
'0',
'',
'',
'1/1/1900 12:00:00 AM',
'1/1/1900 12:00:00 AM',
'')
Update Hosp_Counter set Counter_ID = @ID
where Table_Description = 'wareitem'
commit transaction
it returns an error
(1 row(s) affected)
(24 row(s) affected)
Server: Msg 515, Level 16, State 2, Line 6
Cannot insert the value NULL into column 'ItemID', table 'Medix_Hospital.dbo.Hosp_wareitem'; column does not allow nulls. INSERT fails.
it tries to insert a null value in itemid because it cant get the value of the medicine_id.
any help is much appreciated!
puzzled
joel
View 2 Replies
View Related
Oct 16, 2007
Hi all,
This looks like a great place for SQL Server support. I hope you don't mind a few n00b questions.
I'm a sysop whose main experience is with Windows XP and SBS 2003. I'm also reasonably familiar with Access 97/2000, having programmed a few small databases. I've recently acquired a client who is running the premium version of SBS, which includes SQL Server 2005. This means I have to manage this thing. This *huge* thing. So my first question is: Where can I find information to get me started, first from a sysop perspective, and later from a Access programmer perspective. What is your experience?
Then two current issues. One is that for some reason SQL Server 2005 is using about 2 gigabytes of memory out of the box. Is this normal? Second, I can't for the life of me find the backup system. I can see backups being made of the AdventureWorks database (at 3:00), but even after 2 hours of searching I can't change the schedule. I knew how to do this in SQL Server 2000, but this version is very different.
I'd much rather figure these things out on my own, but this product is just too complicated to learn in a few days, and the memory usage issue is critical.
I'd be much obliged for any help you can offer.
View 3 Replies
View Related
Jan 9, 2008
Please add me for real-time ask for help.
My MSN: ices_ailee@hotmail.com
My Yahoo: ices_ailee@yahoo.com.sg
Please add my msn or yahoo account, easy for me to enquiry straight forward ...thx yo. I am a junior and beginner with SQL Server.
thx yo!
chia ling
View 3 Replies
View Related
Jul 23, 2005
Hi,I'm finishing up a beginning SQL class where we learned on an Oracledatabase and the transition to working on SQL Server is easy. The next moreadvanced course will be in PL/SQL, but I know I will be working on SQLServer in the workplace, so my question is if I should take this course.Will I benefit from the basic philosophies that will be covered, or will itjust make a transition for me more difficult? Will it be partly a waste oftime and money and I'd be better served getting a book and self teachingmyself? I know that in a greater sense learning something isn't necessarilya waste, but I mean from the perspective of my goal of being able to use SqlServer, will this course be useful?thanks.
View 10 Replies
View Related
May 22, 2007
I just installed SQL Server 2005, Office 2007, and the add-in, but when I check services.msc, I don't see the SQL Server Analysis Services. Does anyone know how I can get this to load? Also, when I run the server configuration utility in the DM add-in folder, do you know what I should put in the server name to get it to run off my hard disk and not a server? local host doesn't seem to work. Thanks in advance!
View 11 Replies
View Related