DEFAULT and Stored Procedures
-
Suppose I had a table
CREATE TABLE JustTestParam (
TestDate DATETIME DEFAULT GETDATE(),
TestInt INT DEFAULT 3,
TestStr VARCHAR(30) DEFAULT 'hello' )
I can execute this in QA
insert into JustTestParam ( TestDate, TestInt, TestStr )
values ( DEFAULT, DEFAULT, DEFAULT )
But if I create a Stored Procedure
CREATE PROCEDURE IntoTest (
@myDate DATETIME,
@myInt INT,
@myStr VARCHAR(30)
) AS
INSERT INTO JustTestParam ( TestDate, TestInt, TestStr )
VALUES ( @myDate, @myInt, @myStr )
RETURN 0
How do I perform the equivalent of the following?
EXEC IntoTest
@myDate = DEFAULT,
@myInt = DEFAULT,
@myStr = DEFAULT
Obviously - I am trying to avoid dynamic SQL in this case since the routine that is filling the parameter values comes from my C# code... and we don't allow INSERT commands directly, only through the Stored Procs.
I can get around it in one way - but want to see your response before I continue.
TIA
Guarddata-
-
you could set default values in the definition of the parameters for the proc and then not pass anything in. Or check to see if you want defaults with and IF statement and then just insert using the same keyword (DEFAULT)
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
Something like...
CREATE PROCEDURE IntoTest
(@myDate DATETIME = NULL,
@myInt INT = 3,
@myStr VARCHAR(30) = 'hello')
AS
INSERT INTO JustTestParam (TestDate, TestInt, TestStr)
Select ISNULL(@myDate, GetDate()), @myInt, @myStr
RETURN 0
Interestingly, I could not use GetDate() in the parameter definition block???
@myDate = GetDate()
Once you understand the BITs, all the pieces come together
-
I appreciate the input. I was hoping there was something I had missed.
Guarddata-
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply