December 1, 2014 at 9:19 am
I am trying to alter a procedure..if i edit some record in frontend it will execute this procedure and update the record in tblcal
In tblcal i have 4 columns one is UID,Desc,Caldate and avbl here i am trying to add another column called loginname
in the procedure how can i add the below select statement after begin i can declare @loginname varchar(10) and how can i assign the selected value in the update tblcal
select loginname from tbluser a inner join tblcal b on a.ID = b.EveID
ALTER PROCEDURE [dbo].[UpdateMoose]
(@UID varchar(10),
@Desc varchar(200),
@Actiontype varchar(20),
@CalDate DateTime)
as
begin
if(@Actiontype='edit')
begin
update tblcal
set avbl=(case when @Desc='AVL' then 'IN' else 'OUT' end),
Desc=RTrim(LTrim(@Desc))
where UID=@UID and CalDate=@CalDate
end
end
December 1, 2014 at 9:24 am
What's the issue? Where's the select statement? Who has the crystal ball?
December 1, 2014 at 9:38 am
Sorry,i edited the topic..
December 1, 2014 at 9:45 am
Please post DDL, sample data and expected results based on that sample data.
December 1, 2014 at 10:06 am
I didnt understood..
My main idea is to capture login id in the audit data table..Here is my trigger is there any function to capture the logged in user id....if its there i can add a column in audit table and i can insert it..
ALTER TRIGGER [dbo].[trAudit] ON [dbo].[Employee]
AFTER UPDATE
AS
Declare @ID char(6),
@CurrDesc char(40),
@EID int
SET NOCOUNT ON
Select @ID = i.SID, @CurrDesc =i.Desc,@EID = i.Name From Inserted i
If (@CurrDesc ='' or @CurrDesc ='AVA' )
Begin
INSERT INTO tblAudit VALUES(@EID , @ID, @CurrDesc , GETUTCDATE())
End
else
Begin
INSERT INTO tblAudit VALUES(@EID , @ID, @CurrDesc , GETUTCDATE())
End
SET NOCOUNT OFF
December 1, 2014 at 10:14 am
Read the article linked in my signature. You're not giving the complete picture and I'm certain that this isn't the first time you've been asked to do so.
December 1, 2014 at 10:45 am
This was also posted on stackoverflow. http://stackoverflow.com/questions/27232562/issue-with-select-statement-in-a-stored-procedure
Here is the real issue. We can't see your screen, we have no idea what your tables are like and we don't really know what you are trying to do.
Does every user have their own login to the database? I suspect not. That means you have no way of knowing who is logged in to the application because sql server is NOT your application. You would have to capture this in your application and pass it to this procedure as a parameter.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply