hello,..
please take a look and tell me what's wrong.
*** declare @myfine money
select @myfine=sum(
select fp.total_fine from final_payment fp,inserted i
where fp.member_no=i.member_no and fp.return_complete=0)***
i have final_payment table and everything.I'm creating this syntax in an insert trigger on my final_payment table. will @myfine value be the sum of total_fine where the conditions are true?please help
truly,
JC
Hey Guys, I am creating a scalar function for the first time. I have googled, ck'd your FAQ's, looked 2 text books but cannot find the answer to my questions.
Is the IF statement used in the Scalar function like it is in the stored procedure. In other words does it have to return a Boolean? I want to perform a loop after I call the function, do I have to use a Cursor? Or can I code it like a VB function with a FOR/DO While? Also I can call this UDF from my Stored Proc, right?
Here is a quick synopsis of what I have been assigned to do:
--- Calculate Run Dates
--(start from Previous Saturday subtract 41 days
-- if the 41st day does not fall on a Saturday (day = 7)
-- keep subtracting days until Day=Saturday. Add 6 days to make
-- END date the following Friday)
Thanx,
Trudye
I use the following code to fill the missing leading zero from datepart sys function. Is there another way simple to get the leading zero if month < 10 and/or if day < 9?
-D
--++++++++++++++++++++++++++++++++
declare @currentYYYYMMDD char(8)
declare @currentYYYY char(4), @currentMM varchar(2), @currentDD varchar(2)
select @currentYYYY = cast(datepart(yyyy, getdate()) as char(4))
select @currentMM = cast(datepart(mm, getdate()) as varchar(2))
select @currentDD = cast(datepart(dd, getdate()) as varchar(2))
print '@currentYYYY: ' + @currentYYYY + ' @currentMM:' + @currentMM + ' @currentDD:' + @currentDD
select @currentMM = case when @currentMM < 10 then '0' + @currentMM
else @currentMM end
select @currentDD = case when @currentDD < 10 then '0' + @currentDD
else @currentDD end
print '@currentYYYY: ' + @currentYYYY + ' @currentMM:' + @currentMM + ' @currentDD:' + @currentDD
select @currentYYYYMMDD = @currentYYYY + @currentMM + @currentDD
print '@currentYYYYMMDD: ' + @currentYYYYMMDD
Hi to all,
Is It possible to use dynamic qyery inside a function in sql server.
For Example:
Create function fn_Test
Returns Table
As
Return sp_ExecuteSql 'SELECT * FROM EMP'
Like this.
With regards
Amjath
All,
I need to use ISNULL function in a Conditional split transformation. Data will be split based on the ISNULL function. ISNULL( col) can get all the null records, How to get the not null records? ISNULL(col) = €śfalse€? doesn€™t work.
Thanks in Advance
Hi all!
I'm trying to use a UDF that returns a table, but I'm not sure of the syntax to invoke it. I've found examples in BOL and on-line like the following:
SELECT * FROM dbo.fn_MyTableFunc( 123.09, 'MyID' )
But I need the input parameter to be obtained from another table. For a very simplistic example, I've got 4 tables (and yes, I know that I can get the results I want for this example without using a UDF, but humor me):
CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50))
CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50))
CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50))
CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int)
GO
INSERT INTO tUser VALUES (111, 'Me')
INSERT INTO tAcctGroup VALUES (1, 'NY')
INSERT INTO tAcct VALUES (11, 1, 'New York City')
INSERT INTO tAcct VALUES (12, 1, 'Syracuse')
INSERT INTO tAcctGroup VALUES (2, 'GA')
INSERT INTO tAcct VALUES (21, 2, 'Atlanta')
INSERT INTO tAcct VALUES (22, 2, 'Savannah')
INSERT INTO tAcct VALUES (23, 2, 'Augusta')
INSERT INTO tAcctGroup VALUES (3, 'TX')
INSERT INTO tAcct VALUES (31, 3, 'Dallas')
INSERT INTO tAcct VALUES (32, 3, 'Houston')
INSERT INTO tAcct VALUES (33, 3, 'El Paso')
INSERT INTO tAcct VALUES (34, 3, 'San Antonio')
INSERT INTO tAcctGroup VALUES (4, 'CA')
INSERT INTO tAcct VALUES (41, 4, 'Los Angeles')
INSERT INTO tAcct VALUES (42, 4, 'San Francisco')
INSERT INTO tMapUserToGroup VALUES (111,2)
INSERT INTO tMapUserToGroup VALUES (111,4)
GO
CREATE FUNCTION dbo.ufnGetAcctList(@AcctGroupID int) RETURNS @tAcct table (AcctID int, AcctName varchar(50))
AS
BEGIN
INSERT INTO @tAcct
SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID = @AcctGroupID
RETURN
END
GO
I know that I can do:
SELECT * FROM TestDB.dbo.ufnGetAcctList(4)
But I want the equivalent of:
SELECT AcctID, AcctName FROM tAcct
WHERE AcctGroupID IN (SELECT AcctGroupID FROM tMapUserToGroup WHERE UserID = 111)
Which uses tMapUserToGroup to obtain the AcctGroupID to pass into the function. The results would be:
AcctID AcctName
-----------------------------
21 Atlanta
22 Savannah
23 Augusta
41 Los Angeles
42 San Francisco
Any thoughts?
Thanks in advance for your help.
Cat
I have code
Function GetDealCount(reportItems)
return iif(IsNothing(reportItems!txtDetailCountRows.Value), 0, reportItems!txtDetailCountRows.Value)
End Function
Function GetSumNotionalAmount(reportItems)
return iif(IsNothing(reportItems!txtDealSumNotionalAmount.Value), 0, reportItems!txtDealSumNotionalAmount.Value)
End Function
That I am calling from a textboxes in the page header
= Code.GetDealCount(ReportItems) & " Deal(s)"
also
= Parameters!BaseCurrency.Value + " " + Format(Code.GetSumNotionalAmount(ReportItems),"N2").ToString().Replace(",","'")
When I preview the report in VS.NET I get values showing.
When I deploy the report I just get #Error showing.
Also this report used to work fine in RS2000
Does anyone know the cause of this issue?
is there a way to create
"CREATE TABLE @table ( blah blah blah...."
from a table value function?
My error is that 'Name tr is not declared' tr.Rollback() I tried moving the 'Dim tr As SqlTransaction' outside the try but then I get 'Variable tr is used before it si assinged a value'.
What is the correct way? Try conn.Open() Dim tr As SqlTransaction tr = conn.BeginTransaction() cmdInsert1.Transaction = tr cmdInsert1.ExecuteNonQuery() cmdInsert2.Transaction = tr cmdInsert2.ExecuteNonQuery() cmdInsert3.Transaction = tr cmdInsert3.ExecuteNonQuery() tr.Commit() Catch objException As SqlException tr.Rollback() Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next Finally conn.Close() End Try
I have the below procedure that will not work- I must be losing my mind, this is not that difficult - mental roadblock for me.
Using SQL Server 2000 to create SP being called by ASP.Net with C# code behind
stored procedure only returns if input exactly matches L_Name
PROCEDURE newdawn.LinkNameLIKESearch @L_Name nvarchar(100)AS SELECT [L_Name], [L_ID], [C_ID], [L_Enabled], [L_Rank], [L_URL] FROM tblContractorLinkInfo WHERE L_Name LIKE @L_Name RETURN
I tried: WHERE L_Name LIKE ' % L_Name % ' no luck. What am I missing?
Thank you
SP below is not work.It gives null for @kaysay
I used parametric table name. Is this the problem?
My aim to calculate record count of a table
Thanks.
CREATE PROCEDURE Tablodaki_Kayit_Sayisi(@TABLO varchar(30))AS
Declare @kaysay bigintDeclare @SQLString nvarchar(100)Declare @Param nvarchar(100)
Set @SQLString = N'Select @kaysayOUT = count(BELGE) From ' + @TABLOSet @Param = N'@kaysayOUT bigint'
Execute sp_executesql@SQLString,@Param,@kaysayOUT = @kaysay
Select @kaysay
Return
GO
SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle,C.CategoryTitle
FROM HomePageImage H
JOIN shortlist SL on H.StoryId = SL.id
(INNER JOIN category C on H.CategoryId = C.CategoryId)
order by date DESC
Hello,
I am trying to query with my stored procedure. I am getting comma separated list as input parameter (which is VARCHAR) .
The table column, which I have to compare with input parameter value , is in INTEGER datatype.
So when , I compare Like as follows:-
" AND TABLE1.EMPID IN (@INPUTPARAMETERLIST)"
I am getting error. "Syntax error converting the varchar value '34,343' to a column of data type int."
Could anybody help me , to solve this ?
Thanks !
Nicol
I have a textbox and a checkbox on a form and I'd like to add both values to a db. The textbox value gets inserted fine but I'm having trouble with the checkbox. Any ideas would be greatly appreciated.
Thanks
Form1 is the column in my db.
SqlCommand myCmd = new SqlCommand();
myCmd.Parameters.Add("@ClientCode",SqlDbType.VarChar).Value = TextBox2.Text;
myCmd.CommandText = "UPDATE table SET Form1 = 'Yes' WHERE ClientCode = @ClientCode";
sqlConnection1.Open();
myCmd.Connection = sqlConnection1;
if( CheckBox1.Checked == true)
{
myCmd.ExecuteNonQuery();
}
sqlConnection1.Close();
Hi,
Beside working right from the server how else someone can perform the SQL admistration job, I guess my question is how do most SQL DBA perform their administration without going to the server directly. Anyone --- can help please??
Thanks in advance.
Declare @StartDate datetime
Declare @EndDate Datetime
Set @StartDate = dateadd(day, datediff(day, 0, getdate()), 0)
Set @EndDate = getdate()
The job runs at 11:30 pm so I want the start date to be the same but the time to be equal to 00:00:00.0 When I run the getdate does it also return a time stamp?
The Yak Village Idiot
I had a problem where some users were experiencing timeouts when trying to add a single record to a table with 2.3 million records.
It's not a very wide table; only 10 columns and the biggest column in varchar 500. The rest are guid, datetime, tinyint...
There is also an old VB app that inserts about 3000 records a day into this table during office hours while users occasionally try and insert a record into this table.
Something said to me that the problem could be indexes but I wasn't quite sure because I though indexes only have an impact on select, delete & update. And not particulary on insert. But I checked it out anyway and noticed that the 3 indexes (1 column PK, 1 column Clustered & 1 column non-clustered) weren't padded. So I changed that (Fill Factor 95) and the problem has gone away. But why? I thought the insert would just have appended it to the end of the index before I made this change? Why would that time out?
I have the following queries. The first returns the 'Unknown' row, the second works the way I would expect. Are my expectations wrong? Can someone describe for me what is going on?
Thanks
Code Snippet
select *
FROM
SynonymComFinancialCategory b
LEFT JOIN TT_FinancialCategory a
ON
a.FinanceGroup = b.FinanceGroup
AND a.FinanceCode = b.FinanceCode
AND a.Finance = b.Finance
AND b.Finance <> 'Unknown'
WHERE
a.FinanceGroup IS NULL
AND a.FinanceCode IS NULL
AND a.Finance IS NULL
Code Snippet
select *
FROM
SynonymComFinancialCategory b
LEFT JOIN TT_FinancialCategory a
ON
a.FinanceGroup = b.FinanceGroup
AND a.FinanceCode = b.FinanceCode
AND a.Finance = b.Finance
WHERE
a.FinanceGroup IS NULL
AND a.FinanceCode IS NULL
AND a.Finance IS NULL
AND b.Finance <> 'Unknown'
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
View 1 Replies View RelatedHello,I created a formview in a web page. The data are in a sql server express database.With this form, I can to create a new data, I delete it but I can't to modify the data.The app_data folder is ready to write data; the datakeynames element in formview web control declared. I replace the automated query created by VS 2005 by a strored procedure to see if the problem solved.The problem is the same with an update query or a update stored procedure...Have you an idea, please.Than you for your help.Regards.
View 1 Replies View RelatedIm working with a detailsview and when I try to edit something and then update, the changes are not saved.
I have 2 tables ("[etpi.admin].Ocorrencias" and "[etpi.admin].SMS") that store the data that Im trying to change. Since Im having problems with the name of tables, Im coding it manually, using SQL server management studio and VWD.
I believe my code can be wrong (Im new to vwd and C# world), so here it is:
UpdateCommand="UPDATE [etpi.admin].Ocorrencias SET [Status_Ocor] = @Status_Ocor, [Percentual] = @Percentual, [IDPriori] = @IDPriori, [Abertura] = @Abertura, [IDTecRes] = @IDTecRes, [Area] = @Area, [CodEquip] = @CodEquip, [Descricao] = @Descricao, [Destinatario] = @Destinatario, [Data_Implanta] = @Data_Implanta WHERE [etpi.admin].Ocorrencias.IDOcorre = @IDOcorre
UPDATE [etpi.admin].SMS SET [idSMS] = @idSMSWHERE [etpi.admin].SMS.IDOcorre = @IDOcorre">
<UpdateParameters><asp:Parameter Name="idSMS" Type="Int32" /><asp:Parameter Name="Status_Ocor" Type="String" /><asp:Parameter Name="Percentual" Type="Int32" /><asp:Parameter Name="IDPriori" Type="Int32" /><asp:Parameter Name="Abertura" Type="DateTime" /><asp:Parameter Name="IDTecRes" Type="Int32" /><asp:Parameter Name="Area" Type="String" /><asp:Parameter Name="CodEquip" Type="Int32" /><asp:Parameter Name="Descricao" Type="String" /><asp:Parameter Name="Destinatario" Type="String" /><asp:Parameter Name="Data_Implanta" Type="DateTime" /><asp:Parameter Name="IDOcorre" Type="Int32" /></UpdateParameters>
Thx.
My rollback does not work
In my SP I want any of cmdS,cmdS2,cmdS3,cmdS4 produces error Rollback must be executed for all of cmdS,cmdS2,cmdS3,cmdS4 . I tested for error producing situation but no rollbak occured.
How can I solve this problem. Thanks.
Below is my SP .
..........
..........
..........
begin transaction
..........
..........If Len(ltrim(rtrim(@cmdS)))>0
execute(@cmdS)
If Len(ltrim(rtrim(@cmdS2)))>0
execute(@cmdS2) If Len(ltrim(rtrim(@cmdS3)))>0
execute(@cmdS3)
If Len(ltrim(rtrim(@cmdS4)))>0
execute(@cmdS4)
If Len(ltrim(rtrim(@cmdS)))>0 or Len(ltrim(rtrim(@cmdS2)))>0 or Len(ltrim(rtrim(@cmdS3)))>0 or Len(ltrim(rtrim(@cmdS4)))>0
Beginif @@ERROR <>0
begin
rollback transaction
set @Hata = 'Error !'
end
Else
Beginset @Hata = 'Sucessfully executed :)' End
End
commit transaction
RETURN
I can test it in query builder but when i preview the page and go to do the search i get nothing
Here is my statement
SELECT Employees.Last_Name, Employees.First_Name, Job_Transaction.Name, Seq_Descript.Seq_DescriptionFROM Call_List INNER JOIN Call_Group ON Call_List.Group_ID = Call_Group.Group_ID AND Call_List.Group_ID = Call_Group.Group_ID INNER JOIN Employees ON Call_List.Clock = Employees.Clock INNER JOIN Job_Transaction ON Call_Group.Group_ID = Job_Transaction.Group_ID INNER JOIN Seq_Descript ON Call_List.Sequence = Seq_Descript.SequenceWHERE (Call_List.Clock = @Clock)
Mike
Hi all.I have used a SqlDataSource in my page with this delete command:DELETE FROM tblPersonnel WHERE (ID = @original_ID)and the "OldValueParameterFormatSring" property of the datasource is "original_{0}".and i also have a GridView and a button for delete in rows.(it's CommandName is "Delete"). But when i try to delete a record, the record does not get deleted and this button only makes a PostBack on the page! Why doesn't it work?
Thanks in advance.
hi all, i have created a gridview with the select,delete and insert commands working properly. but the update command does not work. when i edit a column and click the update button, it generates no errors but does not save the changes. it just brings back the original values. i dont know wats missing. can anyone help me?
this is part of my code:UpdateCommand="UPDATE [test101] SET Surname=@Surname, Names=@Names,Registration=@Registration,[Course code]=@Course_code,Grade=@Grade WHERE ID=@ID1 "<UpdateParameters> <asp:Parameter Name="Surname" Type=String /> <asp:Parameter Name="Names" Type=String /> <asp:Parameter Name="Registration" Type=String /> <asp:Parameter Name="Course_code" Type=String /> <asp:Parameter Name="Grade" Type=Int32 /> <asp:Parameter Name="ID1" Type=Int32 /> </UpdateParameters>
Hi,I am having trouble with a WHERE clause:WHERE (([A] = @A) AND ([B] >= @B) AND (([C] < [D])) OR ([C] = 0 AND [D] = 0)) It's meant to only select data where A = @A, and B is more than or equal to @B, and one of the next two are true: C is less than D or C and D are both 0 Thanks,Jon
View 3 Replies View RelatedI am trying to get the id of last entered record and I used select ID command but it doesn't give me the ID of last record enteredProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click
Dim name, address, nice As String
Dim sConnString, sSQL, sID As String
Dim u As MembershipUsername = Me.name.Textaddress = Me.address.Text
u = Membership.GetUser(User.Identity.Name)
nice = u.ToString()sSQL = "INSERT into test ([Address],[Name], [username]) values ('" & _
address & "', '" & _name & "', '" & _
nice & "'); SELECT ID FROM test WHERE (ID = SCOPE_IDENTITY())"
MsgBox(sID)
MsgBox(sSQL)
sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString()Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection)
MyConnection.Open()
MyCmd.ExecuteNonQuery()
MyConnection.Close()
End UsingResponse.Redirect("transferred.aspx?value=2")
End Sub
End Class
I am using the below WHERE clause and it will not filter out records where Companies.CompanyEmail = b@c.com
WHERE (Companies.CompanyEmail <> 'a@a.com') this does not work, is the @ causing a problem? also tried 'a@a.com%' with no success.
WHERE (tblCompanies.C_CompanyEmail <> 'none') this works fine.
Thank you
For the life of me, I cant get this thing to work.. any insight would be greatly appreciated. What I'm trying to do is simply get the RealName field from my aspnetdb.Here the code. SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=aspnetdb;Integrated Security=SSPI"); SqlDataAdapter a = new SqlDataAdapter ("select RealName from dbo.aspnet_Users where UserName='Dude';", conn); DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { getusersrealname.Text = (dr[0].ToString());
View 4 Replies View Relatedcan anyone explain why, when i do a group by clause on the following data....it has no effect?
Part SubPart Qty
120887 66743 83
120887 66743 100
120887 667443 25
553212 122987 119
553212 122987 67
here's my select statement:
select part, subpart sum(qty) from partdata group by part,subpart,qty
the resulting data looks identical to the original.
what i was expecting was a return of just two lines:
Part SubPart Qty
120887 66743 208
553212 122987 186
I am kicking myself, but cannot seem to get a simple stored procedure to return ANY records. I know it's soming that has to do with the Joins. Don't know what. I ran query analyzer and passed it a valid city and nothing was returned, but no errors either. If I run the query without any parameters, all the records are retrieved fine. ANY IDEAS?
CREATE PROCEDURE spUnitsbyCity
@city varchar
AS
SELECT A.unitcity, A.unitid, B.radioip, B.radiomac, C.videoserverip
FROM tbl_units as A
INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
WHERE A.unitcity = @city
GO
Hi
For some reason my stored procedure is returning no results, when I
know it should be. I am trying to take a textbox someone has
typed into and returning all names from the database which include that
string. My SQL statement is
ALTER PROCEDURE getLecturerNames
(
@lName as varchar(20)
)
AS
SELECT * FROM Lecturers
WHERE lName LIKE '%@lName%'
When I step into the procedure, the @lName parameter shows lName =
'ike' (what I typed to search) but no results are returned.
However when I change the procedures last line to :
WHERE lName LIKE '%ike%'
2 records are returned. Is SQL server adding the quotation marks causing the problem, or if not what is?
hi.
I am trying to use parameters in an sql statement, that runs inside a loop. every cycle I have to change the parameters values, and I'm not sure if I do it right.
I'm using the DefaultValue property like this:
SqlDataSource1.UpdateParameters["Text"].DefaultValue = txt;
is it the right way? (I had some errors when I did this, something like "value can not be truncated ")
thanks