SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

« SQL SERVER – 2005 – List All The Column With Specific Data Types

SQL SERVER – UDF – Validate Integer Function »

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

August 10, 2007 by pinaldave

This post is second part of my previous post about SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

This post demonstrates the script which displays create date and modify date for any specific stored procedure in SQL Server.
USE AdventureWorks; GO SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'P' AND name = 'uspUpdateEmployeeHireInfo' GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table, SQL Tips and Tricks, T SQL, Technology | 96 Comments

96 Responses

on August 14, 2007 at 2:47 am | Reply radheshyam patel

i ran this command and it says -invalid column -created_date and invalid modified_date.
also sys.objects is sysobjects.
pls let me know the correct syntax to find modified date of SP
- on August 5, 2010 at 2:11 pm | Reply MK
  
  if you read the post it states create_date, modify_date
  
  your relpy says invalid column created_date and invalid modified_date.
  
  created_date != create_date && modified_date != modify_date
- on August 18, 2010 at 6:32 pm | Reply Triveni Varma
  
  Its create_data and modify_date and not created_date and modified_date.
  Above command works fine :)
- on March 5, 2011 at 11:16 am | Reply Nitin
  
  Hi Guys i have checked this query.. and this worked poperly…
  This guy have great knowledge of Sql.
on August 21, 2007 at 3:10 pm | Reply Jake

I don’t know about the first poster, but I ran this command just fine. Copy & paste…replaced the stored procedure name and the information came up just fine.
on August 21, 2007 at 4:08 pm | Reply pinaldave

@Jake,

I agree with you.
I think first poster might be talking about SQL Server 2000. As mentioned in post title it is for only SQL Server 2005.

@radheshyam patel,
The syntax is correct for SQL Server 2005. I only provide correct syntax. Also sys.objects is correct syntax for SQL Server 2005.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on October 12, 2007 at 7:30 am | Reply Yoganand.A

I need to take the complete modification history of a stored procedure. Sysobjects hold only last modified date. But i need history of Stored procedure every time i modifies.
can u give me a solution.
on November 13, 2007 at 12:43 pm | Reply Mithilesh

Hi Dave,

I am using SQL2005 Management studio express and try to find the select * from sys.objects then it displays Invalid object name ‘SYS.OBJECTS’.

As I have to find the last modified date of any stored procedure i ma using the query:
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘P’ AND name = ‘sp_name’

if I use sysobjects then there is no field as modify_date. there are two date fields crdate and refdate but both are same. even if I modify the procdure its values remains the same as crdate. Please suggest. its urgent. u can also tell me for SQL 2000.
on November 19, 2007 at 3:34 pm | Reply Rashmi

Hi Yoganand,
Any updates. How did you get the complete modification history of a Stored procedure.

Rashmi
on November 20, 2007 at 9:32 pm | Reply Rushabh

Hi…This will gonna work for SQL Server 2000

USE pubs
GO
SELECT name, crdate, refdate
FROM sysobjects
WHERE type = ‘P’ AND name = ‘insert_authors’
GO
- on February 17, 2010 at 7:15 pm | Reply Madhivanan
  
  Refdate is not equivalent of SQL Server 2005′s modify_date
on November 27, 2007 at 1:10 pm | Reply Raghunath

I wanted to select only the procedures in a database. How can I write the select statement?
on December 6, 2007 at 5:33 pm | Reply Om Shanker Mishra

is refdate is modified date in sqlserver2000
on December 6, 2007 at 10:23 pm | Reply Jake Estorque

Hi,

Would like to ask if in SQL Enterprise manager, I modified the table, will the “Create Date” time stamp change? What resources can I read regarding this?

thanks
on December 12, 2007 at 2:54 pm | Reply Claus

Hey.

In Oracle it is possible to see what stored procedures are valid by issueing a “compile” command. Is there any similar way in MS SQL Server? I think of the situation where a stored procedure is doing a SELECT on a column that later changes its name and hence all of a sudden will fail.

I hope there is.

Cheers

Claus
- on February 17, 2010 at 7:20 pm | Reply Madhivanan
  
  Alter the stored procedure
on January 22, 2008 at 7:14 am | Reply Sukh

Hi,

Yes, it shows same date but i have made changes in stored procedure.
on February 5, 2008 at 2:58 pm | Reply Pinoy Abe

Salamat po!
on February 11, 2008 at 5:16 pm | Reply preethi

Dear Sir…
can i know the stored proc for altering date.(current system date)
on March 11, 2008 at 10:36 am | Reply Deivendra padmanaban

Hi,

I think we can not able to identify the modified date of a procedure in sqlserver2000. we need to use tools to identify them. but in sqlserver2005 we can do by using the script given by dave.
on March 31, 2008 at 11:08 am | Reply gowtham

Hi,
I want to write a stored procedure for knowing the server date(Current date). so please can you help me out regarding this, please.

Thanks,

gowtham
- on February 17, 2010 at 7:20 pm | Reply Madhivanan
  
  Select getdate()
on May 16, 2008 at 10:39 am | Reply MLC

my database is having windows authentication i need to know who modified my stored procedure
on May 27, 2008 at 3:51 pm | Reply md mashooque ali

i m not getting a right output.
i want a procedure which returns a seach value a any name given to it from a table
on July 23, 2008 at 12:55 pm | Reply Kannabiran

Hi all,
Im using two database db1,db2, the data will be insert,update will done in db1 will be uploaded once in a day.if i updated or inserted the records in another day means it should fetch that records alone and updated to db2 database,im using sql server 2000,asp.net2.0.how can i acheive this one.

Regrds
Kanna
on July 30, 2008 at 10:44 pm | Reply Don

Thanks for the query! Worked great!
on August 6, 2008 at 3:10 pm | Reply ramah

thank u alot for that code
it work just fine but i need to know when was the last modify date for the data in my database
on September 2, 2008 at 4:48 pm | Reply ved

When i am executing the following query
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘P’
it is showing error “Invalid object name ‘sys.objects’”

in sql server 2005, Microsoft SQL Server Management Studio 9.00.1399.00
on September 5, 2008 at 11:47 am | Reply Mayank Parmar

Well this is excellent, I use this whenever I have to upload new changes to my live site.

Thanks & Regards,
Mayank
on November 11, 2008 at 3:05 pm | Reply Vijayakumar.P

please help me

how to find sql objects like stored procedure,functions created client login name in server by using t-sql query
on December 12, 2008 at 10:28 am | Reply Prasanti

Can we get the Previous version(Before Modification) Script of Modified Stored Procedure in SQL SERVER 2005.
on December 23, 2008 at 6:09 pm | Reply Raj Acharya

Hi,

i want to find out last access date of any procedure or function can u help me out from this
on February 3, 2009 at 9:40 am | Reply Naveen

I would like to capture the date on which a record is being generated
for eg : this is a weekly report and one employee record came in the first week and when i run the report in the next week it should show me the last record running date.

how can we get this since its record level capturing

early response will be appreciated!
on February 7, 2009 at 2:42 am | Reply cyrus m nash

do this for sql server 2000 for same result :

—————————————————————————
select *
from information_schema.routines
WHERE
Routine_Type=’procedure’
AND specific_name= ‘uspUpdateEmployeeHireInfo’
AND specific_Catalog=’AdventureWorks’

————————————————————————–

use specific_name for your determind stored procedure name
and specific_Catalog
for your determind database name
good luck :)
on February 7, 2009 at 2:46 am | Reply cyrus m nash

hi Navee, what is your database?

hi Raj Acharya, you ca n use above query for this porupose if your databse is sqlserver2000, if you have any other database let me know.
on February 17, 2009 at 5:55 pm | Reply jagadish

hi sir/madam,

i write one select command in stored procedure
Eg., Select * from employee_tb where employeeid=’50000001′

above statement written in stored procedure.
How to retrive the data in ASP .
i want information employeefirstname ,lastname,badgeid,salary.

how ‘ll you call above storedprocedure in ASP.
finally i want information above 3 fields.

Thanks,
Jagadish
on February 24, 2009 at 5:40 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date [...]
on March 12, 2009 at 2:48 pm | Reply xxxx

SELECT name as ‘SP Name’
FROM sysobjects
WHERE type = ‘p’
on June 5, 2009 at 3:55 pm | Reply Jitesh

Hi,

I tried this syntax to find the last modified date of a table. But its showing same date for create and modify. Does this modification means change in table structure or modify means modification in data also? I also tried sys.tables to find the modification date but its showing same result? Is there any way to find the last data modification date in a table?

Thanks & Regards
Jitesh
on June 9, 2009 at 4:37 am | Reply Brian

I switched SQL Server versions and forgot how I set up solution/projects which directed my Stored Procedures to my database. Had problems recreated the Solution/project in SQLExpress 2008. When executed individually my stored procedures where dropped into mystery default land. I’ve got a bunch of them there but can’t find them on the machine. Your USE command helped me to insure that I direct my stored procedure to the right database. I know I should take the time to learn how to DBA my SQL Server tool but Ive got enough learning all the .NET stuff righ now. Thanks!
on July 13, 2009 at 4:49 pm | Reply Nirali

Hi,

I want to keep the history of any updations take place in any stored procedure how is possible in sql2005
on July 23, 2009 at 8:36 am | Reply Achilles

Hi,

I tried to add stored procedure in

….\…..\Databases\….\Programmability\Stored Procedures

and it asked to ‘save as’ into a folder but when i refresh the stored procedure i just save won’t show in the Stored Procedure list.

Please help as a I am not a SQL guru.

Thanks,
on July 24, 2009 at 5:32 pm | Reply vaira

hi

I want 2 get alter sp name ..

eg:

I was alter a sp in 1 jan 2009 .
I was execute the query
(SELECT * FROM sysobjects WHERE type = ‘P’) it was got all the sp from db.. i want alter sps from jan to till date ..any know tel me
on July 24, 2009 at 5:34 pm | Reply vaira

hi

I want 2 get alter sp name ..

eg:

I was alter a sp in 1 jan 2009 .
I was execute the query
(SELECT * FROM sysobjects WHERE type = ‘P’) it was got all the sp from db.. i want alter sps from jan to till date ..
any body knows tel me the answer..

adv tx..
vaira
on November 6, 2009 at 8:24 pm | Reply aarti

ALTER PROCEDURE setuserlogin
(
@username varchar (50),
@logintime datetime
)
AS
UPDATE userprofile
set logintime=@logintime,onlinestatus=1
WHERE
username=@username;

if i exe this then 1 error is coming near ste userlogin
please suggest something,so i can exe it.
on December 18, 2009 at 4:33 am | Reply Mike

I would say first this is good for SQL Server 2005. Also you may add ORDER BY modify_date DESC. Sometimes you want to see the latest stored procs.
on January 4, 2010 at 9:56 pm | Reply Harry REspass

We have a calendar that we currently have to update the information each day. Is there a way to have the dates in the table update daily? This would make it easier so that we can just add the info in once.

Regards,
on January 6, 2010 at 12:53 pm | Reply shankar

Dear pinal

Everyday i face problem regarding upload modified procedure and table script.

i know how to find modified tables and procedure. but its very boring process to generate the modified procedure and table script.

I want tool or query which automatically generate the script of modified procedure and modified tables.

i hope you will get the solution.

Waiting for your reply..

Thanks
Shankar.
on January 8, 2010 at 8:51 pm | Reply sabnam

Hi Pinal Dave,

Just had a simple question:

I want to place created_date and modified_date field in every(almost all) table of my database and want created_date to be filled when new data is entered and modified_date to be filled when update is made?

can you give me ur suggestion on this?

Thanks
Sabnam
on January 9, 2010 at 12:56 am | Reply Pinal Dave

Hello Sabnam,

You can define the default value GETDATE() for create_date column but for modified_date column you will have to implement one out of following two methods:
1. Create update trigger for each table to update the modified_date column.
2. Modify your application to update the modified_date vlaue with each UPDATE statement.

Regards,
Pinal Dave
on January 27, 2010 at 12:41 pm | Reply Rajesh

Hi Pinal,

How to Find Unused Stored procedures in sql server 2005?
on February 16, 2010 at 6:09 pm | Reply Deepak Arora

Hi Pinal,

How to locate store proecdure when I have more than 15 database in my SQL server and I have not idea that which database is having this Storeprocedure.

I tried . . . Suppose i am using loop and @i variable comes to 9th Database_id ..with database base platform “Master”

Declare @i smallint
SET @i = 9
Declare @Databasename varchar (50)
SET @Databasename = (SELECT Name FROM sys.databases where database_id = @i)

USE @Databasename — Error ‘Incorrect syntax near @databasename’
SELECT name , create_date from sys.procedures where name like ‘%Emails%’

You tell me.. why i am not able to use variable with key word “USE” in SQL.
Secondly, Can you plz give me the way through which I can locate SP on my SQL server and can find out that database having this SP.
on February 17, 2010 at 7:35 pm | Reply Madhivanan

One method is to script all the procedures to a file and do find

Another method is running this query

declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ select ”’+name+”’ as database_name from ‘+name+’.sys.objects where name =”spGetAttendanceReport1” union all’ from sys.databases
select @sql=substring(@sql,1,len(@sql)-9)
exec(@sql)
on February 17, 2010 at 7:40 pm | Reply Madhivanan

Pinal,

Why is single quote is represented differently when posted here?

Users have to modify the code before running it

Can you change this behaviour?
on March 10, 2010 at 7:58 pm | Reply Durani

Hi,

Is there anyway to find out when was the database last accessed/modified in SQL Server 2000?

Tried the below but noluck..
1) select * from master.dbo.sysdatabases
2) select * from master.dbo.sysfiles
3) sp_Helpfile

I didnt look at the last accessed/modified date of the physicall database/log files.

Any help..much appreciated..

Thanks,
Dan
on March 17, 2010 at 8:33 pm | Reply Matthew

Thank you for this.

One has to wonder why Modified Date is not a column in SQL Server 2005 Management Studio; a rather obvious piece of info to display I should think.

Thanks again,
Matthew
on April 28, 2010 at 11:28 am | Reply Ruchi Vishnoi

you can use INFORMATION_SCHEMA.ROUTINES catalog view for finding out created date and last modified dates of DB Objects in SQL Server 2005 Management Studio. Like :

SELECT ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES

Thanks,
Ruchi
on May 20, 2010 at 7:46 am | Reply Ron Harper

@Ruchi,

I have tried that query, and it doesn’t work in 2000. I have modified a procedure to see if the last_altered data changed, and it doesn’t, it keeps showing the create date.
on May 31, 2010 at 5:43 pm | Reply kashyap

dear all,

i want to know a store procedure for auto generation of a date & time when the a data is added to the table.

can any body knows the procedure
on June 3, 2010 at 2:57 pm | Reply shipra agarwal

Hi, Pinal

Would you like to display a single stored procedures using
almost all SQL queries.It will be very helpful for me to understand the structure of SP’s as well as their working with queries.

Shipra Agarwal
jaipur.
on June 4, 2010 at 12:08 pm | Reply kashyap

i want to know a store procedure for auto generation of a date & time when the a data is added to the table.

can any body knows the procedure
on June 18, 2010 at 11:29 am | Reply MJ

Found this from another site – works fine in 2005

SELECT name, modify_date FROM sys.procedures

if you need to run the same against tables then :

SELECT name, modify_date FROM sys.tables
on June 30, 2010 at 3:12 pm | Reply shivlila

Very nice use ful queries u have posted
on July 7, 2010 at 6:35 pm | Reply KB

Hi,

Is there any way to find which user altered the table last time?

Will it be logged somewhere in SQL Server 2005?
If yes, how to retrieve it?

Thanks,
KB
on September 8, 2010 at 5:29 pm | Reply Abhishek

Hi ,
When ever i create a store procedure like

ALTER PROCEDURE uspAddAddressType
(
@addressTypeName Varchar(30),
@status varchar(30),
@addedOn datetime,
@addedBy varchar(30)

)
AS
BEGIN

INSERT INTO addressType
( addressTypeName,
[status],
addedBy,
addedOn
)
VALUES
( @addressTypeName,
@status,
@addedBy,
GETDATE()
)
END

And Save next time when i see that procedure it display like
ALTER PROCEDURE [dbo].[uspAddAddressType]
(
@addressTypeName Varchar(30),
@status varchar(30),
@addedOn datetime,
@addedBy varchar(30)

)
AS
BEGIN

INSERT INTO addressType
( addressTypeName,
[status],
addedBy,
addedOn
)
VALUES
( @addressTypeName,
@status,
@addedBy,
GETDATE()
)
END

Why it is happening. Any one can help me .
on October 20, 2010 at 12:13 am | Reply Ahmed

Hi
Dear All,

Already project developed. So there is some changes in the project. So whenever we are altering the procedures it is saving in a separate .sql file but in the same procedure. why it is happening like that. But i want to alter the procedure which is already there in the project.Please solve my problem
on January 11, 2011 at 11:25 am | Reply Srini

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘P’ and modify_date between ’10-Jan-2011′ and getdate()
- on January 11, 2011 at 4:41 pm | Reply madhivanan
  
  Make sure to use unambigious date formats
  http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
on February 1, 2011 at 5:27 pm | Reply Reddy

@Triveni….

its ok……….
on February 18, 2011 at 2:48 pm | Reply ankit

thanks FOR this post ….i REALLY NEEDED IT
on March 6, 2011 at 1:22 pm | Reply Jayant dass

Hi,

can we modify system stored procedure on SQL server 2005 ?

Thanks in Advance

Thanks & Regards
Jayant Dass
on April 4, 2011 at 10:09 am | Reply Mukund

declare @day as int
set @day = 12
select * from
(SELECT [name],create_date,modify_date,’table’ type1,1 order1 FROM sys.tables
union
SELECT [name],create_date,modify_date,’view’ type1,2 order1 FROM sys.views
union
SELECT [name],create_date,modify_date,’trigger’ type1,5 order1 FROM sys.triggers
union
SELECT [name],create_date,modify_date,’sp’ type1,3 order1 FROM sys.procedures
WHERE [type] = ‘P’ AND is_ms_shipped = 0 AND [name] NOT LIKE ‘sp[_]%diagram%’
union
select [name],create_date,modify_date,’fn’ type1,4 order1 from sys.objects where type_desc like ‘%function%’)
as modify_table
where datediff(dd,modify_date,getdate())<@day

ORDER BY order1,modify_date DESC
on May 13, 2011 at 5:14 pm | Reply Jayant Dass

Hi Mukund ,

My Question is little different , can we alter system stored procedure on MSSQL server 2005 ?

Thanks in Advance

Regards
jayant dass
- on May 16, 2011 at 3:48 pm | Reply madhivanan
  
  Why do you want to do this?
on June 6, 2011 at 1:51 pm | Reply audit

How do i get the user id of the person who altered store procedure along with modified date in SQL2000/2005/2008?Also how to configure SQL server for keeping the version history of SP changes?
on June 22, 2011 at 8:03 pm | Reply Vinod Soni

Dear All,

I want to keep the history of any updations take place for any stored procedure ? not only the last modified date.

Suppose a SP ‘ABC’s definition has been modified 10 times from the creation date; i required the data of 10 times updation. If it comes with client machine id; it will be a great helpful to track the activities done on SP.
on July 15, 2011 at 8:39 pm | Reply Rave

HI I am trying to create procedure with two columns and need perameter for Bins (if i select 30 then should dispaly 30, if I select 40 then should dispaly 40 ) for bin column. second column display all standard values up to 1000 rows like blow.

Bins standard_normal_Values

1 -0.999325493
2 -0.99367071
3 -0.986924533
4 -0.982329311
5 -0.97467364
6 -0.970499059
7 -0.969153007
8 -0.968298082
9 -0.958441433
10 -0.958200417
11 -0.958079909
12 -0.957231805
13 -0.955901669
14 -0.954814823
15 -0.954814823
16 -0.95192263
17 -0.950481081
18 -0.950360572
19 -0.950360572
20 -0.947120498
21 -0.94448751
22 -0.94448751
23 -0.943175564
24 -0.94031293
25 -0.938766789
26 -0.938055109
27 -0.937936875
28 -0.936749984
29 -0.935681328
30 -0.93107019
-0.928475856
-0.923425887
-0.922020718
-0.921670562
-0.918166734
-0.913744316
-0.910729341
-0.909919891
-0.907375579
-0.906684363
-0.904262834
-0.903917226
-0.901159183
-0.899208317
-0.896231995
-0.895317953
-0.894747245
- on July 18, 2011 at 5:00 pm | Reply madhivanan
  
  Can’t you simple pass it as parameter?
  where bin=@bin
on July 27, 2011 at 5:48 pm | Reply [name removed as it was cell number]

last update date find problem in sql [email removed]
on July 27, 2011 at 11:59 pm | Reply LA

I noticed that the modify_date on all of my objects is referencing the date we upgraded from SQL 2005 to SQL 2008R2. Is there any other column I could use to determine the last revision date for an object that would not have changed as a result of the upgrade?
on August 7, 2011 at 10:18 am | Reply JJ

Hello,

I want generate all the scripts of the tables and stored procedures modified between two dates. How can I do this?

Thanks
on August 30, 2011 at 11:53 am | Reply Rajesh

THIS SHOWING SOME ERROR PLEASE DO THE FAVOUR TO SHOOT THIS

CREATE PROCEDURE INCREASE

DECLARE
@INCR DATETIME

AS

@INCR=SELECT MAX(MonthDate) AS MDATE FROM VMONTHDATE
@INCR=@INCR+1
INSERT INTO VMONTHDATE(MonthDate) VALUES (@INCR)
GO
on September 21, 2011 at 3:41 pm | Reply Maria Loretta Escobal

How will i know the SQL Account used to modify the stored procedure?
- on September 22, 2011 at 2:19 pm | Reply madhivanan
  
  By default sa user can modify the procedure
on October 13, 2011 at 12:17 pm | Reply karthick

Hi,

purchase table sales table
PID PQty PID SQty
1 10 1 5
2 5 2 3
3 7 1 4
1 5

Output Should be
PId PQty SQty Remaining Qty
1 15 9 6
2 5 3 2
3 7 0 7

Can any one help me to get this output
in a single query
on October 31, 2011 at 10:27 am | Reply bineesh

Any chance to find out the login id who created the stored procedure ?
on March 15, 2012 at 5:55 pm | Reply Cradaa

I just have to say, that I love your blog. I use it frequently to get information.
Thank you.
on April 16, 2012 at 4:17 pm | Reply Rajesh

Great. This also Working For me too…. Thanks
on July 15, 2012 at 7:01 am | Reply SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name « SQL Server Journey with SQL Authority

[...] Find Stored Procedure Create Date and Modified Date [...]
on August 24, 2012 at 1:15 am | Reply Naveen.A

Hi,
To find the stored procedure details
“use DataBase_Name
go
sp_helptext StoredProcedure_Name”

wil get the Complete Stored Procedure details
on December 14, 2012 at 12:48 pm | Reply Avinash Ranjan (@avinash_t)

Very Vary Helpful …

Thanks SQL Authority
on January 4, 2013 at 9:52 pm | Reply Utku

Thanks
on January 7, 2013 at 3:43 pm | Reply Utku

Hi,
how to find stored procedure change user detail and column ınformation situation

Thanks
on January 23, 2013 at 10:26 pm | Reply Anmol

Thank you so much..
on March 8, 2013 at 6:44 pm | Reply Manish Solanki

Hi Friends…
I have Problem with Date Field.
I have Birthday_Table.

Table Filed :
=========
ID(int),
NAME(varchar(150)),
DOB(date)

Store Procedure:
=============
//Store Procedure Parameters
@ID INT,
@NAME VARCHAR(150),
@DOB DATE

INSERT INTO Birthday_Table
(ID,NAME,DOB)
VALUES
(@ID,@NAME,@DATE)

I Don’t wants to change DOB datatype.
if i pass DOB parameter like ’05-05-2013′ then Execute Successfully but
i pass DOB parameter like 05-05-2013 then prompt me error in ‘-’.

Please friends help me….
I hope I will get it from hear..
on March 14, 2013 at 5:30 pm | Reply Grgegory Ferdinandsen

I use, that way the code is more generic and does not require know the name of the SP

select @SPCreate = create_date from sys.objects where name = @SPName
select @SPMod = modify_date from sys.objects where name = @SPName