You can't do a where clause as you're trying to do it. Unless someone suggest something better, you must use a subquery to find out the information from the previous...

Ok, won't argue on this, but will definitively search on why I was told that.

EDIT: I believe they told me to change a non-clustered index (only index on a heap)...

Or want to do a clustered insert instead of...

I agree with Lowell, you should try using sys.sql_modules instead of syscomments. Although, it seems easier to use the built-in function with a slight correction.

SELECT OBJECT_DEFINITION(OBJECT_ID('spname'))

You can use

EXEC sp_helptext 'spname'

Or right-click on object explorer and choose "Modify" option

+1

I understood that he wanted the code to fill a datetime column instead of using the int columns. I might be wrong :hehe:

The easy answer: You can't tune this SP.

This procedure is calling other procedures and we would need to know the definition of those stored procedures as well.

To get better help...

How do you define that you want to Join Srinivas AAAA with Srinivas ABC but not with Srinivas KKKK?

Could you provide DDL and sample data in a consumable format?...

Use a batch separator (aka GO);-)

And here's a different approach.

SELECT CONVERT( datetime, RIGHT( @Date, 6) + ' ' + STUFF( STUFF( RIGHT( '0' + CAST( @Time as varchar(6)), 6), 5,0,':'), 3,0,':'), 12)

If you have any...

This is not beautiful but might do the trick while I try something different.

DECLARE @Date int = 1130627 ,--= (exclude the first digit)(13-06-27)

@Time int = 51458 --(24hr) = 5:14:58

SELECT DATEADD(...

I have 2 links for you that will improve the performance and give you the solution for this.

Avoid using UDF unless they're iTVF (inLine Table-Valued Functions).

Tally OH! An Improved SQL...

What's the difference in the workload? How many lines are you using on each?

I wouldn't suspect of the ISNULLs as the main problem. I would check on all the full joins to check if they're really necessary and maybe to change the subqueries...