getting back to stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

getting back to stored procedures

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-24 : 12:31:47

Haven't written one in a while.

CREATE PROCEDURE proc_test
@cf_user_id nvarchar(100) = NULL,
@app_user_id int = NULL,
@transaction_guid = newid()
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

END

Msg 102, Level 15, State 1, Procedure proc_test, Line 10
Incorrect syntax near '='.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-24 : 12:38:22

default values of parameters cant be functions like newid(). as per BOL

The default must be a constant or it can be NULL.

so you need to do like


CREATE PROCEDURE proc_test
@cf_user_id nvarchar(100) = NULL,
@app_user_id int = NULL,
@transaction_guid uniqueidentifier = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SET = COALESCE(@transaction_guid,newid())
...
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 13:36:50

SET @transaction_guid = COALESCE(@transaction_guid,newid())

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 13:38:39

Or if NULL is a valid parameter to pass to @transaction_guid uniqueidentifier then obtain a GUID value and use that as the default:


CREATE PROCEDURE proc_test
@cf_user_id nvarchar(100) = NULL,
@app_user_id int = NULL,
@transaction_guid uniqueidentifier = '10E1BBB9-C48A-4CD1-AA09-D31798361572'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SET @transaction_guid = COALESCE(NullIf(@transaction_guid, '10E1BBB9-C48A-4CD1-AA09-D31798361572'), newid())
...
END

chedderslam
Posting Yak Master

223 Posts

Posted - 2010-03-24 : 13:54:56

Awesome. Thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources