I'm going to skip past your questions and try to offer broader guidelines/advice instead.
The definitive/canonical guide to dynamic SQL, the situations where it is applicable and where it can be avoided, is Erland Sommarskog's Dynamic Search Conditions in T-SQL. Read it, re-read, run through Erland's examples, make sure you understand the reasoning behind the recommendations.
You're dealing with a fairly common scenario and the approach you've taken is not unusual. A couple of points worth highlighting:
- Using temporary tables is probably unnecessary. Is there a reason they were introduced?
- You have probably over-indexed the table. Read Kimberly Tripp's "just because you can, doesn't mean you should" article on the topic.
- Because you've over-indexed on individual columns, you're probably lacking good covering indexes. With so many aggregations and such a wide range of search conditions, these will be a challenge to get right.
Now the most important part of getting these kinds of searches right... apply the 80/20 rule.
The majority of calls to your procedure are likely to comprise a relatively small number of the possible variations of parameters. You cannot create optimal indexes for all combinations of 15 parameters, so identify the most common patterns, create static stored procedures for these and index for them appropriately. Deal with the remaining combinations with dynamic SQL, following Erland's best practices.
In these scenarios, you will often find the usage patterns closer to 95/5 than 80/20 so the additional work of creating static procedures is not as labour intensive as it seems at first glance.
You can easily implement your requirement using a trigger on Login table that will log info in the audit_login table.
the key is using table trigger to log info in the audit table for auditing.
I will see if I can provide a working code. But you got the idea now.
Update: below is a prototype code that will help you.
Note: you dont need any Stored procedure, A trigger will do the job.
You can put try/catch to handle errors, which I have not done as this is only to help you understand the concept.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Logins]') AND type in (N'U'))
DROP TABLE [dbo].[Logins]
GO
create table Logins (userid varchar(15) not null
,username varchar(max)
,[password] varchar(50)
,[status] varchar(1)) -- S = Success, F= Fail
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[audit_login]') AND type in (N'U'))
DROP TABLE [dbo].[audit_login]
GO
SELECT [userid],[username],[status]
,AuditDataState=CONVERT(VARCHAR(10),'')
,AuditDMLAction=CONVERT(VARCHAR(10),'')
,AuditUser =CONVERT(SYSNAME,'')
,AuditDateTime=CONVERT(DATETIME,'01-JAN-1900')
into dbo.audit_login
FROM dbo.Logins
WHERE 1=2
go
CREATE TRIGGER Logins_Insert
ON dbo.Logins
FOR INSERT
AS
INSERT INTO dbo.audit_login
SELECT [userid],[username],[status],'New','Insert',SUSER_SNAME(),getdate() FROM INSERTED
GO
CREATE TRIGGER Logins_Delete
ON dbo.Logins
FOR DELETE
AS
INSERT INTO dbo.audit_login
SELECT [userid],[username],[status],'Old','Delete',SUSER_SNAME(),getdate() FROM DELETED
GO
CREATE TRIGGER Logins_Update
ON dbo.Logins
FOR UPDATE
AS
INSERT INTO dbo.audit_login
SELECT [userid],[username],[status],'New','Update',SUSER_SNAME(),getdate() FROM INSERTED
INSERT INTO dbo.audit_login
SELECT [userid],[username],[status],'Old','Update',SUSER_SNAME(),getdate() FROM DELETED
GO
Now insert some data :
-- Insert New Data
insert into Logins
values ('kin001', 'kin','$tr0ng01','s')
select * from audit_login
-- Update the current Data
update Logins
set userid = 'kin-up-01'
where userid = 'kin001'
select * from audit_login
-- delete the data
delete from Logins
where userid = 'kin-up-01'
Below will be the end result:
Best Answer
This is only as a guidance; it is better to post some sample data and definitions.