Help Needed in writing a function to help daily nightmare with Debugging.

I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is.
Once I have established which SP is the main problem I need to debug the line of code.

What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..

Now what i thought is to write an SP or Function where I pass :

SP name and Parameters that profiler genererates
and returns me Declare Statements and Set Statements with parameters filled.

EG

Profiler Returns
Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'

I would call my new SP =PopulateSPParams and cut and paste the profiler's string
PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'

this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.

DECLARE
@.CustomerID int,
@.CustomerName varchar(50),
@.CustomerSurname varchar(50),
@.CustomerAddress varchar(100)

SET @.CustomerID =1
SET @.CustomerName='JO'
SET @.CustomerSurname='BLOGG'
SET @.CustomerAddress='5 London Road'

Can you help in writing something that generates and populate parameters?

Check this out. It only handles three parms, but it certainly could be made to handle more:

create procedure buildParms
(
@.procName nvarchar(128),
@.parm1 varchar(max),
@.parm2 varchar(max),
@.parm3 varchar(max)
) as
begin
set nocount on

select 'DECLARE @.retval int'
union all
select ' ,' + parameters.name + ' ' + types.name +
case
when types.name like 'n%char' then '(' + cast(parameters.max_length /2 as varchar(10)) + ')'
when types.name like '%char' then '(' +cast(parameters.max_length as varchar(10)) + ')'
when types.name like '%binary' then '(' +cast(parameters.max_length as varchar(10)) + ')'
when types.name = 'numeric' then '(' + cast(parameters.precision as varchar(10)) + ',' + cast(parameters.scale as varchar(10)) + ')'
else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName
union all
select ' '
union all

select 'SET ' + parameters.name + ' = ' +
case when types.name like '%char' then '''' else '' end +
case parameters.parameter_id --to add more parms, add to this list
when 1 then @.parm1
when 2 then @.parm2
when 3 then @.parm3
end +
case when types.name like '%char' then '''' else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName

union all
select ' '
union all
select 'EXECUTE @.retval = ' + @.procName
union all
select ' ' + parameters.name + ' = ' + parameters.name
+ case when parameters.is_output = 1 then ' OUTPUT ' else '' end +
case when parameter_id <> (select max(parameter_id) from sys.parameters where object_name(object_id) = @.procName)
then ',' else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName
end
go

--test with this proc

alter procedure test$paramTest
(
@.value1 int,
@.value2 nvarchar(10) output,
@.value3 numeric (6,2)
) as
select @.value1, @.value2, @.value3
go

Execute this

buildParms 'test$paramTest','1','x','200'

returns:

DECLARE @.retval int
,@.value1 int
,@.value2 nvarchar(10)
,@.value3 numeric(6,2)

SET @.value1 = 1
SET @.value2 = 'x'
SET @.value3 = 200

EXECUTE @.retval = test$paramTest
@.value1 = @.value1,
@.value2 = @.value2 OUTPUT ,
@.value3 = @.value3

|||

Fantastic.Sorry for late reply.I could not access Computer.

Let me check.Wll be back to you.

I was thinking of passing a delimited string so that it could handle all sorts of params.

Very very grateful!!!!!!!!!

|||

Thanks

This works like a dream.I beleive this only works on sql server 2005

To make it work on sql 2000 I will change the varchar(max) to varchar(8000) But still get errors on all the sys stuff.

Any more suggestions?

very very grateful for your help

|||

Try this on 2000. I used the information_schema views this time. I thought of doing this the first time, but thought it would be too much bother (actually it was about the same:)

alter procedure buildParms
(
@.procName nvarchar(128),
@.parm1 varchar(8000),
@.parm2 varchar(8000),
@.parm3 varchar(8000)
) as
begin
set nocount on

select 'DECLARE @.retval int'
union all
select ' ,' + PARAMETER_NAME + ' ' + data_type +
case
when data_type like 'n%char' then '(' + cast(CHARACTER_MAXIMUM_LENGTH /2 as varchar(10)) + ')'
when data_type like '%char' then '(' +cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) + ')'
when data_type like '%binary' then '(' +cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) + ')'
when data_type = 'numeric' then '(' + cast(NUMERIC_PRECISION as varchar(10)) + ',' + cast(NUMERIC_SCALE as varchar(10)) + ')'
else '' end
from information_schema.parameters
where SPECIFIC_NAME = @.procName
union all
select ' '

union all

select 'SET ' + PARAMETER_NAME + ' = ' +
case when data_type like '%char' then '''' else '' end +
case ordinal_position --to add more parms, add to this list
when 1 then @.parm1
when 2 then @.parm2
when 3 then @.parm3
end +
case when data_type like '%char' then '''' else '' end
from information_schema.parameters
where SPECIFIC_NAME = @.procName

union all
select ' '
union all
select 'EXECUTE @.retval = ' + @.procName
union all
select ' ' + PARAMETER_NAME + ' = ' + PARAMETER_NAME
+ case when PARAMETER_MODE = 'INOUT' then ' OUTPUT ' else '' end +
case when ordinal_position <> (select max(ordinal_position) from information_schema.parameters where SPECIFIC_NAME = @.procName)
then ',' else '' end

from information_schema.parameters
where SPECIFIC_NAME = @.procName

end
go

|||

What can I say?

You made my day ,

Thanks a lot I had started to convert it to 2000 but I had got stuck on the join between systypes and Information_schema.parameters i could not find the equivalent to join on.

thanks a lot again