concatinating result: select * from table
-
i would like to assign the concatenated output of a dynamic select statement to a variable e.g.
declare @result varchar(4000)
set @SQLstmnt = ' select ' + @result + ' = * from ' + @table + ' where ' + @where
exec sp_executesql @SQLstmnt
does not work
the select will always send back max. one row. Cursor i can not use since the table variable.
-
Why do you want to do that???
-
i need to write a kind of audit for insert, update and delete for a Web application. The Web application calls stored procedures with user email address logged on to the web app. Inside i would call a generic audit function.
E.g.
CREATE PROCEDURE dbo.updateCustomer
@CustomerName nvarchar(50),
@Segment Int,
@Shared Bit,
@_ECUID varchar(150),
@__CUID bigint,
@LoggedOnUserEmail varchar(100)
AS
delcare @auditID bigint
--store the old values of the Customer data
exec @auditID = auditProc 'Customers','where _CUID = ' + @__CUID,@LoggedOnUserEmail,0
--update the table Customers with the new data
update Customers set CustomerName= @CustomerName where _CUID = @__CUID
--store the new values of the Customer data
exec @auditID = auditProc 'Customers','where _CUID = ' + @__CUID,@LoggedOnUserEmail,@auditID
the table auditTable would now contain the old + new values + the user who performed the manipulation.
-
Here's a logic that works well.
The customer table keeps the current information (no need to have that in 2 places)
On any update/delete of that information, a trigger inserts the old data into an audit table. Bam you're done
.create trigger trCustomers_UD on dbo.Costumers
FOR UPDATE, DELETE
AS
SET NOCOUNT ON
Insert into dbo.AuditClients (col1, col2, coln) select Col1, col2, coln from Deleted
GO
Game over, works for 0 to x billion rows at the time, and not just from proc calls.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply