Store Procedure Help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Store Procedure Help.

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2013-03-21 : 21:15:31

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

54 Posts

Posted - 2013-03-21 : 22:09:04

Have you thought about using SQL Profiler for the first part?

jscot
Posting Yak Master

106 Posts

Posted - 2013-03-21 : 22:15:48

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?)

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-21 : 23:23:35

-- 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;

jscot
Posting Yak Master

106 Posts

Posted - 2013-03-21 : 23:53:07

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-22 : 00:41:49

Nope...You've to hardcode it within SP for that

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

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-22 : 00:50:33

-- 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

jscot
Posting Yak Master

106 Posts

Posted - 2013-03-23 : 01:22:33

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources