September 16, 2008 at 2:23 pm
Hi all,
I just got a challenging project on my head and would like to get some experts help from you all.
We have a 500 lines stored procedure that gets information from 4-5 tables and the procedure is called from the application. The procedure is executing fine except one thing(operator issue in the code), for this I need to debug the procedure fully to find out where exactly the code change should be done. We have the same procedure in Oracle doing same logic, it works fine. Is there a way to debug the procedure from the Management Studio, so I can may be find where in the code we are facing the problem with?
Thanks a lot, all.
September 16, 2008 at 2:26 pm
Can't debug a stored proc in SSMS. You need, if memory serves, Visual Studio 2005 Professional or better.
😎
September 16, 2008 at 3:13 pm
Hmm, I just downloaded the trial version of Visual Studio 2005 I dont see a debug option when I right click on the stored procedure. Is it because of the trail download??
September 16, 2008 at 3:43 pm
The trial edition is most likely the Standard Edition with a expiration date, which is not as powerful as the Professional Edition.
😎
September 17, 2008 at 9:20 am
Okay. Thanks.
Somehow, I found where exactly we are having problem with in the stored procedure. Below is that bit of the code:
select distinct cl.table_name, field_name,t.name,inc,
svalue = '(' + cl.table_name + '.' + field_name +
CASE cl.hval
WHEN cl.lval THEN ' ' + oper + ' ' +
CASE t.name
WHEN 'char' THEN '''' + cl.lval + ''''
WHEN 'varchar' THEN '''' + cl.lval + ''''
WHEN 'datetime' THEN '''' + cl.lval + ''''
ELSE cl.lval
END
.
.
.
Currently we are getting the exact value of 'oper' as is. Instead, we need to use a condition like below:
If the c1.oper is !=, we should get the value as '=' when c1.inc is 'E' else we can get != or the default operator. Basically the value of c1.inc is 'E' or 'I'.
Please let me know if you have any suggestions.Thanks a lot for all your help.
September 17, 2008 at 9:33 am
Ussualy what I do is to copy the code from sql_procedure and paste it into a blank New Query window and then change the parameters as variables at the begining. Everything should work fine and it will give you the line number where is the error if any. I'm using the free MS SQL Server Management Studio Express edition.
September 17, 2008 at 9:38 am
Not sure if my previous message was clear. I tried using CASE for the condition. It didnt work. I think its the syntax error.
select distinct cl.table_name, field_name,t.name,inc,
svalue = '(' + cl.table_name + '.' + field_name +
CASE cl.hval
WHEN cl.lval THEN ' ' + case when m1.inc='E' then oper(!=,=) else oper + ' ' +
CASE t.name
WHEN 'char' THEN '''' + cl.lval + ''''
WHEN 'varchar' THEN '''' + cl.lval + ''''
WHEN 'datetime' THEN '''' + cl.lval + ''''
ELSE cl.lval
END
.
.
. from cdet c1 where..
When the c1.oper value is != and c1.inc is 'E', we need to get the value as '='
Right now, it is just getting the value of the oper as is.
Please help, if you know. Thanks again.
September 17, 2008 at 9:58 am
If it can help, we use the below in Oracle:
Decode(oper,'!=','=',oper)
But the only difference is, in Oracle the condition for m1.inc='E' is the where clause of the select statement.
In Sql server, we dont have this in the where clause. So we have to mention it in the CASE:
When m1.inc is 'E' and oper is != , the value of oper should be =
Else != or default(value of oper).
September 17, 2008 at 10:00 am
When use CASE you have to put the value not the field name on THEN clause. So change it to this:
select distinct cl.table_name, field_name, t.name, inc, svalue = '(' + cl.table_name + '.' + field_name + CASE cl.hval
WHEN ‘value_of_cl.lval’ THEN ' ' +
CASE m1.inc
WHEN 'E' then oper(!=,=)
Else oper + ' ' +
CASE t.name
WHEN 'char' THEN '''' + cl.lval + ''''
WHEN 'varchar' THEN '''' + cl.lval + ''''
WHEN 'datetime' THEN '''' + cl.lval + ''''
ELSE cl.lval
END
END
END
.
.
. from cdet c1 where..
September 17, 2008 at 10:15 am
THanks, I tried the same:
select distinct cl.table_name, field_name, t.name, inc, svalue = '(' + cl.table_name + '.' + field_name + CASE cl.hval
WHEN c1.lval THEN ' ' +
CASE m1.inc
WHEN 'E' then oper(!=,=)
Else oper + ' ' +
CASE t.name
WHEN 'char' THEN '''' + cl.lval + ''''
WHEN 'varchar' THEN '''' + cl.lval + ''''
WHEN 'datetime' THEN '''' + cl.lval + ''''
ELSE cl.lval
END
END
END
.
.
. from cdet c1 where..
Getting two errors:
Incorrect syntax near '!'.
Incorrect syntax near 'END'.
I tried to put != and = withing quotes
select distinct cl.table_name, field_name, t.name, inc, svalue = '(' + cl.table_name + '.' + field_name + CASE cl.hval
WHEN c1.lval THEN ' ' +
CASE m1.inc
WHEN 'E' then oper('!=','=')
Else oper + ' ' +
CASE t.name
WHEN 'char' THEN '''' + cl.lval + ''''
WHEN 'varchar' THEN '''' + cl.lval + ''''
WHEN 'datetime' THEN '''' + cl.lval + ''''
ELSE cl.lval
END
END
END
.
.
. from cdet c1 where..
Getting the below error:
'oper' is not a recognized built-in function name.
Incorrect syntax near 'END'.
Please help and Thanks
September 17, 2008 at 11:06 am
You have mixed 'cl.' with 'c1.'. Are there 2 different tables?
Here are some changes, but it will be helpful if you can provide the full SELCT statement to proper fix the error.
Try this:
select distinct cl.table_name, field_name, t.name, inc, svalue = '(' + c1.table_name + '.' + field_name + CASE c1.hval
WHEN c1.lval THEN
Case m1.inc
When 'E' Then '='
Else IsNull(c1.oper, '!=') +
CASE t.name
WHEN 'char' THEN '''' + c1.lval + ''''
WHEN 'varchar' THEN '''' + c1.lval + ''''
WHEN 'datetime' THEN '''' + c1.lval + ''''
ELSE c1.lval
END
End
END
.
.
. from cdet c1 where..
September 17, 2008 at 11:23 am
Thanks a lot for all your help. It worked!!!!!!!!
I appreciate it
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply