SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Store Procedure Help.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jscot
Posting Yak Master

106 Posts

Posted - 03/21/2013 :  21:15:31  Show Profile  Reply with Quote
Hi Guys,

I need big favor.
I create new table Table_Track (ID, StoreProcedureName, CreatedDate, CreatedUserId). I want to know it is possible, if any store procedure runs though Application then Insert SP Name and UserID in Table_Track that i just created.

and One more question:- I want to add One new Variable in more than 100 SPs is there any way i can use any query to add all in once instead of one by one.

Any advise would be great appreciate.

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 03/21/2013 :  22:09:04  Show Profile  Reply with Quote
Have you thought about using SQL Profiler for the first part?
Go to Top of Page

jscot
Posting Yak Master

106 Posts

Posted - 03/21/2013 :  22:15:48  Show Profile  Reply with Quote
What i want actually. i want to create SP that will Insert data (SP NAME,CreatedDate and CreateUserId) on the new table that i just created, and then use this SP in Specific Store Procedure to find out when this execute and who execute.

Profiler is for whole DB and its make my application slow if i turn on whole time ( am i right?)
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 03/21/2013 :  23:23:35  Show Profile  Reply with Quote
-- this is for adding a variable to an SP
-- this is a start, you can do the rest. There probably is a better way. Only works if you are adding more parameters.
Declare @Name varchar(max),
@SP varchar(8000),
@SP2 varchar(max),
@NewVariableText varchar(max)

SELECT @NewVariableText = ',' + CHAR(13) + CHAR(10) + '@testvariable varchar(128)' + CHAR(13) + CHAR(10);


DECLARE cur CURSOR FOR
Select name From sys.procedures
Where [Type] = 'P' and name is not null

OPEN cur

FETCH NEXT FROM cur
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SP2 = OBJECT_DEFINITION (OBJECT_ID(N'[AdventureWorks2012].dbo.' + @Name));
SELECT @SP = substring(@SP2,1,8000)

IF @SP is null or @SP <> @SP2
SELECT @Name + ' not done...'
ELSE
BEGIN
select @SP
SELECT @SP = STUFF(@SP,CHARINDEX ('AS' + CHAR(13) + CHAR(10) + 'BEGIN', @SP) - 2,2,@NewVariableText)

SELECT @SP = STUFF(@SP,CHARINDEX ('CREATE PROCEDURE',@SP),len('CREATE PROCEDURE'),'ALTER PROCEDURE')

select @SP
-- EXEC (SP@) -- remove remark when happy to do the actual thing
END
FETCH NEXT FROM cur
INTO @name
END
CLOSE cur;
DEALLOCATE cur;
Go to Top of Page

jscot
Posting Yak Master

106 Posts

Posted - 03/21/2013 :  23:53:07  Show Profile  Reply with Quote
Thanks for your help. I will let you know when i done with this. Quick question regarding first part of my question.

Let say here is my SP

Create Procedure Test
@Variable1 int
as
Select * from mytable
where @Variable1 = Variable1

--Here is my question
Declare @VariableName int

Set @VariableName = (Is there any way when this SP runs i can get the name of the SP with Variable?)

PRINT @VariableName

Please guide me. Thank You.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/22/2013 :  00:41:49  Show Profile  Reply with Quote
Nope...You've to hardcode it within SP for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 03/22/2013 :  00:50:33  Show Profile  Reply with Quote
-- you can get the names but not the values
Create procedure test
@Var1 int,
@var2 varchar(10),
@var3 money
as
begin
declare @procName varchar(256)
select @procName=OBJECT_NAME(@@PROCID)
select
'Parameter_name' = name,
'Type' = type_name(user_type_id),
'Length' = max_length
into #Stuff
from sys.all_parameters where object_id = @@PROCID
select @procName
select * from #Stuff

END;
GO
EXEC TEST 1,'ABC',3
go

Edited by - UnemployedInOz on 03/22/2013 00:55:10
Go to Top of Page

jscot
Posting Yak Master

106 Posts

Posted - 03/23/2013 :  01:22:33  Show Profile  Reply with Quote
Thanks Guys For help.

Here is the solution that i come up with your help..

Declare @Procedurename varchar(200)

Set @ProcedureName = OBJECT_NAME(@@PROCID)+' '+convert(varchar,@FirstVariable)+','+convert(varchar,@SecondVariable)

Note:- This way i can get Store Procedure name and the values that user enter.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000